slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
성 능 관 리 PowerPoint Presentation
Download Presentation
성 능 관 리

Loading in 2 Seconds...

play fullscreen
1 / 145

성 능 관 리 - PowerPoint PPT Presentation


  • 1327 Views
  • Uploaded on

성 능 관 리. 2005. 8. 김무영. 목 차. 성능관리에 대하여 ----------- 3 개발측면에서의 성능관리 단계 ------- 5 성능확보를 위한 설계 가이드 라인 ---- 14 SQL 튜닝 ------------------- 36 데이타베이스 튜닝 --------------- 70 시스템에서 주로 발생하는 문제들 ----- 97 성능관리 예제 1, 2 -------------- 103. 퍼포먼스. 무장애. 성능보장. 최적의 자원사용.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about '성 능 관 리' - lacy


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

성 능 관 리

2005. 8.

김무영

slide2

목 차

  • 성능관리에 대하여 ----------- 3
  • 개발측면에서의 성능관리 단계 ------- 5
  • 성능확보를 위한 설계 가이드 라인 ---- 14
  • SQL 튜닝 ------------------- 36
  • 데이타베이스 튜닝 --------------- 70
  • 시스템에서 주로 발생하는 문제들 ----- 97
  • 성능관리 예제 1, 2 -------------- 103
slide3

퍼포먼스

무장애

성능보장

최적의

자원사용

성능관리란?

O 성능관리자의 역할은?

O 성능관리의 책임은 누구한테 있는가?

slide4

성능상의 주요요소

DB Tuning

APP’

(SQL)

DBA

Performance

Management

프로그래머

모델러

DB Modeling

etc : OS, Web서버, WAS, H/W…

slide6
응용 설계자(Application Designer)

응용 개발자(Application Developer)

DBA(Database Administrator)

Hardware/Software 관리자

누가 성능관리를 하는가

* 성능관리의 책임자는???

slide7
언제 성능관리를 하는가 - 계획단계
  • 전문성능관리 인력 및 비용 확보
  • 하드웨어 및 소프트웨어 구성 선택
  • 시스템 확장을 감안한 용량계획 수립
  • 시스템 분석 및 설계표준 정립
  • 시스템별 성능기대치 결정
  • 성능목표 설정과 우선순위 결정은 분명하고 일관성 있게

계획

분석/

설계

개발

검수

운영

slide8

계획

언제 성능관리를 하는가 - 분석/설계단계
  • 성능을 감안한 데이터모델링 및 데이터베이스 설계
  • Table간의 자동화 규칙(제한조건)의 적절한 설계
  • 성능을 감안한 프로그램 설계 및 Table 구조 변경, 추가
  • 데이터요구에 적합한 인덱스 정책수립 및 정의
  • 데이터 모델과 User Interface 간의 적합성 검증

분석/

설계

개발

검수

운영

slide9

계획

언제 성능관리를 하는가 - 개발단계
  • 옵티마이저에 대한 이해와 실행계획 조정
  • 고급SQL 사용 및 최적화
  • 적용업무 프로그램에 적합한 Locking 범위 결정
  • 재사용 Library의 정립 및 적극적 활용
  • 프로그램 코딩 표준의 엄격한 준수

분석/

설계

개발

검수

운영

slide10

계획

언제 성능관리를 하는가 –검수(시험평가)단계
  • 실제 운영상황에 견줄만한 테스트용 데이터량의 확보
  • 실제 운영환경과 동일한 시스템 구성(Configuration)
  • 전문 튜너에 의한 성능시험 및 보증
  • 성능관리 전문툴 활용
  • 테스트 및 품질보증을 통과한 프로그램만을 현업에 적용

분석/

설계

개발

검수

운영

slide11

계획

언제 성능관리를 하는가 - 운영단계

분석/

설계

  • 시스템성능에 대한 지속적인 감시
  • 시스템 및 RDBMS 에서 제공하는 모든 성능감시도구 활용
  • O/S 와 RDBMS 튜닝간의 적합성 검증
  • LIVE SYSTEM과 TEST용 SYSTEM의 별도 사용

개발

검수

운영

slide12

비즈니스

분석

데이터

분석

논리

모델

물리

모델

DB

구현

시험

언제 성능관리를 하는가 – DB 구축과정
system tunnig
OLTP DB의 경우 : A

DW DB의 경우 : B

SYSTEM TUNNIG 비율

B.

A.

Database

Program(SQL)

DB Modeling

Database

15%

20%

15%

System

17.5%

System

2.5%

5%

DB Modeling

Program(SQL)

60%

65%

slide16
DB 튜닝측면

: 옵티마이저 정책 수립 실천, 자원소모 최소화 및 병목현상 제거, 적절한 DB 환경조절

SQL 사용측면

: 코딩표준, Sql사용형태, 실행계획 조절, 최적성/처리횟수 고려

DB 모델링 측면

- 최적의 논리 모델링 수행, 인덱스 정책 수립 실천

- DB 벤더 Master에 의한 물리설계

- 적절한 DB Constraints의 사용으로 SQL수량 제한

운영 및 향후 개선을 염두에 둔 설계

시스템 전체 측면에서의 과부하 제거

- 불필요한 네트워크 트래픽 제거

- 서버, OS측면에서의 부하제거

예상되는 문제점 제거

설계 가이드 라인
slide17
사전 준비사항

데이터모델의 세부 다이아그램

테이블의 평균 로우 수, 한 로우의 평균크기등

데이터 증가율, 평균 사용자수 등

최대집중시(Peak Time) 초당 수행되는 트랜잭션 개수 및 크기등

기타 데이터에 대한 문서

튜닝되지 않은 데이터모델

일대일 관계, 다대다 관계, 원형함정모델등

온라인 트랜잭션 처리에 적절하지 않은 모델

과도한 조인 및 Full Table Scan 을 유발하는 데이터모델

단순 명료 하지않고 복잡한 형태의 모델

데이터 모델 측면(간략히)
slide18

Database

Buffer Cache

Database

Buffer Cache

Shared

Pool

Shared

Pool

Redo Log

Buffer

Redo Log

Buffer

김무영 7803** ……..

김무영

78030**

서홍교

79030**

임성열

68030**

조광원

88030**

윤송이

88030**

ㅇ DB_BLOCK_SIZE & 모델링

- 평균레코드 길이가 적당

- 평균레코드 길이가 과도(과도한 디노말제이션)

SGA

SGA

Oracle block

Oracle block

DB block IO

(2k ~ 64k)

Disk IO

(2k ~ 4k)

slide19

EMP_idx1

EMP Table

iNDEX VALUE

ROWID/Address

사원번호

성명

직위

2812 0000089D.0000.0001

2854 김준억 부장

2840 0000089D.0001.0001

2858 허성욱 부장

2843 0000089D.0002.0001

2812 진종건 사원

2854 0000089D.0003.0001

2855 김종훈 차장

2855 0000089D.0004.0001

2863 이정호 차장

2856 0000089D.000A.0001

2840 김무용 사원

2863 0000089D.0003B.0001

2843 이재민 사원

인덱스정책 측면

SELECT 사원번호,성명,직위

FROM EMP

WHERE 사원번호 BETWEEN ‘2843’

AND ‘2856’

ORDER BY 1;

Sort 된 결과

ㅇ 인덱스란?

ㅇ 인덱스의 종류는 어떤것이 있으며 본 개발사업에서 사용할 인덱스종류는?

B*Tree, Bitmap, F.B.I, Hash, Cluster, Domain, spatial, IOT, Functional, Reverse, compressed …

slide20
인덱스 대상 테이블 선정

테이블의 크기가 5~6블럭 이상

무작위접근(Random Access)이 빈번한 경우

특정범위/순서의 데이터조회가 필요한 경우

Join의 연결고리가 되는 칼럼

참조무결성을 지정한 경우 연결고리 되는 칼럼

Nested Join이 일어나는 테이블

인덱스 대상 칼럼 선정

분포도와 손익분기점 : 분포도는 10~15%를 넘지 않아야 한다.

인덱스 머지(Index Merge)

결합인덱스(Composite Index) : 결합되는 순서에 따라 영향이 크므로 주의

* F.B.I : Oracle 8.0 DB이상

* Index skip 알고리즘 : 9i DB이상

칼럼 2

칼럼 1

칼럼 4

칼럼 3

1. 인덱스 선정기준

참고자료 : 대용량DB솔류션

slide21
해당 테이블의 가능한 모든 접근형태 수집

대상칼럼 선정 및 분포도 조사

반복 수행(Looping)되는 Critical Access Path의 해결

클러스터링 검토

인덱스 칼럼의 조합 및 순서의 결정

시험생성 및 테스트

수정이 필요한 Application 조사 및 수정

일괄적용

2. 인덱스 선정절차

slide22
인덱스와 데이터 테이블은 각기 다른 디스크에 배치

인덱스에 대한 적절한 공간 할당

테이블 접근없이 인덱스만으로 데이터 요구 해결

인덱스 생성 및 삭제 시점의 효율적 운용 검토

데이터 로드나 일시에 많은 데이터를 입력할 경우 인덱스에 대한 부하를 줄이기 위해 인덱스를 삭제, 입력 및 로드가 끝난 이후 재생성

인덱스의 명명은 생성목적과 의미를 내포

Ex) Sys_30002(?), IX_dept2

인덱스 관리측면 감안

- ANALYZE, REBUILD, 생성 및 삭제

인덱스의 종류 및 특징을 감안하여 사용

: B*Tree, Bitmap, F.B.I, Hash, Cluster, Domain, spatial,

Functional, Reverse, compressed …

3. 인덱스의 효율적 관리

slide23
“다음의 경우에는 인덱스가 사용되지 않는다.”

복합 인덱스에서 Leading Column이 제한 조건에 사용되지 못한 경우

인덱스칼럼의 변형

부정형 비교

널을 사용한 비교

LIKE문의 ‘%’가 맨 처음에 위치한 경우

Optimizer에 의한 취사선택

4. 인덱스의 활용

Select *

From emp

Where 주민번호 like ’68%’

and 성별 != ‘남자’

and 이전주소 is null

and substr(주민번호,3,2) != ’03’

Pk : 주민번호

* Where조건에서 인덱스를 사용하는 드라이빙 칼럼은

반드시 위의 조건을 지켜야하며 나머지 체크 칼럼은

부정형으로 사용하여도 무방하다.

slide24
SELECT *

FROM DEPT

WHERE SUBSTR(DNAME,1,3) = 'ABC'

부정형 비교

인덱스 칼럼 변형

NULL, NOT NULL

Optimizer 의 취사선택

5. 인덱스가 사용되지 않을경우

SELECT *

FROM EMP

WHERE JOB <> 'SALES'

SELECT *

FROM EMP

WHERE ENAME IS NOT NULL

SELECT *

FROM EMP

WHERE JOB LIKE 'AB%'

AND EMPNO = '7890'

참고자료 : 대용량DB솔류션

slide25

6. 인덱스칼럼의 변형(external)

SELECT *

FROM EMP

WHERE SUBSTR(DNAME,1,3) = 'ABC'

SELECT *

FROM EMP

WHERE DNAME LIKE 'ABC%'

SELECT *

FROM EMP

WHERE SAL * 12 = 12000000

SELECT *

FROM EMP

WHERE SAL = 12000000 / 12

SELECT *

FROM EMP

WHERE TO_CHAR(HIREDATE,'YYMMDD') =

'940101'

SELECT *

FROM EMP

WHERE HIREDATE =

TO_DATE('940101','YYMMDD')

slide26

7. 인덱스 칼럼의 변형(external)

SELECT *

FROM EMP

WHERE EMPNO BETWEEN 100 AND 200

AND NVL(JOB,'X') = 'CLERK'

SELECT *

FROM EMP

WHERE EMPNO BETWEEN 100 AND 200

AND JOB = 'CLERK'

SELECT *

FROM EMP

WHERE DEPTNO = '10'

AND JOB = 'SALSMAN'

SELECT *

FROM EMP

WHERE DEPTNO || JOB = '10SALESMAN'

의도적인 SUPPRESSING

SELECT *

FROM EMP

WHERE RTRIM(JOB) = 'MANAGER'

SELECT *

FROM EMP

WHERE JOB = 'MANAGER'

SELECT *

FROM EMP

WHERE RTRIM(EMPNO) = 8978

SELECT *

FROM EMP

WHERE EMPNO = 8978

slide27

8. 인덱스 칼럼의 변형(internal)

CREATE TABLE SAMPLET

( CHR CHAR(10),

NUM NUMBER (12,3),

VAR VARCHAR2(20),

DAT DATE)

SELECT * FROM SAMPLET WHERECHA = 10

SELECT * FROM SAMPLET WHERETO_NUMBER(CHA)= 10

SELECT * FROM SAMPLET WHERENUM LIKE '9410%'

SELECT * FROM SAMPLET WHERETO_CHAR(NUM)LIKE '9410%'

slide28

INDEX COLUMN의 변형 (internal) 예제

TABLE ACCESS FULL CHULGOT

SQL> SELECT SUM(UNCOST)

FROM CHULGOT

WHERE STATUS = 90

1 row,

28.5 sec

SQL> SELECT SUM(UNCOST)

FROM CHULGOT

WHERE STATUS = '90'

SORT AGGREGATE

TABLE ACCESS BY ROWID CHULGOT

INDEX RANGE SCAN CH_STATUS

1 row,

0.15 sec

SQL> SELECT CHULNO, CUSTNO, UNCOST

FROM CHULGOT

WHERE CFMDEPT LIKE '71%'

TABLE ACCESS FULL CHULGOT

rows,

sec

NUMBER type

SQL> SELECT ORDNO, CHULNO, STATUS

FROM ORDER1T X, CHULGOT Y

WHERE X.CUSTNO = Y.CUSTNO

AND X.ORDDEPT = Y.CFMDEPT

AND y.CHULDATE LIKE '9711%'

NESTED LOOPS

TABLE ACCESS FULL ORDER1T

TABLE ACCESS BY ROWID CHULGOT

INDEX RANGE SCAN CH_CFMDEPT

rows,

71 sec

slide29

9. NULL, NOT NULL

SELECT *

FROM EMP

WHERE ENAME > ' '

SELECT *

FROM EMP

WHERE ENAME IS NOT NULL

SELECT *

FROM EMP

WHERE COMM > 0

SELECT *

FROM EMP

WHERE COMM IS NOT NULL

SELECT *

FROM EMP

WHERE COMM IS NULL

slide30

NOT Operator

SELECT 'Not fornd !' INTO :COL1

FROM EMP

WHERE EMPNO <> '1234'

SELECT 'OK' INTO :COL1

FROM DUAL

WHERE NOT EXISTS ( SELECT '' FROM EMP

WHERE EMPNO = '1234')

SELECT *

FROM EMP

WHERE ENAME LIKE '김%'

AND JOB <> 'SALES'

SELECT *

FROM EMP a

WHERE a.ENAME LIKE '김%'

AND NOT EXISTS( SELECT '' FROM EMP b

WHERE a.ENAME = b.ENAME

AND b.JOB = 'SALES')

SELECT *

FROM EMP

WHERE ENAME LIKE '김%'

MINUS

SELECT *

FROM EMP b

WHERE b.JOB = 'SALES'

slide31

참고자료

Modified Column Values Prevent Use of Index

ㅇ where sal * 1.1 > 50 => where sal > 50 / 1.1

ㅇ where to_char(hiredate, ‘YYMMDD’) = ’040223’

=> where hiredate = to_date(‘040223’,’YYMMDD’)

ㅇ where substr(job,1,4) = ‘DATA’ => where job like ‘DATA%’

ㅇ where col1||col2 = :FLD

=> where col1 = substr(:FLD,1,3) and col2 = substr(:FLD,4,2)

ㅇ where ename is not null => where ename >= 0

ㅇ where deptno != 30 => where deptno > 30 or deptno < 30

ㅇ number와 char의 비교: where empno = ‘5224’Index(X)

ㅇ number와 varchar2의 비교: where empno = ‘5225’Index(X)

ㅇ char와 varchar2의 비교: where a.dept = b.dept Index(X)

ㅇ Composite Indexes (prjno, empno)

- where prjno = 100 and empno = 100 Index(O)

- where prjno = 100 Index(O)

- where empno = 100 Index(X)

slide32

ㅇ Optimizer : 모든 관계형 DBMS에서 SQL문장을 처리하는 질의 최적화기로 30년 이상 축적된 기술을 포함

하고 있는, 인간의 지능이 가장 많이 녹아 있는 복잡한 소프트웨어이다

ㅇ 구성요소 : 질의 변환(Query Rewriter) + 실행 계획 생성(Plan Generator) + 비용 산정(Estimator)모듈

ㅇ 종 류 : RBO(Rule based optimizer), CBO(Cost based optimizer)

ㅇ 오라클 Optimizer_mode

- CHOOSE : Analyze 수행시 All_Rows로 동작하고, 미수행시 Rule로 동작된다.

- RULE : 테이블과 인덱스의 통계존재 여부에 상관없이 RBO를 호출함. 규칙위주의 실행계획을 만듬.

- FIRST_ROWS(CBO) : 하나의 ROW를 가장 빨리 가져올수 있도록 최적화됨. 주로 NESTED LOOP

방식을 선호함.

- ALL_ROWS (CBO) : 전체 ROW를 가장 빨리 가져올수 있도록 최적화함.

* 힌트 사용시 RULE Hint를 제외하고 나머지는 CBO를 호출한다.

select /*+ ordered use_nl(d) */ ename, sal

from emp e, dept d

where e.deptno = d.deptno and d.loc = 'SEOUL'

ㅇ ANALYZE수행여부

ㅇ Optimizer 정책은?

OPTIMIZER

slide33

SQL해석

실행

계획

작성

실행

참조

참조

추출

OBJ$

t

r

a

n

s

a

c

t

i

o

n

COL$

c

u

s

t

o

m

e

r

IND$

TAB$

account

VIEW$

DATA

DATA Dictionary

1. RDB, 왜 쉽고도 어려운가?

OPTIMIZER

select col1,

col2*10, . .

from account x,

custommer y,

transection z

where x.acct = z.acct

and y.cust = z.cust

and jdate = ‘970609’;

SQL

  • 사용자는 요구만 하고 OPTIMIZER가 실행계획 수립
  • 수립된 실행계획에 따라 엄청난 수행속도 차이 발생
  • 실행계획 제어가 어렵다.
  • OPTIMIZER가 좋은 실행계획을 수립할 수 있도록 종합적이고 전략적인 FACTOR를 부여
  • 비절차형, 집합적으로 접근해야 함
  • SQL이 어떤 역할을 담당하도록 구현할 것인가?
  • 시스템에 과부하 발생량을 최소화하는 SQL구사

참고자료 : 대용량DB솔류션

slide34
규칙기반 최적화(Rule-Based Optimizer)

비용기반 최적화(Cost-Based Optimizer)

2. OPTIMIZER 종류

3 optimizer

Nested Loops ?

  • Sort Merge ?
  • Hash ?
  • . . . . . . . ?

처리방법

결정

무자격 배제

Driving

결정

처리순서 결정

COL$

사용 가능한 액세스형태 선별

IND$

경선 (Rule, Cost)

  • tab1 - tab2 - tab3 ?
  • tab2 - tab1 - tab3 ?
  • tab3 - tab2 - tab1 ?

Driving 선택

(나머지는 야당)

  • . . . . . . . ?
3. OPTIMIZER의 역활

select * from tab1x, tab2 y, tab3 z

where x.key = y.key

and z.key = x.key

and x.col1 = ‘10’

and x.col2 <> 111

and x.col3 like ‘ABC%’

and y.col4 between ‘10’ and ‘50’

OPTIMIZER

Row SOURSE

생성

실행계획

작성

참조

OBJ$

TAB$

VIEW$

DATA Dictionary

slide36

Index,

Clustering

SQL 형태

B

CB

A

JOIN

OPERATOR

SCALAR

SUB QUERY

FUNCTION

Analytic information

연산자 형태

로우 수

=, LIKE, …

A = ‘상수’,

A = :변수…

분포도

최대값 ...

OPTIMIZER

SQL Hints

Optimizer Mode

SELECT

/*+ Use_nl(a b) */ …

FROM ...

DBMS, Version, H/W, DB Parameter

Distributed DB

RULE

ALL_rows

First_rows

Oracle

DB LINK

Sort, hash

V7, 8i, 9i...

4. Optimizer에 영향을 미치는 요소

* 개발뿐만 아니라 운영시

에도 지속적인 정책 필요

참고자료 : 대용량DB솔류션

slide37

5. RULE Based Optimizer

Rank

Access Path

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

Single row by ROWID

Single row by cluster join

Single row by hash cluster with unique or primary key

Single row by unique or primary key

Cluster join

Hash cluster key

Indexed cluster key

Composite key

Single column indexes

Bounded range search on indexed columns

Unbounded range search on indexed columns

Sort merge join

MAX or MIN of indexed columns

ORDER BY on indexed columns

Full table scan

slide38
비용 기반 접근 방식에서의 접근 경로의 선택

문장에 대해 사용 가능한 접근 경로

각 접근 경로와 그 접근 경로들간의 상호관계에 따른 실행에 필요한

비용

접근 경로의 선택을 위하여 고려하는 것 : Selectivity

Selectivity를 위한 정보

WHERE 절에 사용되는 연산자

WHERE 절에 사용되는 UNIQUE나 PRIMARY KEY를 가지는 칼럼들

그 테이블에 대한 통계치

각종 설정값 : Sort, hash영역 등

6. Cost Based Optimizer

slide39

7. OPTIMIZER의 취사 선택

EMPNO Index만 사용

SELECT *

FROM EMP

WHERE ENAME LIKE 'AB%'

AND EMPNO = '7890'

RANKING 의 차이

ENAME or

JOB index중

하나만 사용 ,

혹은 Full scan

SELECT *

FROM EMP

WHERE ENAME LIKE 'AB%'

AND JOB LIKE 'SA%'

INDEX Merge 회피

FULL Table Scan

SELECT *

FROM EMP

WHERE EMPNO > '10'

Low COST 의 선택

JOB Index만 사용

SELECT /*+ INDEX(EMP JOB_IDX) */ *

FROM EMP

WHERE ENAME LIKE 'AB%'

AND JOB LIKE 'SA%'

HINT 에 의한 선택

slide40

8. COST_based vs RULE_based

SELECT * FROM EMP

WHERE JOB = 'SALESMAN'

AND EMPNO = '7890'

INDEX merge

(and_equel)

Rule

based

SELECT * FROM EMP

WHERE ENAME LIKE 'AB%'

AND EMPNO = '7890'

항상 EMPNO

Index만 사용

SELECT * FROM EMP

WHERE ENAME BETWEEN '1101' AND '1210'

AND JOB LIKE 'SA%'

항상 나중에

생성된

Index만 사용

SELECT * FROM EMP

WHERE JOB = 'SALESMAN'

AND EMPNO = '7890'

INDEX merge (and_equel),

특정 iNDEX

Cost

based

SELECT * FROM EMP

WHERE ENAME LIKE 'AB%'

AND EMPNO = '7890'

분포도에 따라

ENAME index

도 사용

분포도에 따라

index 사용,

혹은 full scan

SELECT * FROM EMP

WHERE ENAME LIKE 'AB%'

AND JOB LIKE 'SA%'

slide42
SQL튜닝이란?

유연한 언어

서로 다른 여러 개의 SQL문이 똑같은 결과를 가져올 수 있다.

최선의 선택

그러나, 주어진 환경이 똑같다면 오직 하나의 SQL만이 최상의 성능을 낼 수 있다.

데이터량, WHERE절, 인덱스사용, 최적화 등을 통해 SQL문의 성능을 판단하는 작업이 SQL 튜닝이다.

SQL 튜닝 개요
slide43
SQL의 공유(표준화)

ㅇ 이미 수립된 실행 계획을 공유해서 재 사용

- 구문 분석 비용 감소(Hard Parse)

- 메모리 사용 개선(Literal SQL에 의한 단편화현상)

ㅇ 동일한 SQL 문장 이란?

–텍스트 동일(대소문자 구분, 빈 칸, 주석 포함)

–사용자 및 참조 객체 동일

–바인드 변수의 데이터 형식 동일

ㅇ SQL 형식 표준을 정하여 코딩

–대소문자, 빈 칸, 주석 사용 규칙

아예 SQL에 주석 사용 금지 등

– SQL 문장을 동일한 위치에 따로 관리

–자주 쓰는 것은 PL/SQL로 대치

sql 3

ㅇ 부하의 감소

– 동일한 부하를 보다 효율적인 방법으로 수행

– 일반적인 SQL Tuning(DB모델링 & SQL형태 조정,

실행계획 조정, 자원소모 및 경합감소등)

ㅇ 부하의 조정

– 부하 정도에 따라 업무 조정

- 배치, 리포트 업무 등과 일반 업무(OLTP)를 분리

ㅇ 응용 프로그램별, 시간대별 부하의 병렬 수행

– 병렬로 수행하여 응답 시간을 크게 단축

– 과도한 사용은 일반 사용자에게 악 영향 가능성

– 주로 배치 업무에 많이 사용됨

SQL 튜닝의 3가지 접근방법
hints
Optimization Hints

/*+ ALL_ROWS */

/*+ FIRST_ROWS */

/*+ AND_EQUAL */

/*+ CLUSTER */

/*+ COST */

/*+ FULL */

/*+ USE_HASH */

/*+ HASH_AJ */

/*+ INDEX(TAB) */

/*+ INDEX_ASC */

/*+ INDEX_DESC */

가장 적합한 Hint를 선정하여 해당 SQL문에 기술

/*+ CHOOSE */

/*+ RULE */

/*+ STAR */

/*+ PARALLEL */

/*+ USE_HASH */

/*+ USE_CONCAT */

/*+ USE_MERGE */

/*+ USE_NL */

/*+ ORDERED */

/*+ ROWID */

Hints

* Instance 레벨 : Optimizer_Mode = All_Rows

* Session 레벨 : Alter Session Set Optimizer_Mode = Rule

* Sql 레벨 : Hints 사용

slide46

Select /*+ Ordered use_hash(a b) */

a.주민번호, a.이름, (select ….from.. Where…) 계급,

b.주소, fun_idc(xxx), sum(xx)

From 인원 a, (Select /*+use_nl(x y)*/…. -- In Line View

From … Where…) b

Where a.주민번호 = b.주민번호

and a.이름 in (Select …. From… Where….)

Group by ….

Union all (Minus, Intersect….)

Select /*+ rule */

c.주민번호, c.이름, (Select ….From… Where….)계급,

d.주소, fun_idc(xxx), sum(xx)

From 인원 c, (Select /*+all_rows*/…. From … Where…) d

Where c.주민번호 = d.주민번호

and c.이름 in (Select …. From… Where….)

Group by ….

데이터 연결방법

ㅇ JOIN

- Nested Loop

- Sort Merge

- Hash

- Cartesian

- Star …..

- Out

- Etc(natural, self join)

ㅇ OPERATOR

- Union All…..Group by

- Union…..Group by

- Intersect

- Minus

ㅇ SCALAR QUERY

ㅇ SUB QUERY

ㅇ FUNCTION

SQL TECH’

slide47

데이터 연결방법 1 : JOIN

SELECT 고객명, 상품구매일시, 금액 ………

FROM 고객 X, 상품구매내역 Y

WHERE X.고객번호 =Y.고객번호

AND X. 고객번호 =’1235’;

참고자료 : 대용량DB솔류션

slide48

가. NESTED LOOP JOIN

SELECT a.FLD1, ..., b.FLD1,...

FROM TAB1 a, TAB2 b

WHERE a.KEY1 = b.KEY2

AND a.FLD1 = 'AB'

AND b.FLD2 = '10'

TABLE ACCESS BY ROWID

TABLE ACCESS BY ROWID

KEY2= KEY1

FLD1='AB'

o

o

o

  • 순차적 (부분범위처리 가능)
  • 종속적 (먼저 처리되는 테이블의 처리범위에 따라 처리량 결정)
  • 랜덤(Random) 액세스 위주
  • 연결고리 상태에 따라 영향이 큼
  • 주로 좁은 범위 처리에 유리

운반

단위

FLD2 ='10' check

x

TAB1 TAB2

INDEX

(FLD1)

INDEX

(KEY2)

97 : 3

slide49

TABLE ACCESS BY ROWID

TABLE ACCESS BY ROWID

FLD1='AB'

FLD2='10'

a.KEY1= b.KEY2 를 조건으로 Merge

S

O

R

T

S

O

R

T

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

운반단위

TAB1 TAB2

INDEX

(FLD1)

INDEX

(FLD2)

나. SORT MERGE JOIN

SELECT /*+ use_merge(a b) */

a.FLD1, ..., b.FLD2,...

FROM TAB1 a, TAB2 b

WHERE a.KEY1 = b.KEY2

AND a.FLD1 = 'AB'

AND b.FLD2 = '10'

  • 동시적(무조건 전체범위처리)
  • 독립적(자기의 처리범위만으로 처리량 결정)
  • 스캔(Scan) 액세스 위주
  • 연결고리상태에 영향이 없음
  • 주로 넓은범위처리에 유리

* DB LINK

hash join

ㅇ 작은 table과 큰 table의 Join시에 유리.

ㅇ Equi Join에서만 가능.

ㅇ Driving Table에 Index를 필요로 하지 않는다.

ㅇ 각 table에 대하여 1번만 읽는다.

Customer

Partition

Hash Table

CustId

CustId

CustId

Hash

Hash

Name

HASH

HASH

a001

AA

a001

A1

a001

.....

b001

BB

b001

B1

b001

.....

b002

BB

b002

B2

....

c001

CC

c001

C1

c001

b002

.....

c002

CC

c001

C2

c002

....

Order

Partition

Hash Table

Ord Id

CustId

Cust Id

CustId

Amt

Hash

Hash

HASH

Seoul

r01

b001

a001

AA

a001

A1

a001

AA

a001

A2

r02

a001

....

b001

BB

b001

B1

New

r03

c001

b002

BB

b002

B2

....

r04

a001

b002

BB

b002

B2

Seoul

r05

b002

CC

c001

Seoul

C1

r06

b002

c001

다. Hash Join

SELECT B..Customer_Name, A.Order_amtFROM CUSTOMER B, ORDER A

WHERE B.Customer_Id = A.Customer_Id

서로 매칭되는 Partition별로 Hash Key를 이용하여 조인

slide52

데이터 연결방법 3 : SCALAR SubQuery

세번째 사용자 함수를 이용한 방법 또는 네번째 SCALAR SUBQUERY를 이용한 방법은 대상 집합을 항상 한 개의 ROW로만 데이터를 연결한 후 하나의 컬럼을 Return 받는 방법이다. 결과 집합은 세로의 확장은 전혀 없고 가로의 확장은 단지 하나의 컬럼으로 확장하기 때문에 집합의 확장은 상당히 제한적이다. 특히 사용자 함수를 사용하는 경우는 메인집합에서 추출된 ROW수만큼 반복 수행되기 때문에 응답속도에 큰 영향을 미치게 된다. SCALAR SUBQUERY도 사용자 함수와 비슷하지만 단지 Multi Block Buffer를 사용함으로써 Buffer에있는 데이터에 대해서는 대상 집합의 데이터를 다시 액세스하지 않는다는 점이 다르다.

slide53

S

O

R

T

운반단위

INDEX

TABLE

데이터 연결방법 4 : Function

Item별 Max Value 구하기

slide54

데이터 연결방법 5 : Sub Query

SUBQUERY를 이용한 방법은 집합을 확장하는 것이 아니라 단지 특정 집합의 데이터를 추출하기 위한 조건으로 사용하는 데이터 연결 방법이다. 왜냐하면 SUBQUERY에 사용된 집합의 컬럼들을 최종 결과집합으로 추출이 불가능하기 때문이다. 그리고 SUBQUERY는 경우에 따라서 메인 집합의 추출하기 위한 조건의 값을 제공하는 제공자 역할을 담당하는 경우와 메인 집합에서 추출된 집합을 단지 체크하기 위한 확인자 역할을 담당하는 경우등 두가지 경우로 데이터를 연결한다.

slide55

ㅇ 어플리케이션의 코딩량을 줄일수 있다.

ㅇ SQL문장을 사용하지 않고 데이타를 제어

ㅇ 데이타베이스 설계시 정의된 설계 사상에 대하여 데이타베이스 Level에서 사전 정의

- 데이터의 일관성 및 일치성 그리고 무결성을 응용 프로그램의 추가 부담이 없이 보장

ㅇ 과도하게 사용하지 말것 : 데이타베이스 운영시 애로가 될수있다

PK(Primary Key)

해당 칼럼에 Null값을 허용하지 않고 유일한 값만 허용된다.

참조무결성 보장 제한조건으로 Parent Table의 해당 칼럼의 값만 허용한다.

FK(Foreign Key)

해당 칼럼에 유일한 값만 허용한다.

Unique

해당 칼럼에 Null값을 허용하지 않는다.

Not null

해당 칼럼에 정해진 값만 허용한다.

Check

제한조건은 아니지만 해당 칼럼에 Default 값을 선언할수 있고

Not null과 조합되어 Sql튜닝에 유용하게 사용된다.

Default

데이타베이스 Constraints(제한조건)

slide56
HARD PARSING, LITERAL SQL에 의한 부하를 줄일것

- SQL이 공유되도록

데이터베이스에 접근하는 횟수를 줄일 것

- 고급SQL 및 DB Constraints(제한조건) 사용

고급 SQL(DECODE, CASE, Analytic Function, RI SQL, SQL MODEL) 활용

인덱스가 사용되도록 WHERE절을 기술할 것

DB모델에 맞는 SQL형태 사용

루핑문내 SQL문 사용자제(만일 어쩔수없이 사용한다면 완전 튜닝수행)

널 값에 대한 완전한 이해(DB Default Option사용)

DBMS의 자원소모(Performance Statistics)와 자원경합(Waiting event)을

이해하고 사용할것

옵티마이저를 이해하고 최적의 SQL이 되도록

실행계획을 제어할것(SQL형태 변경, 모델링 변경, 실행계획 변경 등)

SQL 관련 권고사항
slide57
고급 SQL 활용

Decode, Case

Analytic Function

Super Update

…………….

다수의 처리를 최대한 단순하게 처리가능

시스템에 영향 최소화

유지보수 업무수행시 편리

사용되는 SQL횟수를 줄일수 있다.

데이타베이스 튜닝은 SQL튜닝으로….

참고자료 : 대용량DB솔류션

sql decode
고급 SQL 활용 예 : Decode

요구사항

판매일자와 함께 각각의 제품에 대한 판매 내용이 있을 때 전체 판매에 대한 수량과 매출액 등의 통계자료를 보고싶은 것이다.

단, 통계자료를 볼 때 제품명이 HP인 제품은 저가여서 제품별로만 봐도 되니까 모델 별은 무시하고 제품별로만 수량과 매출액 합계를 내고, 제품명이 LD인 제품은 고가이므로 각 모델별로 수량과 매출액 합계를 내고, 제품명이 PP인 제품은 저가 단품들이므로 모든 모델을 하나로 몰아서 기타로 하여 수량과 매출액에 대한 합계를 구하라.

그러나 제품명이 PP인 제품 중에서 모델명이 P530C인 제품은 최근에 개발한 신제품이므로 기타에 합치지 말고 별도로 빼내어 모델명까지 구분하도록 하라.

sql decode1

제품명이 HP인 제품은 저가여서 제품별로 만 봐도 되니까 모델 별은 무시하고 제품별로만 수량과 매출액 합계를 내고, 제품명이 LD인 제품은 고가이므로 각 모델별로 수량과 매출액 합계를 내고, 제품명이 PP인 제품은 저가 단품들이므로 모든 모델을 하나로 몰아서 기타로 하여 수량과 매출액에 대한 합계를 구하라.

그러나 제품명이 PP인 제품 중에서 모델명이 P530C인 제품은 최근에 개발한 신제품이므로 기타에 합치지 말고 별도로 빼내어 모델명까지 구분하도록 하라.

고급 SQL 활용 예 : Decode

SELECT DECODE(제품,'HP', 제품, 'LD', 모델명, 'PP',

DECODE(모델명, 'P530C', 모델명, '기타')),

SUM(수량), SUM(매출액)

FROM 매출테이블

WHERE 매출일 LIKE '199610%'

GROUP BY DECODE(제품,'HP', 제품, 'LD', 모델명, 'PP',

DECODE(모델명, 'P530C', 모델명, '기타'));

slide60
고급 SQL 활용 예

요구사항

1개의 레코드에서 3개 이상 값이 있는 행을 선별

sql decode in line view
고급 SQL 활용 예 : Decode & In Line View

select *

from (select y.aa, y.a1, y.a2, y.a3, y.a4, y.a5, y.a6,

y.x1 + y.x2 + y.x3 + y.x4 + y.x5 + y.x6 “y1”

from (select aa,

decode(a1,null,0,1) x1,

decode(a2,null,0,1) x2,

decode(a3,null,0,1) x3,

decode(a4,null,0,1) x4,

decode(a5,null,0,1) x5,

decode(a6,null,0,1) x6,

a1, a2, a3, a4, a5, a6

from ROTTO) y

)

where y1 >= 3;

sql case
고급 SQL 활용 예 : Case

아래 테이블에서 70대, 80대, 90대, 100세 이상의 수를 각각 구하라는 요구가 있다고

하자. 이것은 count(decode())와 case절을 이용하여 다음과 같이 구할 수 있다.

select count(decode(sign(Ages-69),1,decode(sign(Ages-80),-1,1)) "70대",

count(decode(sign(Ages-79),1,decode(sign(Ages-90),-1,1)) "80대",

count(decode(sign(Ages-89),1,decode(sign(Ages-100),-1,1)) "90대",

count(decode(sign(Ages-99),1,1) "100세이상"

from tab ;

Ages

-----

100

96

93

90

88

85

79

76

76

72

SELECT COUNT(CASE WHEN age BETWEEN 70 AND 79 THEN 1 END) as "70대",

COUNT(CASE WHEN age BETWEEN 80 AND 89 THEN 1 END) as "80대",

COUNT(CASE WHEN age BETWEEN 90 AND 99 THEN 1 END) as "90대",

COUNT(CASE WHEN age > 99 THEN 1 END) as "100세이상"

FROM tab ;

SELECT SUM(CASE WHEN age BETWEEN 70 AND 79 THEN 1 ELSE 0 END) as "70대",

SUM(CASE WHEN age BETWEEN 80 AND 89 THEN 1 ELSE 0 END) as "80대",

SUM(CASE WHEN age BETWEEN 90 AND 99 THEN 1 ELSE 0 END) as "90대",

SUM(CASE WHEN age > 99 THEN 1 ELSE 0 END) as "100세이상"

FROM tab ;

sql update

데이터 처리

DATA

SQL

DISPLAY

로직처리,

화면제어

고급 SQL 활용 예: Update

Client/Server

SELECT *

FROM TAB1

WHERE SALE_DATE LIKE ‘199810%’ ;

UPDATE TAB1

SET (COL1=DECODE(…),

COL2=100,...

WHERE KEY = :KEY ;

매우 많은 로우가

리턴되고 SQL이

반복수행

Server 내에서 모두 수행하고

성공여부만 리턴

UPDATE TAB1 x

SET (COL1, COL2,….) =

(SELECT DECODE(…), 100, ...

FROM TAB2 y

WHERE x.KEY = y.KEY … )

WHERE SALE_DATE LIKE ‘199810%’

어떤 SQL을 사용하느냐에 따라 처리 주체가 달라지며 수행속도와 네트워크 부하에 큰 차이

a nalytic f unction v 8 1 6
Analytic Function 소개 (v 8.1.6 이상)

SQL이 여러 분야에서 다양하게 사용되긴 하지만, 분석작업을 위한 강력한 기능을 가지고 있지는 않다.

때문에 MOVING AVERAGES, RANKINGS, AND LEAD/LAG COMPARISONS 같은 기본적인 BUSINESS INTELLIGENCE CALCULATION 을 수행하기 위해서는 표준 SQL외에 과도한 프로그램밍 작업을 요구하게 된다. 뿐만 아니라 이러한 작업은 간혹 시스템 성능에 치명적인 장애가 될 수도 있다.

ORACLE8i 8.1.6 에서는 이러한 요구들을 다루기 위해 BUSINESS INTELLIGENCE CALCULATION에 대한 새로운 FUNCTIONS들을 제공하게 된다. 이 FUNCTIONS 들은 분석적인 작업에 유용하기 때문에 ANALYTIC FUNCTIONS 이라고 하며, 현재 SQL 표준에 추가중이다

Analytic SQL

Analytic SQL

Capabilities

Aggegation

Capabilities

Models

=

+

+

  • Rank, N-tile, Moving averages
  • Cumulative sums, Lag/Lead
  • Ratio-to-Report, Inverse Percentiles
  • FIRST/LAST values
  • model
  • Rollup
  • Cube
  • Grouping
slide65

Analytic SQL Capabilities : (Rank & Dense_rank)

SELECT deptno,empno, sal, RANK() OVER (ORDER BY sal) AS asc_rank, RANK() OVER (ORDER BY sal desc nulls last) as desc_rankFROM emp;

SELECT deptno, empno, sal, RANK() OVER (ORDER BY sal desc, empno

nulls last) as desc_rank FROM empWHERE DEPTNO = 30;

slide66

Analytic SQL Capabilities : Windowing

Windowing Clause :

O ROWS | RANGE

WINDOW에서 각 ROW에 대해 정의하는 부분으로 ROWS는

물리적인 단위로 WINDOW를 지정하는 것을 말하며 RNAGE

는 논리적인 상대번지로 WINDOW를 지정하는 것을 말한다.

O BETWEEN....AND

WINDOW에서 START POINT와 END POINT를 지정하는 절

로서 첫 번째 표현식은 START POINT를 나타내며 두 번째

표현식은 END POINT를 나타낸다.

O UNBOUNDED PRECEDING

"PARTITION의 첫 번째 ROW에서 WINDOW가 시작된다"는

것을 나타낸다.

select deptno,empno,sal,sum(sal)over (partition by deptno order by empno rows unbounded preceding ) csumfrom emp;

select deptno,hiredate,sal,avg(sal)over( partition by deptno order by hiredate range interval '6' month preceding) mavg from emp;

slide67

Analytic SQL Capabilities : Ratio_To_Report

RATIO_TO_REPORT

어떤 한 집합의 합계에 대하여 각각의 비율을 구하는 report function.

RATIO_TO_REPORT (<value expression1>) OVER ([PARTITION BY <value expression2>[,...]])

다음은 부서 전체 월급에 대하여 직업별 비율을 구하기 위하여 RATIO_TO_REPORT를 사용한 사례이다.

select deptno,job,sum(sal) as sum_sal,sum(sum(sal)) over(partition by deptno) sum_total,ratio_to_report(sum(sal)) over(partition by deptno) as max_sum_salfrom emp agroup by deptno,job

slide68

Analytic SQL Capabilities : NTile

SELECT calendar_month_desc AS MONTH ,

TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$,

NTILE(4) OVER (ORDER BY SUM(amount_sold)) AS TILE4

FROM sales, products, customers, times, channels

WHERE sales.prod_id=products.prod_id

AND sales.cust_id=customers.cust_id

AND sales.time_id=times.time_id

AND sales.channel_id=channels.channel_id

AND times.calendar_year=2003

AND prod_category= 'Men'

GROUP BY calendar_month_desc;

slide69

N-Tile Results

MONTH SALES$ TILE4

-------- -------------- ---------

1999-10 4,373,102 1

1999-01 4,754,622 1

1999-11 5,367,943 1

1999-12 6,082,226 2

1999-07 6,161,638 2

1999-02 6,518,877 2

1999-06 6,634,401 3

1999-04 6,772,673 3

1999-08 6,954,221 3

1999-03 6,968,928 4

1999-09 7,030,524 4

1999-05 8,018,174 4

Quartile 1

Quartile 2

Quartile 3

Quartile 4

aggegation sql capabilities rollup
Aggegation SQL Capabilities: Rollup

SELECT DECODE(GROUPING(dname), 1, 'All Departments',dname) AS dname,

DECODE(GROUPING(job), 1, 'All Jobs', job) AS job,

COUNT(*) "Total Empl",

AVG(sal) * 12 "Average Sal"

FROM EMP, DEPT

WHERE DEPT.DEPTNO = EMP.DEPTNO

GROUP BY ROLLUP (DNAME, JOB);

아래 결과에서 2개의 GROUPPING COLUMNS에 대하여 다음과 같은 3 LEVEL의 SUBTOTAL이 생성된 것을 볼 수

있다.

. GROUP BY 로 부터 생성되는 표준 집계

. DNAME별 모든 JOB의 SUBTOTAL

. GRAND TOTAL

GROUP

BY

DNAME

LEVEL

GRAND TOTAL

aggegation sql capabilities cube
Aggegation SQL Capabilities: Cube

SELECT DECODE(GROUPING(dname), 1, 'All Departments',dname) AS dname,

DECODE(GROUPING(job), 1, 'All Jobs', job) AS job,

COUNT(*) "Total Empl",

AVG(sal) * 12 "Average Sal"

FROM EMP, DEPT

WHERE DEPT.DEPTNO = EMP.DEPTNO

GROUP BY CUBE (DNAME, JOB); subtotal은 2의n승 = 4개 생성

GROUP

BY

DNAME

LEVEL

JOB

LEVEL

GRAND TOTAL

slide72

SQL MODEL Capabilities :Model

  • Language: Spreadsheet-like calculations in SQL
    • Inter-row calculation. Treats relations as an N-Dim array
    • Symbolic references to cells and their ranges
    • Multiple Formulas over N-Dim arrays
    • Automatic Formula Ordering
    • Recursive Model Solving
    • Model is a relation & can be processed further in SQL
    • Multiple arrays with different dimensionality in one query
  • Performance
    • Parallel Processing in partitioning & formulas
    • Multiple-self joins with one data access structure
    • Multiple UNIONs with one data access structure
  • Why Better?
    • Automatic Consolidation (models as views – combine using SQL)
    • One version of truth (calc directly over data base, no exchange)
slide73

SELECT SUBSTR(country,1,20), SUBSTR(prod,1,15), year, s

FROM sales_view

MODEL

PARTITION BY (country)

DIMENSION BY (prod, year)

MEASURES (sale s)

(s['Bottle', 2002] = s['Bottle', 2001] + s['Bottle', 2000],

s['Suntan Lotion', 2002] = s['Suntan Lotion', 2001],

s['All_Products', 2002] = s['Bottle', 2002] + s['Suntan Lotion', 2002])

ORDER BY country, prod, year;

SUBSTR(COUNTRY,1,20) SUBSTR(PROD,1,1 YEAR S

-------------------- --------------- ---------------------

France All_Products 2002 25,509,995.40

France Bottle 1998 269,995.02

France Bottle 1999 357,842.17

France Bottle 2000 625,473.44

France Bottle 2001 354,302.96

France Bottle 2002 979,776.40

France Suntan Lotion 1998 16,110,519.40

France Suntan Lotion 1999 18,584,977.60

France Suntan Lotion 2000 30,123,863.60

France Suntan Lotion 2001 24,530,219.00

France Suntan Lotion 2002 24,530,219.00

slide74

select channel_desc, prod_category, year, quantity_sold

From

(select c.channel_desc, p.prod_category, t.calendar_year year,

sum(s.quantity_sold) quantity_sold

from sales s, products p, channels c, times t

where s.prod_id = p.prod_id

and s.channel_id = c.channel_id

and s.time_id = t.time_id

group by c.channel_desc, p.prod_category, t.calendar_year)

sales

where channel_desc = 'Direct Sales'

Model

partition by (channel_desc)

dimension by (prod_category, year)

measures (quantity_sold)

rules (quantity_sold['Hardware', 2002] = quantity_sold['Hardware', 2001] * 1.10)

order by channel_desc, prod_category, year;

slide75

CHANNEL_DESC PROD_CATEGORY YEAR QUANTITY_SOLD---------------- ------------------------------ ------ -------------Direct Sales Electronics 1998 7758Direct Sales Electronics 1999 15007...Direct Sales Hardware 2000 1970Direct Sales Hardware 2001 2399Direct Sales Hardware 2002 2638.9Direct Sales Peripherals and Accessories 1998 44258...Direct Sales Software/Other 2000 64483Direct Sales Software/Other 2001 49146

slide77
메모리 튜닝

Hit Ratio

Free Memory의 유지

스와핑(Swapping)과 페이징(Paging)의 최소화

논리적 저장구조

물리적 저장구조와의 관계

Tablespace, Extent, Block

저장구조의 역할

디스크의 분산배치

Rollback Segment

EXTENTS

SQL 최적화를 위한 Tracing Tool

Cost-Based의 Optimizer를 위한 Analyzer

데이터베이스 튜닝
slide78

Shared SQL Area

Dictionary Cache

SQL, PL/SQL 문장의 텍스트

Data Dictionary

SQL, PL/SQL 문장의 분석된 형태(Parsed form)

Character Set

Network Security

Etc…

SQL, PL/SQL Execute Plan

Oracle Database Architecture

SNPn

LCKn

RECO

Pnnn

Snnn

Dnnn

INSTANCE = SGA+BGP

Back Ground PRO’

SGA

PMON

LGWR

Redo Log Buffer

Shared Pool

Data Buffer Cache

SMON

CKPT

DBWn

ARCH

SELECT EMPNO, NAME

FROM EMP

WHERE EMPNO = :NO ;

…….. = ‘123’;

User

Server P

LISTENER

Data File

DBMS = Instance + DB

* ORACLE DBA과정 : SQL/PLSQL, Administration, Backup & Recovery, Network, Tunnig

slide79
메모리 튜닝은 성능향상/추가투자의 관점

더 많은 메모리 = 더 적은 물리적 I/O = 더 빠른 반응시간

보다 적은 물리적 I/O가 되도록 구현

메모리 추가를 위해 System Monitor를 이용하여 진단

할당된 메모리 자원에 맞도록 사용자 Process의 수와 OPEN할 수 있는 CURSOR의 수 등을 할당, 제어할 것

이런 경우는 메모리를 더 사라 !!!

Monitoring 의 결과 Hit Ratio 가 50 % 이하이고

Dictionary Cache 및 Application 을 이미 튜닝했는데

Response Time 이 형편없고

Free Memory 도 없는 경우 !

메모리 튜닝
hit ratio
Hit Ratio란?

Query에 의해 요구된 데이터가 메모리에서 발견될 백분율

메모리의 효율성을 판단하는 근거가 되는 수치

Hit Ratio 계산

Hit Ratio = (Logical Reads - Physical Reads) / Logical Reads

Hit Ratio에 영향을 미치는 요소

Dictionary Table Activity

Temporary Segment Activity

Rollback Segment Activity

Index Activity, Table Scan

* Buffer cache hit, lib cache hit

Hit Ratio
free memory
최소한 전체 메모리의 5% 정도의 Free Memory를 유지

지속적인 Monitoring

OS에서 제공하는 Command 이용

CRON 혹은 Daemon process를 이용해 지속적으로 Reporting

Peak Time시의 상태를 중점 확인

Free Memory가 부족한 경우에는

OS의 Kernel과 RDBMS의 Kernel이 사용하는 영역을 명확히 계산

RDBMS에 의해 사용되는 공통영역(즉 SGA)과.

사용자 프로세스에 의해 사용되는 메모리 영역을 적절히 배분하여 Free Memory를 유지할 수 있도록 조정

Free Memory의 유지
swapping
OS 메카니즘의 하나로 메모리크기의 제한요소를 극복하기 위해 전체 프로세스를 디스크로 이동시켜 메모리를 재 확보하는 기능

Memory 부족 시 심각한 오버헤드 유발

해결방안

불필요한 시스템 Demon프로세스와 애플리케이션 프로세스를 실행하지 않음

메모리 추가

타 시스템으로의 부하분산

DBMS에서 사용하는 Memory영역(SGA)의 최적화로 사용자 프로세스에 의해 사용되는 메모리 영역을 적절히 배분

스와핑(Swapping)의 최소화
paging
프로세스 전체의 이동이 요구되는 스와핑과는 달리 페이징은 메모리의 재확보를 위해 필요한 프로세스의 해당 Page를 디스크로 이동(LRU알고리즘 사용)

소량의 Page-Out이 시스템 성능에 끼치는 영향은 그리 크지 않으나 Paging양이 증가할 수록 시스템의 성능은 급격히 저하

해결방안

메모리 추가

타 시스템으로의 부하분산

페이징(Paging)의 최소화
sga database buffer cache
테이블, 인덱스, Rollback Segments, 클러스터등 데이터베이스 블록의 내용을 보관하는 메모리영역

Buffer Cache 에 많은 양의 데이터블록을 보관하고 있을 수록 성능향상에 도움이 됨

효과 : Online Transaction 처리의 경우 5 - 20 % 의 성능향상을 기대할 수 있음

Test 와 Monitoring 을 반복, 가장 적합한 수치를 지정

SQL튜닝과 병행하여 실시 *

SGA

Redo Log

Buffer

Shared

Pool

Database Buffer Cache

SGA - Database Buffer Cache 튜닝
sga shared buffer pool
Shared SQL Area (Library Cache) 해석(Parse)된 SQL문을 보관

충분히 지정하는 것이 바람직

Session

Data

SGA - Shared Buffer Pool 튜닝

Shared Buffer Pool

Shared SQL and PL/SQL

Dictionary

Cache

A

B

C

D

E

User1

SQL Statement D

User2

SQL Statement D

User3

SQL Statement D

sga redo log buffer
거래처리(Transaction)에 의해 일어난 데이터베이스 버퍼블록의 변화를 보관하는 메모리 영역

LGWR 라는 ORACLE Process 에 의해 Redo Log File 에 Logging 되므로 Log Buffer 의 크기가 작으면Disk 에 자주 Write 하게 되고, 따라서 성능저하의 원인이 될 수 있음

SGA

Redo Log

Buffer

Shared

Pool

Database Buffer Cache

SGA - Redo Log Buffer
slide87
Database Schema(user, role, table, index, cluster, procedure, function, db link …)

Tablespace

Segments

Objects

논리적 저장구조

index

Table

Table

index

Table

tablespace

Tablespace

Database

slide88
물리적 저장구조와의 관계

회계

Tablespace

구매

Tablespace

SYSTEM

Tablespace

테이블스페이스

데이타 파일

회계

datafile1

회계

datafile2

구매

datafile1

구매

datafile2

system datafile

Data file tunnig

tablespace extent block
Tablespace, Extent, Block와의 관계

Tablespace

Segment

112K

Extent

84K

2K

Extent

28K

Database Blocks

Object tunnig

Database

slide90
Segments

특정 데이타 유형을 저장하는 Extents 의 집합

Data Segment

Rollback Segment

Index Segment

Temporary Segment

Extents

Segment

Block

Block

Extent

Extent

저장구조의 역할
slide91
블럭

가장 작은 저장단위(보통 2K)

데이타 및 자신의 Header 정보 포함

Bytes/Block 결정요인

Operating System

DB_BLOCK_SIZE

구성요소

Row Data

Free Space(pct free)

Block Infomation

블럭의 구조

Common and variable Header

Table Directory

Row Directory

Free Space

Row Data

Data Block

slide92

Table 1

Index 1

Index 2

Table 2

공간 할당
  • 데이터 객체(테이블, 인덱스)의 디스크 할당

. Disk I/O Contention 감소

. 빠른 Access time

  • 테이블별 초기기억공간(Primary Space), 확장기억공간(Extent), 자유기억공간 (Free Space) 지정- 보관주기내의 데이터전체가 저장될수 있는 충분한 초기기억공간 할당- 예상되는 입력/수정에 적합하도록 자유공간 할당. Disk의 효율적 사용. 과다 Extents 억제
rollback segment
Before image를 기록하는데 사용

거래처리(transaction)의 양과 유형에 따라 관리

대규모 갱신이나 장시간 업무에는 대규모의 Rollback segment를 할당한다

Rollback Segment의 Monitoring

Rollback segment는 엄격히 통제하지 않으면 오래지 않아 크게 증가.

Rollback Segment의 Extent의 수

통계적으로 rollback segment 의 평균 extents 는 20 정도가

적당

Rollback Segment
rollback segment1
Rollback segment 의 갯수     

Minimum number of rollback segment =

CEIL (TRANSACTION / TRANSACTIONS_PER_ROLLBACK_SEGMENT)

최소 4 이상

일반적으로 4개의 거래처리당 1개의 Rollback segment를 할당

거래처리 유형에 따라 Rollback segment의 사용을 제어

Rollback segment의 크기는 생성할때 Storage 절에서 Optimal Option을 지정

Rollback segment의 개수/크기 관리
extents
테이블 및 인덱스의 Extension은 Disk I/O를 많이 유발하며 Disk I/O의 성능을 저하시킴

Monitoring

SYSDBA로 Connect

DBA_EXTENTS Dictionary View를 활용하여 각각 Object가 몇 개의 Extent로 구성되어 있는지 확인

Extents의 정리 작업 절차

테이블의 경우

인덱스의 경우

EXTENTS의 정리
extents deallocate
DEALLOCATE의 대상

TABLE, INDEX, CLUSTER들이 생성된 이후에 한번도 사용되어지지 않은 공간.

Extent 1

Extent 2

Extent 3

Free Space

Extents Deallocate

이후의 Data Insert위한 공간

Free 되어질 공간

High Water Mark

현재 Data가 차 있는 위치

free extents

Segment 1

Extent 1

Segment 2

Extent 1

Segment 2

Extent 2

Segment 2

Extent 3

Segment 2

Extent 4

Segment 1

Extent 2

Free

Space

Segment 1

Extent 1

Free

Space

Free

Space

Free

Space

Free

Space

Segment 1

Extent 2

Free

Space

Segment 1

Extent 1

Free

Space

Segment 1

Extent 2

Free

Space

FREE EXTENTS의 정리

1. 초기설정

2. Segment 2가 Drop되었을 때

3. Free Extents의 Coalesce

sql tracing tool
SQL_TRACE : Trace file의 생성

Initial Parameter file의 수정SQL_TRACE TRUE USER_DUMP_DEST directory TIMED_STATISTICS TRUE MAX_DUMP_SIZE number

TKPROF : Trace file의 해석

tkprof tracefile outputfile [EXPLAN=username/password]

EXPLAIN PLAN : Access Path의 Tracing

"PLAN_TABLE" 테이블 생성

구문 :

SQL>EXPLAIN PLAN [SET STATEMENT_ID='identifier']

FOR SELECT statement

SQL 최적화를 위한 Tracing Tool
cost based optimizer analyzer
테이블 및 인덱스의 각종 정보를 분석

작업내용

구문

. ANALYZE TABLE(or INDEX) 이름 COMPUTE STATISTICS ESTIMATE DELETE

. DBMS 제공 패키지 사용

분석내용은

USER_TABLES, USER_TAB_COLUMNS, USER_INDEXES Dictionary View 참조

Cost-Based의 Optimizer를 위한 Analyzer
slide100

자원소모(Peformance Statistics)

  • 자원경합(Waiting Event)

데이타베이스는 성능의 트랙킹과 측정을 위한 지표를 제공하는데, 여러 가지 리소스(I/O, CPU, 메모리)와 관련된 상세 성능 정보를 보여준다. Performance Statistics는 ‘리소스 사용 직접 연관지표’ 로서 이 정보의 수치 증가 여부로 특정 리소스의 과다사용에 대한 측정이 가능한 것이다.

Logical Read, Physical Read, table scan rows gotten, session pga memory, User Calls,

User rollbacks, Commit, Parse Count, Execution Count 등

리소스 사용 간접 연관지표라 할수 있으며 어떠한 리소스든지 한정적인 자원이므로 이에 대한 경합이 필연적으로 발생한다. Waiting의 대부분은 특정 리소스를 과다하게 사용하는 것은 아니지만,어쨌든 요구되는 자원에 대한 경합이 발생하고 있다는 사실을 의미하고 무엇인가 데이타베이스 전체적으로 자원의 과다사용으로 인한 성능 문제가 발생할 가능성을 예고하는 것이다.

DB File Sequential Read, DB File Scattered Read, Latch Free, Library Cache Pin/Lock

Pmon/Smon timer등

DBMS 성능관리 측정 기준
slide103

ㅇ 시스템에서 주로 발생하는 문제들

- 하드파싱 문제

- Literal SQL에 의한 메모리 단편화 문제

- 악성 SQL에 의한 자원 과다 소모 문제

·잘못된 SQL사용, 인덱스 문제등

- Lock or Blocking session 문제

- Session Leak

- SQL에 의한 자원경합(Waiting Event) 문제

- DB 모델링 & 디자인 잘못으로 인한 문제

- DB 미튜닝으로 인한 문제

slide104

Shared SQL Area

Dictionary Cache

SQL, PL/SQL 문장의 텍스트

Data Dictionary

SQL, PL/SQL 문장의 분석된 형태(Parsed form)

Character Set

Network Security

Etc…

SQL, PL/SQL Execute Plan

SQL 처리과정

SNPn

LCKn

RECO

Pnnn

Snnn

Dnnn

INSTANCE = SGA+BGP

Back Ground PRO’

SGA

PMON

LGWR

Redo Log Buffer

Shared Pool

Data Buffer Cache

SMON

CKPT

DBWn

ARCH

SELECT EMPNO,NAME

FROM EMP

WHERE EMPNO = ‘123’;

User

Server

Data File

LISTENER

Sql 처리순서

ㅇ Parse : Sintax, Semantics(User, Object, Privilege),

-> Optimizer (Excution Plan, Row Source생성),

Binding : 변수처리

ㅇ Execute : Physical Read, Logical Read

ㅇ Fetch

slide105

User

User

미공유

SQL

미공유

SQL

미공유

SQL

미공유

SQL

Loop….

if then ….

select ….

loop

if then….

update….

.

.

SELECT EMPNO,NAME

FROM EMP

WHERE EMPNO = ‘123’

ㅇ Mem’ Fragmentation

- ora 4031….

ㅇ 각종 contention 발생

- Latch, Hard Parse등

- Lib’ cache miss

ㅇ 자원소모

미공유

SQL

미공유

SQL

미공유

SQL

미공유

SQL

미공유

SQL

SELECT EMPNO,NAME

FROM EMP

WHERE EMPNO = ‘245’;

미공유

SQL

미공유

SQL

미공유

SQL

HARD PARSING TECH’

SNPn

LCKn

RECO

Pnnn

Snnn

Dnnn

SGA

PMON

LGWR

Redo Log Buffer

Shared Pool

Data Buffer Cache

SMON

CKPT

????

DBWn

ARCH

Server P

Data File

????

slide108

User

User

Literal

SQL

Literal

SQL

SELECT EMPNO,NAME

FROM EMP

WHERE EMPNO = ‘123’;

Literal

SQL

Literal

SQL

Literal

SQL

SELECT EMPNO,NAME

FROM EMP

WHERE EMPNO = ‘245’;

Literal

SQL

Literal

SQL

Literal

SQL

Literal

SQL

SELECT EMPNO,NAME

FROM EMP

WHERE EMPNO = ‘267’;

.

.

Literal SQL TECH’

SNPn

LCKn

RECO

Pnnn

Snnn

Dnnn

INSTANCE = SGA+BGP

Back Ground PRO’

SGA

PMON

LGWR

Redo Log Buffer

Shared Pool

Data Buffer Cache

SMON

????

CKPT

DBWn

ARCH

Server P

????

Data File

Loop….

if then ….

select ….

loop

if then….

update….

.

.

.

ㅇ Mem’ Fragmentation

- ora-4031….

ㅇ 비공유 Literal SQL사용으로

각종 contention이 발생하고

하드파싱을 유발하며, 메모리

단편화를 일으켜 DB가 다운

된다

slide109

해결방안

ㅇ Literal SQL을 Bind 변수를 사용하여 수정

// Create Command Object.

Cmd1= new msado15.Command();

Cmd1.putActiveConnection( Conn1 );

Cmd1.putCommandText( "SELECT * FROM Authors WHERE Au_ID < ?" );

// Create Parameter Object.

v1.putString( "P1" );

v2.putInt( 5 );

Cmd1.getParameters().Append(

Cmd1.CreateParameter( v1.getString(),

msado15.DataTypeEnum.adInteger,

msado15.ParameterDirectionEnum.adParamInput,

0,

v2));

ㅇ Cursor_Sharing 옵션 사용

Alter Session Set cursor_sharing= ‘FORCE’ or ‘SIMILAR’;

* 디비링크, 복잡한 쿼리에는 사용하지 말것

ㅇ 주기적인 Shared Pool 리프레쉬

alter system flush shared_pool;

slide110

Blocking Session or LOCK TECH’

LOCK & Waiting Session Monitoring

DB Session Monitoring

Unix Process Monitoring

slide111

USER B : update

code

USER A

USER B

USER C

USER C

SELECT 10

USER D

UPDATE 10->20

SELECT ?

SELECT ?

SELECT ?

USER E

UPDATE 10->30

.

.

.

.

UPDATE 10->60

USER F

USER G

USER H

COMMIT ;

SELECT ?

USER Z

Blocking Session or LOCK(계속)

USER A : update sal…

Commit

ㅇ Index Create, Drop

ㅇ Analyze 수행시

slide116

Blocking Session or LOCK (계속)

해결방법은?

ㅇ 권한분리 : 관리자와 사용자의 접근권한 및 시기, 수정 가능 칼럼 권한조정

ㅇ COMMIT 횟수 조절 : 하드웨어 성능, 사용자수와 관련하여 레코드 20 – 200개에 1회 수행

ㅇ DB 오브젝트 튜닝 : ini Trans, free list등

SELECT parsing_user_id, executions, sorts, buffer_gets, disk_reads, first_load_time, sql_text

FROM V$SQL

WHERE first_load_time between '2004-10-12/11:00:00' and '2004-10-12/11:10:00'

ORDER BY first_load_time

slide117

Session leak

발생원인은?

ㅇ 어플리케이션 코드 에러

ㅇ WAS 서버 이상동작(환경설정이상, 과부하등)

ㅇ 하드웨어 리소스 부족, Network 문제 등등

해결방법은?

ㅇ 어플리케이션 코드 수정

ㅇ WAS서버 환경설정 확인,

과부하 원인 제거 (SQL튜닝등)

ㅇ 하드웨어 업그레이드, Network 문제해결

DB

Client

WAS

DB

slide119

성능관리 예제 1

ㅇ 시스템 : 재무/회계, 인사, 전자입찰, 원격연수 시스템등 15개시스템

ㅇ 환 경 : ASP(CBD방법론), 오라클 DBMS 8.1.6, IIS, 사용자수 (4000 - 5000),

DB크기(0.5T), SUN5500(CPU 8, MEM 8G)

ㅇ 현 상 : 이유없는 시스템 다운현상 반복,

주요화면 속도 저하,

IIS 다운현상 심각(주 3 - 4회),

특정화면 조회시 시스템 다운현상,

전자입찰시 시스템 속도 저하 및 다운현상으로 민원제기가 많음.

ㅇ 사용자 : 기능은 좋으나 특정화면 속도가 너무 느리고 자주 시스템이 다운되어 믿을수 없다.

특정기능 사용시 시스템이 다운된다.

slide122

S

O

R

T

운반단위

INDEX

TABLE

ㅇ 시스템 : WAMIS 시스템

ㅇ 원문장 :

SELECT /*+ INDEX(S103 S103_IX1) */

MIN(GX), MIN(GY), MAX(GX), MAX(GY)

FROM OWIS.S103

WHERE gx < 16,777,216

ㅇ 속 도 : 개선전 8초 -> 0.2초

QUERY_PLAN

---------------------------------------------

SELECT STATEMENT cost = 4

.1 SORT AGGREGATE

.1 INDEX FAST FULL SCAN IX_S1031 NON-UNIQUE

ㅇ 원인 : min, max 함수로 인하여 index full scan이 일어나서 속도 저하

ㅇ 조 치 : 인덱스 2개 생성후 rownum을 이용하여 조치

create index ix2_s103 on owis.s103 (GY)

tablespace owis storage(initial 1m next 1m pctincrease 0) nologging;

create index ix1_s103 on owis.s103 (GX)

tablespace owis storage(initial 1m next 1m pctincrease 0) nologging;

ㅇ 수정문장

SELECT A.A , B.B , C.C , D.D

FROM (SELECT /*+ INDEX_ASC(S103 ix1_s103) */

GX A

FROM OWIS.S103

WHERE gx < 16777216

AND ROWNUM = 1) A,

(SELECT /*+ INDEX_ASC(S103 ix2_s103) */

GY B

FROM OWIS.S103

WHERE gx < 16777216

AND ROWNUM = 1) B,

(SELECT /*+ INDEX_DESC(S103 ix1_s103) */

GX C

FROM OWIS.S103

WHERE gx < 16777216

AND ROWNUM = 1) C,

(SELECT /*+ INDEX_DESC(S103 ix2_s103) */

GY D

FROM OWIS.S103

WHERE gx < 16777216

AND ROWNUM = 1) D

운반단위

INDEX

slide123

ㅇ 시스템 :재무시스템 거래처별지급내역

ㅇ 속 도 : 3분48초 -> 0.78초

SELECT B.NAME||'총계', '', '', '', '', '', '', '',

'', '', Sum (A.CASHAMT), Sum(A.CDAMT), Sum(A.INPAMT),

00, Count(B.NAME), ' '

FROM ofms.FACDCAUNM B,

( SELECT CSDAUN, CASHAMT, CDAMT, INPAMT

FROM ofms.FAABMDECD

WHERE CSDAUN = '01'

AND INPDT >= TO_DATE('2002/03/07', 'YYYY/MM/DD')

AND INPDT < TO_DATE('2002/03/08', 'YYYY/MM/DD') + 1

AND ACSLPNO >= '20020150278') A

WHERE A.CSDAUN = B.CODE

GROUP BY B.NAME

HAVING COUNT(B.NAME) > 1

UNION

SELECT B.NAME, TO_CHAR(A.INPDT,'YYYY/MM/DD'),

'일자소계', '', '', '', '', '', '' , '' ,

Sum (A.CASHAMT), Sum(A.CDAMT), Sum(A.INPAMT) , 01,

Count(B.NAME), ' '

FROM ofms.FACDCAUNM B,

(SELECT CSDAUN, INPDT, CASHAMT, CDAMT, INPAMT

FROM ofms.FAABMDECD

WHERE CSDAUN = '01'

AND INPDT >= TO_DATE('2002/03/07', 'YYYY/MM/DD')

AND INPDT < TO_DATE('2002/03/08', 'YYYY/MM/DD') + 1

AND ACSLPNO >= '20020150278') A

WHERE A.CSDAUN = B.CODE

GROUP BY B.NAME,TO_CHAR(A.INPDT,'YYYY/MM/DD')

HAVING COUNT(B.NAME) > 1

UNION

SELECT B.NAME, TO_CHAR(A.INPDT,'YYYY/MM/DD'), A.ACSLPNO,

REPLACE(C.TITCNT, Chr(13)||Chr(10), ' ') ,

A.CUSNAME, A.INBNAME, A.DPSNO, A.SAVNAME,

A.CDDPSSTS, A.CDCUSNO, A.CASHAMT , A.CDAMT,

A.INPAMT , 02, 1, C.ACCIND

FROM ofms.FACDCAUNM B, ofms.FAABMDECI C,

(SELECT INPDT, ACSLPNO, CUSNAME, INBNAME,

DPSNO, SAVNAME, CDDPSSTS, CDCUSNO,

CASHAMT, CDAMT, INPAMT, CSDAUN

FROM ofms.FAABMDECD

…………………………………

ㅇ 실행계획

QUERY_PLAN

-------------------------------------------------------

SELECT STATEMENT cost = 332

.1 SORT UNIQUE

.1 UNION-ALL

.1 FILTER

.1 SORT GROUP BY

.1 MERGE JOIN CARTESIAN

.1 TABLE ACCESS BY INDEX ROWID FACDCAUNM

.1 INDEX RANGE SCAN FACDCAUNM_PK_CODE UNIQUE

.2 SORT JOIN

.1 TABLE ACCESS BY INDEX ROWID FAABMDECD

.1 INDEX RANGE SCAN FAABMDECD_IDX_INPDT NON-UNIQUE

.2 FILTER

.1 SORT GROUP BY

.1 MERGE JOIN CARTESIAN

.1 TABLE ACCESS BY INDEX ROWID FACDCAUNM

.1 INDEX RANGE SCAN FACDCAUNM_PK_CODE UNIQUE

.2 SORT JOIN

.1 TABLE ACCESS BY INDEX ROWID FAABMDECD

.1 INDEX RANGE SCAN FAABMDECD_IDX_INPDT NON-UNIQUE

.3 HASH JOIN

.1 NESTED LOOPS

.1 TABLE ACCESS BY INDEX ROWID FACDCAUNM

.1 INDEX RANGE SCAN FACDCAUNM_PK_CODE UNIQUE

.2 TABLE ACCESS BY INDEX ROWID FAABMDECI

.1 INDEX RANGE SCAN FAABMDECI_PK_SLPNOSSENO UNIQUE

.1 SORT AGGREGATE

.1 FIRST ROW

.1 INDEX RANGE SCAN (MIN/MAX) FAABMDECI_PK_SLPNOSSENO UNIQUE

.2 TABLE ACCESS BY INDEX ROWID FAABMDECD

.1 INDEX RANGE SCAN FAABMDECD_IDX_INPDT NON-UNIQUE

ㅇ 원 인 : 8.1.6 버젼 optimizer는 first_rows optimizer에 의해 실행계획시

잘못된 실행계획을 세우는 확률이 매우 높아 rule optimizer로

변경 --first_rows 버그로 수정되었음

slide124

ㅇ 시스템 : 재무시스템 원천 영수증 발행화면

ㅇ 속 도 : 28.85초 -> 1.32초

ㅇ 원문장

SELECT A.SLPNO, B.WRTDT, A.IBENO,

DECODE(A.IVNN, NULL, A.ICONAME, A.IVNN),

B.TITCNT, B.PPSCNT, A.INCIND, TO_CHAR(NVL(A.PRNCD,0))

FROM ofms.FTSOMSRTM A, ofms.FAABMSLPM B

WHERE A.ACSDT BETWEEN TO_DATE('2002/01/01','YYYY/MM/DD')

AND TO_DATE('2002/04/04','YYYY/MM/DD')

AND B.SLPNO = A.SLPNO

AND A.INCIND ='사업소득'

AND A.AUNCD = '01'

AND A.CBENO = '306-82-00471'

ORDER BY A.SLPNO, B.WRTDT

ㅇ SQL TRACE

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0

------- ------ -------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 5 26.56 28.85 13938 911698 4 54

------- ------ -------- ---------- ---------- ----------

total 7 26.56 28.85 13938 911698 4 54

ㅇ 실행계획

Misses in library cache during parse: 1

Optimizer goal: RULE

Parsing user id: 5 (SYSTEM)

Rows Execution Plan

------- ---------------------------------------------------

0 SELECT STATEMENT GOAL: RULE

54 SORT (ORDER BY)

54 NESTED LOOPS

444919 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'FAABMSLPM'

54 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'FTSOMSRTM‘

446612 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'FTSOMSRTM_PK_SLPNOIBENO' (UNIQUE)

slide125

ㅇ 원 인 : B와 A 테이블의 관계가 1 : M 인데 부모테이블인 B의 건수가

44만건이고 자식 테이블인 A의 건수는 44만건인 비정상적

상황으로 RULE 옵티마이저가 부모 테이블인 B를 먼저

드라이빙하여 문제가 발생

ㅇ 조 치 : WHERE 조건에서 날짜조건인 A.ACSDT를 B.ACSDT로 변경하여

실핼계획 변경 유도

ㅇ 수정후 계획

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 5 1.34 1.32 0 45662 0 54

------- ------ -------- ---------- ---------- ---------

total 7 1.34 1.32 0 45662 0 54

QUERY_PLAN

---------------------------------

SELECT STATEMENT cost =

.1 SORT ORDER BY

.1 NESTED LOOPS

.1 TABLE ACCESS BY INDEX ROWID FAABMSLPM

.1 INDEX RANGE SCAN FAABMSLPM_IDX_ACSDT NON-UNIQUE

.2 TABLE ACCESS BY INDEX ROWID FTSOMSRTM

.1 INDEX RANGE SCAN FTSOMSRTM_PK_SLPNOIBENO UNIQUE

A

(446,612)

B

444,919

(?)

ㅇ 수정문장

SELECT A.SLPNO, B.WRTDT, A.IBENO,

DECODE(A.IVNN, NULL, A.ICONAME, A.IVNN),

B.TITCNT, B.PPSCNT, A.INCIND, TO_CHAR(NVL(A.PRNCD,0))

FROM ofms.FTSOMSRTM A, ofms.FAABMSLPM B

WHERE b.ACSDT BETWEEN TO_DATE('2002/01/01','YYYY/MM/DD')

AND TO_DATE('2002/04/04','YYYY/MM/DD')

AND B.SLPNO = A.SLPNO

AND A.INCIND ='사업소득'

AND A.AUNCD = '01'

AND A.CBENO = '306-82-00471'

ORDER BY A.SLPNO, B.WRTDT

slide126

ㅇ 시스템 : 재무시스템 추산부2화면

ㅇ 속 도 : 개선전 50초 -> 1.5초

ㅇ 원문장

SELECT A.BGTIND, A.BGDPT, A.ACCCD, A.BUACD, A.SACCCD, A.CNSCD,

A.SEQ, NVL(DECODE(A.ITMNAME,'****','*****',A.ITMNAME),'*****'), A.SYSCD,

REPLACE(A.TITCNT,CHR(13)||CHR(10),CHR(32)) TITCNT,

TO_CHAR(A.AMT0) AMT0, TO_CHAR(A.AMT1) AMT1, TO_CHAR(A.AMT2) AMT2,

TO_CHAR(A.AMT3) AMT3, TO_CHAR(A.AMT4) AMT4, '0' AMT1TOT, '0' AMT2TOT,

'0' AMT3TOT, '0' AMT4TOT, A.SLPNO, TO_CHAR(A.ACSDT,'YYYY/MM/DD'), A.MANITEM5

FROM (SELECT DISTINCT C.REQNO SLPNO, D.BGTIND, G.CONDPT WTDPT,

G.CONDPTNAME WTDPTNAME, D.BGDPT,

D.ACCCD, D.BUACD, D.SACCCD, D.CNSCD,

D.SEQ, D.ITMNAME, G.CPIND SYSCD,

G.ORDNO MANITEM5, G.ORDKNAME TITCNT,

C.PCONAMT AMT1, 0 AMT2, 0 AMT3, 0 AMT4,

A.CONDT ACSDT, '' TEMPACC, SUM(NVL(D.MONAMT,0)) AMT0

FROM ofms.FOCOMDDSM A, ofms.FPODMDDSM G, ofms.FPRQMDDSM B,

ofms.FPRQMBGTD C, ofms.FBPTMEXEM D

WHERE D.SEQ = C.BDTCD

AND D.CNSCD = C.CNSCD

AND D.SACCCD = C.SACCD

AND D.BUACD = C.BUACD

AND D.ACCCD = C.ACCCD

AND D.BGDPT = C.DPTCD

AND D.BGTIND = C.BGTIND

AND D.YEA = C.YEA

AND C.PCONAMT > 0

AND C.DPTCD = '190'

AND C.REQNO = B.REQNO

AND B.ORDNO = G.ORDNO

AND G.CPIND IN ('공사','용역')

AND G.ORDNO = A.CONNO

AND A.CONDT BETWEEN TO_DATE('2002/02/20','YYYY/MM/DD')

AND TO_DATE('2002/03/20','YYYY/MM/DD')

GROUP BY A.CONNO, D.BGTIND, G.CONDPT, G.CONDPTNAME, D.BGDPT,

D.ACCCD, D.BUACD, D.SACCCD, D.CNSCD, D.SEQ, D.ITMNAME,

G.CPIND, G.ORDNO, G.ORDKNAME, C.PCONAMT, A.CONDT, C.REQNO

UNION ALL

SELECT DISTINCT C.REQNO SLPNO, D.BGTIND, G.CONDPT WTDPT,

G.CONDPTNAME WTDPTNAME, D.BGDPT,

D.ACCCD, D.BUACD, D.SACCCD, D.CNSCD,

D.SEQ, D.ITMNAME, G.CPIND SYSCD,

G.ORDNO MANITEM5, G.ORDKNAME TITCNT,

C.PCONAMT AMT1, 0 AMT2, 0 AMT3, 0 AMT4,

A.CONDT ACSDT, '' TEMPACC, SUM(NVL(D.MONAMT,0)) AMT0

FROM ofms.FOCOMDDSM A, ofms.FPODMDDSM G, ofms.FPRQMITMM E,

ofms.FPRQMBGTD C, ofms.FBPTMEXEM D

WHERE D.SEQ = C.BDTCD

AND D.CNSCD = C.CNSCD

AND D.SACCCD = C.SACCD

AND D.BUACD = C.BUACD

………………………………….

ㅇ 원 인 : FBPTMEXEM 테이블의 결합인덱스가 9개 컬럼으로 생성되었는데 순서가 잘못되어

속도가 저하됨

* 기존 인덱스 칼럼순서 : YEA,CMM,BGTIND,BGDPT,ACCCD, BUACD,SACCCD,CNSCD,SEQ

ㅇ 조 치

- 인덱스생성

create index ix_FBPTMEXEM2 on ofms.FBPTMEXEM(BUACD,SACCCD,BGDPT,ACCCD,SEQ,YEA,CNSCD,BGTIND)

tablespace OFMSFIDX storage(initial 2m next 2m pctincrease 0) nologging;

create index ix_FAABMSLPD3 on ofms.FAABMSLPD(slpno,bgdpt)

tablespace OFMSFIDX storage(initial 2m next 2m pctincrease 0) nologging;

ㅇ 살행계획

Misses in library cache during parse: 1

Optimizer goal: RULE

Parsing user id: 5 (SYSTEM)

Rows Row Source Operation

------- ---------------------------------------------------

89 SORT ORDER BY

89 VIEW

89 UNION-ALL

0 SORT UNIQUE

0 SORT GROUP BY

0 NESTED LOOPS

1 NESTED LOOPS

7 NESTED LOOPS

66 NESTED LOOPS

66 TABLE ACCESS FULL FPRQMBGTD

130 TABLE ACCESS BY INDEX ROWID FPRQMDDSM

130 INDEX UNIQUE SCAN (object id 14006)

71 TABLE ACCESS BY INDEX ROWID FPODMDDSM

96 INDEX UNIQUE SCAN (object id 14002)

6 TABLE ACCESS BY INDEX ROWID FOCOMDDSM

10 INDEX UNIQUE SCAN (object id 11486)

0 TABLE ACCESS BY INDEX ROWID FBPTMEXEM

0 INDEX RANGE SCAN (object id 30476)

4 SORT UNIQUE

4 SORT GROUP BY

48 NESTED LOOPS

5 NESTED LOOPS

56 NESTED LOOPS

59 NESTED LOOPS

66 TABLE ACCESS FULL FPRQMBGTD

123 TABLE ACCESS BY INDEX ROWID FPRQMITMM

123 INDEX UNIQUE SCAN (object id 9484)

113 TABLE ACCESS BY INDEX ROWID FPODMDDSM

113 INDEX UNIQUE SCAN (object id 14002)

59 TABLE ACCESS BY INDEX ROWID FOCOMDDSM

110 INDEX UNIQUE SCAN (object id 11486)

48 TABLE ACCESS BY INDEX ROWID FBPTMEXEM

------------------------------

----------------------……….

slide127

ㅇ 시스템 : 재무시스템 계정/보조검색화면

ㅇ 속 도 : 개선전 1.27초 -> 0.07초

ㅇ 원문장

Select P.ACCCD, P.NAME, P.SACCCD, P.SACCNAME ,

P.MACSTS, P.BUASTS

FROM (Select A.Code ACCCD, A.NAME NAME,

C.SACCCD SACCCD, C.SACCNAME SACCNAME,

C.MACSTS MACSTS, C.BATSTS BATSTS,

A.BUASTS BUASTS

FROM ofms.FACDCACCM A,

(Select A.ACCCD ACCCD, A.SACCCD SACCCD,

A.SACCNAME, A.MACSTS MACSTS,

A.BATSTS BATSTS

From ofms.FaCDCACSA A,

(select ACCCD, Count(SACCCD)

From ofms.FaCDCACSA

Where salevind = '중분류'

Group By ACCCD, ACCNAME

Having Count(SACCCD) = 1) B

Where A.ACCCD = B.ACCCD

And A.salevind = '중분류' ) C

Where A.CODE = C.ACCCD(+)

AND A.ACCSTS = 'Y'

AND A.BGTSTS = 'Y'

AND A.MACSTS ='N') P

Where P.BATSTS IS null

OR P.BATSTS = 'N'

ORDER BY P.ACCCD, P.NAME

ㅇ 실행계획

Rows Execution Plan

------- ---------------------------------------------------

0 SELECT STATEMENT GOAL: RULE

71 SORT (ORDER BY)

71 FILTER

78 MERGE JOIN (OUTER)

79 SORT (JOIN)

78 TABLE ACCESS (FULL) OF 'FACDCACCM'

54 SORT (JOIN)

104 VIEW

104 NESTED LOOPS

105 VIEW

105 FILTER

140 SORT (GROUP BY)

1287 TABLE ACCESS (BY INDEX ROWID) OF 'FACDCACSA'

1288 INDEX (RANGE SCAN) OF

'FACDCACSA_IDX_SALEVIND' (NON-UNIQUE)

104 TABLE ACCESS (BY INDEX ROWID) OF 'FACDCACSA'

133952 INDEX (RANGE SCAN) OF 'FACDCACSA_IDX_SALEVIND'

(NON-UNIQUE)

slide128

ㅇ 원 인 : FACDCACSA테이블의 분포도가 좋지않는 인덱스가 전체적인

실행계획을 방해하며 2번씩 읽히고 있음. FACDCACSA가

드라이빙될수 있도록 조치

ㅇ 조 치 : 다른 인덱스를 읽도록 rtrim을 이용하여 실행계획 조정

ㅇ 수정문

Select P.ACCCD, P.NAME, P.SACCCD, P.SACCNAME ,

P.MACSTS, P.BUASTS

FROM (Select A.Code ACCCD, A.NAME NAME,

C.SACCCD SACCCD, C.SACCNAME SACCNAME,

C.MACSTS MACSTS, C.BATSTS BATSTS,

A.BUASTS BUASTS

FROM ofms.FACDCACCM A,

(Select A.ACCCD ACCCD, A.SACCCD SACCCD,

A.SACCNAME, A.MACSTS MACSTS,

A.BATSTS BATSTS

From ofms.FaCDCACSA A,

(select ACCCD, Count(SACCCD)

From ofms.FaCDCACSA

Where RTRIM(salevind) = '중분류'

Group By ACCCD, ACCNAME

Having Count(SACCCD) = 1) B

Where A.ACCCD = B.ACCCD

And rtrim(A.salevind) = '중분류' ) C

Where A.CODE = C.ACCCD(+)

AND A.ACCSTS = 'Y'

AND A.BGTSTS = 'Y'

AND A.MACSTS ='N') P

Where P.BATSTS IS null

OR P.BATSTS = 'N'

ORDER BY P.ACCCD, P.NAME;

ㅇ 수정후 실행계획

Execution Plan

---------------------------------------------------

SELECT STATEMENT GOAL: RULE

SORT (ORDER BY)

FILTER

MERGE JOIN (OUTER)

SORT (JOIN)

TABLE ACCESS (FULL) OF 'FACDCACCM'

SORT (JOIN)

VIEW

NESTED LOOPS

VIEW

FILTER

SORT (GROUP BY)

TABLE ACCESS (FULL) OF 'FACDCACSA'

TABLE ACCESS (BY INDEX ROWID) OF 'FACDCACSA'

INDEX (RANGE SCAN) OF 'FACDCACSA_CK_ACCCDSACCCD' (UNIQUE)

slide129

ㅇ 시스템 : 재무시스템

ㅇ 속 도 : 기존 30초 -> 0.5초

ㅇ 원문장

SELECT A.ACSLPNO, A.SSENO, REPLACE(D.TITCNT, CHR(13) || CHR(10), ' '),

A.CUSNAME, A.DPSNO, A.CASHAMT+A.CDAMT INAMT, A.PAYIND,

TO_CHAR(D.APPDT,'yyyy/mm/dd'), A.CUSCD

FROM ofms.FAABMDECD A,

ofms.FFBNMMSTM B,

ofms.FACDCAUNM C,

ofms.FAABMDECI D,

ofms.FACDCAUNM E

WHERE A.OUTDPSNO = B.DPSNO

AND B.SRL = (SELECT MAX(SRL)

FROM ofms.FFBNMMSTM

WHERE DPSNO = A.OUTDPSNO )

AND B.AUNCD = C.CODE

AND A.ACSLPNO = D.SLPNO

AND D.SSENO = 1

AND D.WTAUN = E.CODE

AND B.AUNCD = '01'

AND A.PAYIND IN ('계좌이체', '구매카드', '~보수발행', '지준이체',

'현금(출장)', '계좌이체(출장)', '인터넷지로',

'자동이체', '대량이체' )

AND D.APPDT BETWEEN ADD_MONTHS(TO_DATE('2002/07/23','YYYY/MM/DD'),-1)

AND TO_DATE('2002/07/23','YYYY/MM/DD') +1

AND D.WTEMPNO = '19950872'

slide130

ㅇ 실행계획

QUERY_PLAN

-----------------------------

SELECT STATEMENT cost =

.1 NESTED LOOPS

.1 NESTED LOOPS

.1 NESTED LOOPS

.1 NESTED LOOPS

.1 TABLE ACCESS FULL FAABMDECI

.2 INDEX UNIQUE SCAN FACDCAUNM_PK_CODE UNIQUE

.2 TABLE ACCESS BY INDEX ROWID FAABMDECD

.1 INDEX RANGE SCAN FAABMDECD_IDX_ACSLPNO

NON-UNIQUE

.2 TABLE ACCESS BY INDEX ROWID FFBNMMSTM

.1 INDEX UNIQUE SCAN FFBNMMSTM_CK_DPSNO UNIQUE

.1 SORT AGGREGATE

.1 INDEX RANGE SCAN FFBNMMSTM_CK_DPSNO UNIQUE

.2 INDEX UNIQUE SCAN FACDCAUNM_PK_CODE UNIQUE

ㅇ 원 인 : index가 1개 생략되어 있음

ㅇ 조 치 : index 생성

create index ix_FAABMDECI9 on fms.FAABMDECI(APPDT,

WTEMPNO)

tablespace indx storage(initial 2m next 2m pctincrease 0)

nologging;

ㅇ 실행계획

QUERY_PLAN

-----------------------------

SELECT STATEMENT cost =

.1 NESTED LOOPS

.1 NESTED LOOPS

.1 NESTED LOOPS

.1 NESTED LOOPS

.1 TABLE ACCESS BY INDEX ROWID FAABMDECI

.1 INDEX RANGE SCAN IX_FAABMDECI9 NON-UNIQUE

.2 INDEX UNIQUE SCAN FACDCAUNM_PK_CODE UNIQUE

.2 TABLE ACCESS BY INDEX ROWID FAABMDECD

.1 INDEX RANGE SCAN FAABMDECD_IDX_ACSLPNO

NON-UNIQUE

.2 TABLE ACCESS BY INDEX ROWID FFBNMMSTM

.1 INDEX UNIQUE SCAN FFBNMMSTM_CK_DPSNO UNIQUE

.1 SORT AGGREGATE

.1 INDEX RANGE SCAN FFBNMMSTM_CK_DPSNO UNIQUE

.2 INDEX UNIQUE SCAN FACDCAUNM_PK_CODE UNIQUE

slide132

성능관리 예제 2

ㅇ 시스템 : 그룹웨어, KM시스템

ㅇ 환 경 : 오라클 DBMS 8.1.7, 사용자수 (4000), DB크기(100G), SUN5500(CPU 8, MEM 8G)

ㅇ 현 상 : 원인불명의 시스템 다운현상 반복,

주요화면 속도 저하,

아침시간대 30분간 시스템 HANGUP현상 발생

ㅇ 사용자 : 아침시간대 30분간 시스템을 사용 할 수 없고, 장애가 빈번하고 속도가

느려 도저히 못 쓰겠음.

slide135

ㅇ 시스템 : 오아시스(지식관리 배치처리)

ㅇ 속 도 : 14시간

ㅇ 문 장

select docnumber

from docsadm.profile

where fulltext = 'Y'

and board is null

and work_flag is null

and rownum < 501;

ㅇ 실행계획

QUERY_PLAN

---------------------------------

SELECT STATEMENT cost = 11701

.1 COUNT STOPKEY

.1 TABLE ACCESS FULL PROFILE

ㅇ 원 인 : 200만건의 profile을 full scan하고 있음. fulltext 칼럼의 분포도가

50%로서 매우 좋지 않아 단일인덱스를 생성할수 없는 상황이며 테이블에

빈번한 수정 삭제가 일어나서 bitmap index를 생성할수 없으므로

b tree 결합인덱스로 테이블 접근이 이루어지지 않도록 조치

ㅇ 조 치

create index ix_profile44 on docsadm.profile(fulltext,docnumber,board,work_flag)

tablespace docsind storage(initial 2m next 2m pctincrease 0) nologging;

ㅇ 수정후 실행계획

QUERY_PLAN

--------------------------

SELECT STATEMENT cost = 4

.1 COUNT STOPKEY

.1 INDEX FAST FULL SCAN IX_PROFILE44 NON-UNIQUE

ㅇ 완료후 속도 : ??

slide136

ㅇ 소요시간 : 14초 -> 0.01초ㅇ 문장SELECT A.indate, A.ggubungig,A.ggubun,A.sug_no, A.acceptid, A.invres, A.invman, A.invDate, A.sname, C.dept, C.empno, C.paffsort, C.grade, C.korname, A.sgubun, D.codename, A.cause, A.improve, A.effect, A.opflag, A.opdept, C.head FROM oimi.imimidea A, oimi.imilidea B, ocom.ahambase C, ocode.imicidea D WHERE A.sug_no = B.sug_no AND B.empno = C.empno(+) AND A.ggubun = D.code AND A.sug_no = '07679'ㅇ plan_table : ------------------------------------ SELECT STATEMENT cost = .1 MERGE JOIN OUTER .1 SORT JOIN .1 NESTED LOOPS .1 NESTED LOOPS .1 TABLE ACCESS BY INDEX ROWID IMIMIDEA .1 INDEX UNIQUE SCAN SYS_C004093 UNIQUE .2 TABLE ACCESS BY INDEX ROWID IMICIDEA .1 INDEX UNIQUE SCAN SYS_C00960 UNIQUE .2 INDEX RANGE SCAN IX_IMILIDEA NON-UNIQUE .2 SORT JOIN .1 VIEW AHAMBASE .1 NESTED LOOPS .1 NESTED LOOPS .1 TABLE ACCESS FULL AHAMBASE .2 TABLE ACCESS BY INDEX ROWID AHACDEPT .1 INDEX RANGE SCAN AHACDEPT_PK_YEARDCODEID UNIQUE .1 TABLE ACCESS FULL DUAL .2 TABLE ACCESS BY INDEX ROWID AHACDEPT .1 INDEX RANGE SCAN AHACDEPT_PK_YEARDCODEID UNIQUE .1 TABLE ACCESS FULL DUAL --------------------------------------

ㅇ 원인 : 14590건의 imimidea와 3216건의 ahambase 테이블을 중심으로merge join이 일어나고 있다.ㅇ 수정 : imimidea의 sug_no칼럼이 가장 좋은 인덱스 컬럼이므로 테이블 조인

순서를 바꾸어서 튜닝. 조인량을 줄이기 위해 인라인뷰를 사용.

ㅇ 수정문장

SELECT /*+ordered use_nl(a)*/

A.indate, A.ggubungig,A.ggubun,

A.sug_no, A.accepti , A.invres,

A.invman, A.invDate, A.sname,

C.dept, C.empno, C.paffsort,

C.grade, C.korname, A.sgubun,

D.codename, A.cause, A.improve,

A.effect, A.opflag, A.opdept, C.head FROM (select *

from oimi.imimidea x

where x.sug_no = '07679') A,

oimi.imilidea B,

ocom.ahambase C,

ocode.imicidea D WHERE A.sug_no = B.sug_no

AND B.empno = C.empno

AND A.ggubun = D.code ;

slide137

ㅇ 시스템 : 오아시스

ㅇ 속 도 : 기존 110msec(0.11초)

ㅇ 기존문장 :

SELECT COUNT(hsMsgID)

FROM intraware.DOCBOX

WHERE hsSaveDeptID ='00000655370000001050'

AND UWBOXTYPE=5010

AND UWMSGTYPE=5011

AND UWRECEIVERCOUNT>0

AND hsControllerID = '00000000000000000000'

AND hsDrafterID ='00000000010000003714‘ ;

ㅇ 실행계획

QUERY_PLAN

----------------------------------------------

SELECT STATEMENT cost = 55

.1 SORT AGGREGATE

.1 TABLE ACCESS BY INDEX ROWID DOCBOX

.1 INDEX RANGE SCAN XIE2COUNTDOCBOX NON-UNIQUE

ㅇ 인덱스분석

- XIE2COUNTDOCBOX 칼럼순서 : HSSAVEDEPTID,UWBOXTYPE,UWMSGTYPE

- 분포도가 좋지않은 INDEX가 선택되고 있다.

create index ix_docbox20 on intraware.docbox(hsDrafterID,HSSAVEDEPTID)

tablespace indx storage(initial 4m next 4m pctincrease 0) nologging;

ㅇ 수정후

PLAN_TABLE

----------------------------------------------

SELECT STATEMENT cost = 2

.1 SORT AGGREGATE

.1 TABLE ACCESS BY INDEX ROWID DOCBOX

.1 INDEX RANGE SCAN IX_DOCBOX20 NON-UNIQUE

ㅇ 수정후 속도 : 0.001초(1msec)

slide140

ㅇ 문제문장

Select a.title

From intraware.PARTICIPANT b, intraware.APPROVAL a

WHERE a.APPRID = b.APPRID

and b.APPROVALSTATUS in (4, 512)

and (b.SIGNERID = '00000000010'||'001012065')

ㅇ 수행속도 : 기존 1761ms -> 튜닝후 10ms

ㅇ 실행계획분석

QUERY_PLAN

---------------------------------------

SELECT STATEMENT cost =

.1 NESTED LOOPS

.1 TABLE ACCESS FULL PARTICIPANT

.2 TABLE ACCESS BY INDEX ROWID APPROVAL

.1 INDEX UNIQUE SCAN XPKAPPROVAL UNIQUE

ㅇ 생성후 실행계획

QUERY_PLAN

------------------------------------------

SELECT STATEMENT cost =

.1 NESTED LOOPS

.1 INLIST ITERATOR

.1 TABLE ACCESS BY INDEX ROWID PARTICIPANT

.1 INDEX RANGE SCAN IX_PARTICIPANT2 NON-UNIQUE

.2 TABLE ACCESS BY INDEX ROWID APPROVAL

.1 INDEX UNIQUE SCAN XPKAPPROVAL UNIQUE