Transcript orange 튜닝교육

SQL Tuning 교육
Preface
• Response Time
• Throughput Time
Resources for Tuning
처리시간(t)
Measurement for Tuning
• Memory
• CPU
• Disk
• Network
자료량(n)
사용자수(n)
Query 처리흐름
Query
Parse
Results
Optimizer
Query
Rewrite
Query
Execution
Query
Optimization
RBO
CBO
QEP
Generation
테이블/인덱스 개념
“ 도서관에서 필요한 책 찾기 ”
작가이름순 인덱스
책이름순 인덱스
도서분야(종류) 인덱스
B*Tree 인덱스
ROOT
A B C D E F G H I J K L
BRANCH
FA FB FC FD FE FF FG FH
FI FJ FK FL FM FN FO FP
BRANCH
FIA FIB FIC FID FIE FIF FIG FIH
FII
FIJ FIK FIL FIM FIN
FIO FIP FIQ FIR FIS FIT
LEAF
FIFA
FIFB
FIFC
FIFD
FIFE
FIFH
DATA+ROWID
Leaf block scan
FIFI
FIFJ
FIFK
FIFL
FIFM FIFN
DATA+ROWID
FIFO
FIFP
FIFQ
FIFR
FIFS
DATA+ROWID
FIFT
데이터 생성
인덱스
테이블
인덱스 데이터 값을 정렬하여 기록
들어오는 순서대로 기록
이름
ROWID
고객번호
이름
생일
A
page 7
109
D
90/01
C
page 3
108
K
90/01
D
page 1
102
C
92/05
D
page 4
103
D
92/06
F
page 5
104
F
93/01
K
page 8
105
K
page 2
101
A
93/10
page 6
100
K
93/10
93/10
테이블 액세스(인덱스 없는 경우)
WHERE
이름 = ‘K’
Œ
FULL TABLE SCAN
고객번호
이름
생일
100
D
90/01
X
101
K
90/01
O
102
C
92/05
X
103
D
92/06
X
104
F
93/01
X
105
X
93/10
X
106
A
93/10
X
107
K
93/10
O
테이블 액세스(인덱스 있는 경우)
WHERE
이름 = ‘K’
TABLE ACCESS BY ROWID
INDEX SCAN
이름
X
ROWID
고객번호
이름
생일
A
page 7
100
D
90/01
C
page 3
101
K
90/01
D
page 1
102
C
92/05
D
page 4
103
D
92/06
F
page 5
104
F
93/01
K
page 8
105
X
93/10
K
page 2
106
A
93/10
X
page 6
107
K
93/10
인덱스 사용 기준
인덱스 대상 컬럼
• 조건문에 자주 등장하는 컬럼
• 같은 값이 적은 컬럼(분포도가 좁은 컬럼)
• 조인에 참여하는 컬럼
인덱스 사용 시 손해 보는 경우
• 데이터가 적은 테이블 ( 16 Block이내인 경우)
• 같은 값이 많은 컬럼(분포도가 넓은 컬럼)
• 조회보다 DML의 부담이 큰 경우
스캔범위결정 조건과 검증조건
인덱스 범위결정
이름
X
생일
WHERE
AND
AND
ROWID
이름 = ‘C’
생일 > ‘92/12’
고객번호 = 105
고객번호
CHECK
이름
생일
A
93/10
page 7
109
D
90/01
C
92/05
page 3
108
K
90/01
C
93/01
page 5
102
C
92/05
C
93/10
page 6
103
D
92/06
D
90/01
page 1
104
C
93/01
X
D
92/06
page 4
105
C
93/10
O
K
90/01
page 2
101
A
93/10
K
93/10
page 8
100
K
93/10
스캔범위결정 조건과 검증조건
인덱스 범위결정
이름
X
생일
WHERE
AND
AND
check
이름 = ‘C’
생일 like ‘%10’
고객번호 = 105
고객번호
ROWID
이름
check
생일
109
D
90/01
108
K
90/01
page 5
102
C
92/05
93/10
page 6
103
D
92/06
D
90/01
page 1
104
C
93/10
X
D
92/06
page 4
105
C
93/10
O
K
90/01
page 2
101
A
93/10
K
93/10
page 8
100
K
93/10
A
93/10
page 7
C
92/05
page 3
C
93/10
C
X
인덱스를 사용하지 못하는 경우
• 인덱스 컬럼에 변형이 일어난 경우
• 부정형으로 조건을 기술한 경우
• NULL을 비교하였을 경우
• 내부적인 변형이 일어난 경우
• 옵티마이져의 판단에 따라 (cost-based optimizer)
인덱스 컬럼의 변형
Select …
from department
where max_salary * 12 > 2500;
max_salary
ROWID
부서번호
부서명
max_salary
100
xxxx.xxxx.xxxxxxxx
109
D
150
X
100
xxxx.xxxx.xxxxxxxx
108
K
180
X
150
xxxx.xxxx.xxxxxxxx
102
C
100
X
180
xxxx.xxxx.xxxxxxxx
103
D
200
X
200
xxxx.xxxx.xxxxxxxx
104
C
100
X
200
xxxx.xxxx.xxxxxxxx
105
C
350
O
300
xxxx.xxxx.xxxxxxxx
101
A
200
X
350
xxxx.xxxx.xxxxxxxx
100
K
300
O
인덱스 컬럼의 변형
Select …
from department
where max_salary > 2500/12 ;
max_salary
ROWID
부서번호
부서명
max_salary
100
xxxx.xxxx.xxxxxxxx
109
D
150
100
xxxx.xxxx.xxxxxxxx
108
K
180
150
xxxx.xxxx.xxxxxxxx
102
C
100
180
xxxx.xxxx.xxxxxxxx
103
D
200
200
xxxx.xxxx.xxxxxxxx
104
C
100
200
xxxx.xxxx.xxxxxxxx
105
C
350
300
xxxx.xxxx.xxxxxxxx
101
A
200
350
xxxx.xxxx.xxxxxxxx
100
K
300
O
O
부정형 조건
Select …
from Employee_TEMP
where 부서번호 <> ’100’ ;
max_salary
ROWID
부서번호
부서명
max_salary
99
xxxx.xxxx.xxxxxxxx
99
D
150
O
100
xxxx.xxxx.xxxxxxxx
100
K
180
X
100
xxxx.xxxx.xxxxxxxx
100
C
100
X
100
xxxx.xxxx.xxxxxxxx
100
D
200
X
100
xxxx.xxxx.xxxxxxxx
100
C
100
X
100
xxxx.xxxx.xxxxxxxx
100
C
350
X
100
xxxx.xxxx.xxxxxxxx
100
A
200
X
101
xxxx.xxxx.xxxxxxxx
101
K
300
O
부정형 조건
max_salary
Select …
from Employee_TEMP
where 부서번호 < ‘100’ OR 부서번호 >
‘100’ ;
부서번호 부서명
ROWID
max_salary
99
xxxx.xxxx.xxxxxxxx
99
D
150
100
xxxx.xxxx.xxxxxxxx
100
K
180
100
xxxx.xxxx.xxxxxxxx
100
C
100
100
xxxx.xxxx.xxxxxxxx
100
D
200
100
xxxx.xxxx.xxxxxxxx
100
C
100
100
xxxx.xxxx.xxxxxxxx
100
C
350
100
xxxx.xxxx.xxxxxxxx
100
A
200
101
xxxx.xxxx.xxxxxxxx
101
K
300
O
O
NULL비교와 내부변형
NULL로 비교시
내부변형 발생시
Select …
from Employee
where 생일 is not null;
Select …
from Employee
where 생일 > ‘ ’;
Select …
from Employee
where 급여 is not null;
Select …
from Employee
where 급여 > 0 ;
Select …
from Employee
where 부서번호 = 10(숫자)
Select …
from Employee
where 부서번호 = to_char(10)
Select …
from Employee
where 생일 > today(일자)
Select …
from Employee
where 생일 >
to_char( today, ‘YYMMDD’)
조인(JOIN)
• 조인과 서브쿼리의 차이
• 조인의 방법
• 조인의 속도
옵티마이져의 조인선택
조인 조건문
테이블 1
테이블 2
조인방법
외부(Outer)조인인 경우
+
한쪽만 인덱스가 있다.
O
X
테이블2 -> 테이블1
X
O
테이블1 -> 테이블2
양쪽에 인덱스가 있다.
양쪽에 인덱스가 없다.
테이블2 -> 테이블1
O
O
X
X
1. 조인순서에 상관없슴
2. 나머지 조건들로 판단
1. SORT+MERGE
2. HASH JOIN
“ 반드시 조인의 수를 줄일 수 있는 조인순서를 알아내고 이를
옵티마이져가 선택할 수 있도록 유도해야 한다. “
Join Method – Nested Loop Join
result = {};
for each row r in R do
for each row s in S do
if p(r.c, s.c) then result = result + {<r, s>};
end
end
Join Method – Sort Merge Join
sort_merge(R, S, ci, cj)
MERGE
{
sort(R, ci);
sort(S, cj);
merge(R, S, ci, cj);
}
SORT
SORT
R
S
Sort-Merge
R12345678
Internal Sort
• in-memory sort
• O(n2), O(nlog2n)
External Sort
R1234
• internal sort로 run생성
R5678
• m개 run들을 merge
• O(n2) + O(mlog2m) + ?
Internal Sort
R12
R1
R2
R34
R3
R56
R4
R5
R6
R78
R7
R8
Join Method – Hash Join
작은 row source로 hash table과 bitmap을 만든다.
다른 row source를 hashing하여 조사한다.
bitmap은 hash table이 커 in-memory에 모두 놓일 수 없을 때, 사용된다.
Table R
(build input)
Table S
(probe)
Memory에 존재하는
hash table과 bitmap
Result Set
Disk
조인의 종류(Nested Loop)
Select a.col1, a.col2, b.col3
from TAB1 a, TAB2 b
where a.PK = b.FK
and a.col5 = ‘10’
and b.col6 like ‘AB%’
Index
(col5)
100회
2
●
●
(조인 조건 )
2
(인덱스 SCAN )
3
(결과 검증 )
Index
(FK)
TAB1
100회
1
100회
이상
1
●
50건
100회
이상
●
●
1:M
TAB2
●
●
3
●
●
X
O
●
X
●
O
조인의 종류(Nested Loop)
Select a.col1, a.col2, b.col3
from TAB1 a, TAB2 b
where a.PK = b.FK
and a.col5 = ‘10’
and b.col6 like ‘AB%’
200회
3
Index
(col6)
200회
●
●
(조인 조건 )
2
(결과 검증 )
3
(인덱스 SCAN)
Index
(PK)
TAB2
200회
1
1
●
50건
200회
2
●
●
1:1
TAB1
●
●
X
●
X
●
O
조인의 종류(Sort Merge)
Select a.col1, a.col2, b.col3
from TAB1 a, TAB2 b
where a.PK = b.FK
and a.col5 = ‘10’
and b.col6 like ‘AB%’
Index
(col6)
200
TAB2
200
200건
●
●
(SORT +중복데이터 삭제 )
2
(인덱스 SCAN )
3
(인덱스 SCAN )
SORT
1
SORT
100
100건
●
O
X
O
Index
(col5)
TAB1
MERGE
●
3
1
●
●
100
●
●
●
2
조인의 종류(Hash Join)
Select a.col1, a.col2, b.col3
from TAB1 a, TAB2 b
where a.PK = b.FK
and a.col5 = ‘10’
and b.col6 like ‘AB%’
Index
(col6)
200
(해쉬조인 )
2
(인덱스 SCAN )
3
(인덱스 SCAN )
TAB2
200
200건
●
●
Index
(PK)
TAB1
Hashing
●
3
1
Hash
Function
100
100건
●
●
●
100
●
●
●
2
Join문 튜닝 예제
Select a.col1, b.col2, b.col3, c.col4 From a, b, c
Where a.jcol1 = b.jcol1
And
a.jcol2 = c.jcol2
And
a.col1 = ‘KKK’
And
b.col2 > 10 And b.col3 like ‘a%’
And
c.col4 Between ‘20030101’ And ‘20030201’
테이블
A
Row 수
Column 조건
1,000,000 col1 = ‘KKK’
a.jcol1+a.Jol2 (# of Distinct Value : 100,000)
B
10,000 b.col2 > 10 and b.col3 like ‘a%’
b.jcol1 (# of Distinct Value : 2,500)
C
100,000 c.col4 between ‘20030101’ and ‘20030201’
c.jcol2 (# of Distinct Value : 20,000)
선택도
0.1%
0.001%
5%
0.04%
20%
0.005%
1. 튜닝 시 드라이빙 테이블의 선택
Driving Table의 결정
추출성능이 가장 좋은 테이블*
조인절을 제외한 조건절에 의한 선택도가 가장 낮은 테이블
Cardinality = Table의 전체 로우수 * 조건절 Selectivity
Driving Table
And col1 = ‘KKK’
0.1%
B
1,000 = Cardinality
1,000,000
A
And col2 > 10
And col3 like ‘a%’
5%
10,000
C
100,000
And col4 between … 20%
2. 튜닝 시 조인순서의 선택
Next Join Table의 결정
조인조건으로 연결이 되는 테이블 중에서 조인결과 비율이
가장 적은 테이블 (아래 결과 A, B, C순서로 해석)
비율 = Join Column의 대응비 * 조건절 Selectivity
Cardinality = 결과건수 = Driving Table의 전체 로우수 * 조건절 Selectivity
결과건수
1:4 (0.04%)
a.jcol1 = b.jcol1
1,000
B
And col2 > 10
And col3 like ‘a%’
10,000
4 * 0.05 = 0.2 (20%)
1: 5 (0.005%)
A
a.jcol2 = b.jcol2
C
100,000
Next Join Table
5%
And col4 between … 20%
5 * 0.2 = 1 (100%)
3. 튜닝 시 조인방법의 선택
조인방법의 결정
조인을 통한 선택도가 조인대상 테이블의 일정비율(5-10 %)를
초과 시 hash join, 반대의 경우에는 nested loop join
비율 = ( (Cardinality * 대응비) / 대상테이블 전체로우수 ) * 100%
= ( Cardinality * 조인 컬럼의 Selectivity ) * 100%
Cardinality = 결과건수 = (Cardinality * 대응비) * 조건절 Selectivity
결과 건수
A
1,000
A+B
200
1:4 (0.04%)
a.jcol1 = b.jcol1
Hash Join
1: 5 (0.005%)
a.jcol2 = b.jcol2
Nested Loop
B
10,000
C
100,000
대상 Table
And col2 > 10
And col3 like ‘a%’
5%
1,000 * 4 / 10,000 = 0.4 (40%)
1,000 * 4 * 0.05 = 200
And col4 between … 20%
200 * 5 / 100,000 = 0.01 (1%)
조인방법과 순서(예제1)
SELECT A.std_code,
B.sel_cnt+ B.buy_cnt,
C.expire_kind ……
FROM bofjmst A, bofjbdeal B, bmsms C
WHERE A.std_code = B.std_code
AND A.std_code = C.std_code
AND A.trade_date= B.trade_date
AND A.trade_date = :A
AND C.expire_kind >= 5
AND C.expire_kind < 100;
인덱스 정보
BOFJBDEAL(PK_BOFJBDEAL)
TRADE_DATE+STD_CODE
(UNIQUE)
BMSMS (PK_BMSMS)
- STD_CODE (UNIQUE)
Driving테이블 : bofimst A(20,000)
조인순서 : A -> C -> B
조인방법 : ALL Nested Loop
ROWS
EXECUTION PLAN
-------- ----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
360 HASH JOIN
624
HASH JOIN
624
TABLE ACCESS (BY INDEX ROWID) OF 'BOFJMST’
625
INDEX (RANGE SCAN) OF 'PK_BOFJMST' (UNIQUE)
1201800
TABLE ACCESS (FULL) OF 'BOFJBDEAL’
7766
TABLE ACCESS (FULL) OF 'BMSMS'
조인방법과 순서(예제1)
1. Driving Table위 선택 : 조건절에 의한 선택도가 가장 낮은 Table
2. Next 조인 Table의 선택 : 조인결과 비율이 가장 낮은 Table
3. 조인방법의 선택 : 조인비율이 10% 이상이면 Hah Join, 이하이면 Nested Loop
결과건수 = 전체 로우수 * 조건절 선택도 = Cardinality * 대응비 * 조건절 선택
도
조인결과비율 = 대응비 * 조건절 선택도
조인비율 = (Cardinality * 대응비) / 대상테이블 전체로우수
4:1( < 1:1 )
trade_date = :A
3%
A
20,000
std_code = std_code
C
expire_kind >= 5
expire_kind < 100
50%
7,766
1: 1
trade_date = trade_date
Std_code = std_code
B
1,201,800
bofjmst = A,
=C
bofjbdeal = B,
bmsms
100%
조인방법과 순서(예제1)
Driving Table
bofjdeal
bmsms



Hash
Function



7766
pk_bofjms
t
Hash
Function
1201800
bofjms
t



625
1차 hashing
2차 hashing
Cost
High
all
count
cpu elapsed
disk
query current
rows
------- ------ -------- ---------- ---------- ---------- ---------- ---------Parse
1
0.01
0.01
0
0
0
0
Execute
1
0.01
0.01
0
0
0
0
Fetch
5
4.82
5.60
4593
5949
8
360
------- ------ -------- ---------- ---------- ---------- ---------- ---------Total
7
4.84
5.62
4593
5949
8
360
조인방법과 순서(예제1)
SELECT /*+ordered use_nl(bofjmst bmsms bofjbdeal) */
A.std_code,
B.sel_cnt+ B.buy_cnt,
C.expire_kind ……
FROM bofjmst A, bmsms C, bofjbdeal B
WHERE A.std_code = B.std_code
AND A.std_code = C.std_code
AND A.trade_date= B.trade_date
AND A.trade_date = :A
AND C.expire_kind >= 5
AND C.expire_kind < 100;
ROWS
EXECUTION PLAN
-------- ---------------------------------------------------------0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS
360 NESTED LOOPS
625
NESTED LOOPS
625
TABLE ACCESS (BY INDEX ROWID) OF 'BOFJMST’
625
INDEX (RANGE SCAN) OF 'PK_BOFJMST' (UNIQUE)
360
TABLE ACCESS (BY INDEX ROWID) OF 'BMSMS'
625
INDEX (UNIQUE SCAN) OF 'PK_BMSMS' (UNIQUE)
360
TABLE ACCESS (BY INDEX ROWID) OF 'BOFJBDEAL’
360
INDEX (UNIQUE SCAN) OF 'PK_BOFJBDEAL' (UNIQUE)
조인방법과 순서(예제1)
조인순서(Nested Loop)
Driving Table
pk_bofjmst
bofjmst
pk_bmsms
pk_bofideal
bmsms
bofideal



625
625
625
360
360
Cost Low
all
count
cpu elapsed
disk
query current
rows
------- ------ -------- ---------- ---------- ---------- ---------- ---------Parse
1
0.01
0.01
0
0
0
0
Execute
1
0.00
0.00
0
0
0
0
Fetch
5
0.52
0.60
572
4933
8
360
------- ------ -------- ---------- ---------- ---------- ---------- ---------Total
7
0.53
0.61
572
4933
8
360
Tuning Tips - Overview
SQL문의 성능저하에 대한 일반적인 원인
• Sub-optimal access methods
• 부적합한 통계정보
• Bind 변수
• 불필요한 sorting
• Skewed data
Suboptimal Access Methods
필요 없는 rows을 가급적 빨리 제거하라
불필요한 FTS을 피하라
인덱스가 제대로 사용되는 지 확인하라
• Leading컬럼의 selectivity가 좋은가?
• Column 순서는 제대로인가?
Best plan을 위해 다른 join순서를 조사하라
부적합한 통계정보
정기적으로 analyze
• OLTP환경에는 문제
• Large table에 대해서는 estimate로 analyze
Query에 있는 모든 테이블을 analyze
Join컬럼에 대해서 analyze
Bind 변수
Bind변수 사용시 trade-off:
• 공유에 의한 parse time 절약
• 최적화된 수행계획
원하는 access path를 위해 hint를 사용
Sorting
불필요한 sort를 제거하라
• 인덱스 Access 사용
• UNION ALL사용
• Sort-Merge Join을 피하라 (Hah Join, HASH_AREA_SIZE)
Sort를 위한 튜닝
• SORT_AREA_SIZE
• TEMPORARY tablespaces