1 / 42

SQL Tuning 교육

SQL Tuning 교육. 처리시간 (t). 자료량 (n) 사용자수 (n). Preface. Measurement for Tuning Response Time Throughput Time Resources for Tuning Memory CPU Disk Network. Query. Optimizer. Query Optimization. Query Rewrite. Parse. RBO CBO. Query Execution. QEP Generation. Results.

brendan-lyne
Download Presentation

SQL Tuning 교육

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Tuning 교육

  2. 처리시간(t) 자료량(n) 사용자수(n) Preface • Measurement for Tuning • Response Time • Throughput Time • Resources for Tuning • Memory • CPU • Disk • Network

  3. Query Optimizer Query Optimization QueryRewrite Parse RBO CBO Query Execution QEPGeneration Results Query 처리흐름

  4. 테이블/인덱스 개념 작가이름순 인덱스 책이름순 인덱스 도서분야(종류) 인덱스 “ 도서관에서 필요한 책 찾기 ”

  5. 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 FIFI FIFJ FIFK FIFL FIFM FIFN FIFO FIFP FIFQ FIFR FIFS FIFT DATA+ROWID DATA+ROWID DATA+ROWID Leaf block scan

  6. 데이터 생성 인덱스 인덱스 데이터 값을 정렬하여 기록 테이블 들어오는 순서대로 기록 이름 ROWID 고객번호 이름 생일 109 D 90/01 A page 7 108 K 90/01 C page 3 102 C 92/05 D page 1 103 D 92/06 D page 4 104 F 93/01 F page 5 105 93/10 K page 8 101 A 93/10 K page 2 100 K 93/10 page 6

  7. 테이블 액세스(인덱스 없는 경우) X O X X X X X O WHERE 이름 = ‘K’ 고객번호 이름 생일 100 D 90/01 101 K 90/01 FULL TABLE SCAN 102 C 92/05 103 D 92/06 104 F 93/01 105 X 93/10 106 A 93/10 107 K 93/10

  8. 테이블 액세스(인덱스 있는 경우) X WHERE 이름 = ‘K’ TABLE ACCESS BY ROWID INDEX SCAN 고객번호 이름 생일 이름 ROWID 100 D 90/01 A page 7 101 K 90/01 C page 3 102 C 92/05 D page 1 103 D 92/06 D page 4 104 F 93/01 F page 5 105 X 93/10 K page 8 106 A 93/10 K page 2 107 K 93/10 X page 6

  9. 인덱스 사용 기준 • 인덱스 대상 컬럼 • 조건문에 자주 등장하는 컬럼 • 같은 값이 적은 컬럼(분포도가 좁은 컬럼) • 조인에 참여하는 컬럼 • 인덱스 사용 시 손해 보는 경우 • 데이터가 적은 테이블 ( 16 Block이내인 경우) • 같은 값이 많은 컬럼(분포도가 넓은 컬럼) • 조회보다 DML의 부담이 큰 경우

  10. 스캔범위결정 조건과 검증조건 인덱스 범위결정 CHECK X X O WHERE 이름 = ‘C’ AND 생일 > ‘92/12’ AND 고객번호 = 105 고객번호 이름 생일 이름 생일 ROWID 109 D 90/01 A 93/10 page 7 108 K 90/01 C 92/05 page 3 102 C 92/05 C 93/01 page 5 103 D 92/06 C 93/10 page 6 104 C 93/01 D 90/01 page 1 105 C 93/10 D 92/06 page 4 101 A 93/10 K 90/01 page 2 100 K 93/10 K 93/10 page 8

  11. 스캔범위결정 조건과 검증조건 인덱스 범위결정 check check X X X O WHERE 이름 = ‘C’ AND 생일 like ‘%10’ AND 고객번호 = 105 고객번호 이름 생일 이름 생일 ROWID 109 D 90/01 A 93/10 page 7 108 K 90/01 C 92/05 page 3 102 C 92/05 C 93/10 page 5 103 D 92/06 C 93/10 page 6 104 C 93/10 D 90/01 page 1 105 C 93/10 D 92/06 page 4 101 A 93/10 K 90/01 page 2 100 K 93/10 K 93/10 page 8

  12. 인덱스를 사용하지 못하는 경우 • 인덱스 컬럼에 변형이 일어난 경우 • 부정형으로 조건을 기술한 경우 • NULL을 비교하였을 경우 • 내부적인 변형이 일어난 경우 • 옵티마이져의 판단에 따라 (cost-based optimizer)

  13. 인덱스 컬럼의 변형 X X X X X O X O Select … from department where max_salary * 12 > 2500; 부서번호 부서명 max_salary max_salary ROWID 100 xxxx.xxxx.xxxxxxxx 109 D 150 108 K 180 100 xxxx.xxxx.xxxxxxxx 102 C 100 150 xxxx.xxxx.xxxxxxxx 180 xxxx.xxxx.xxxxxxxx 103 D 200 200 xxxx.xxxx.xxxxxxxx 104 C 100 105 C 350 200 xxxx.xxxx.xxxxxxxx 101 A 200 300 xxxx.xxxx.xxxxxxxx 350 xxxx.xxxx.xxxxxxxx 100 K 300

  14. 인덱스 컬럼의 변형 O O Select … from department where max_salary > 2500/12 ; 부서번호 부서명 max_salary max_salary ROWID 100 xxxx.xxxx.xxxxxxxx 109 D 150 108 K 180 100 xxxx.xxxx.xxxxxxxx 150 xxxx.xxxx.xxxxxxxx 102 C 100 180 xxxx.xxxx.xxxxxxxx 103 D 200 200 xxxx.xxxx.xxxxxxxx 104 C 100 105 C 350 200 xxxx.xxxx.xxxxxxxx 300 xxxx.xxxx.xxxxxxxx 101 A 200 350 xxxx.xxxx.xxxxxxxx 100 K 300

  15. 부정형 조건 O X X X X X X O Select … from Employee_TEMP where 부서번호 <> ’100’ ; 부서번호 부서명 max_salary max_salary ROWID 99 xxxx.xxxx.xxxxxxxx 99 D 150 100 K 180 100 xxxx.xxxx.xxxxxxxx 100 C 100 100 xxxx.xxxx.xxxxxxxx 100 xxxx.xxxx.xxxxxxxx 100 D 200 100 xxxx.xxxx.xxxxxxxx 100 C 100 100 C 350 100 xxxx.xxxx.xxxxxxxx 100 A 200 100 xxxx.xxxx.xxxxxxxx 101 xxxx.xxxx.xxxxxxxx 101 K 300

  16. 부정형 조건 O O Select … from Employee_TEMP where 부서번호 < ‘100’ OR 부서번호 > ‘100’ ; 부서번호 부서명 max_salary max_salary ROWID 99 xxxx.xxxx.xxxxxxxx 99 D 150 100 K 180 100 xxxx.xxxx.xxxxxxxx 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

  17. 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’) NULL로 비교시 내부변형 발생시

  18. 조인(JOIN) • 조인과 서브쿼리의 차이 • 조인의 방법 • 조인의 속도

  19. + 외부(Outer)조인인 경우 테이블2 -> 테이블1 한쪽만 인덱스가 있다. O X 테이블2 -> 테이블1 테이블1 -> 테이블2 X O 양쪽에 인덱스가 있다. 1. 조인순서에 상관없슴 O O 2. 나머지 조건들로 판단 양쪽에 인덱스가 없다. 1. SORT+MERGE X X 2. HASH JOIN 옵티마이져의 조인선택 조인 조건문 테이블 1 테이블 2 조인방법 “ 반드시 조인의 수를 줄일 수 있는 조인순서를 알아내고 이를 옵티마이져가 선택할 수 있도록 유도해야 한다. “

  20. 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

  21. MERGE SORT SORT R S Join Method – Sort Merge Join • sort_merge(R, S, ci, cj) • { • sort(R, ci); • sort(S, cj); • merge(R, S, ci, cj); • }

  22. R12345678 R1234 R5678 R12 R34 R56 R78 R1 R2 R3 R4 R5 R6 R7 R8 Sort-Merge • Internal Sort • in-memory sort • O(n2), O(nlog2n) • External Sort • internal sort로 run생성 • m개 run들을 merge • O(n2) + O(mlog2m) + ? Internal Sort

  23. Table R(build input) Table S(probe) Memory에 존재하는 hash table과 bitmap Result Set Disk Join Method – Hash Join 작은 row source로 hash table과 bitmap을 만든다. 다른 row source를 hashing하여 조사한다. bitmap은 hash table이 커 in-memory에 모두 놓일 수 없을 때, 사용된다.

  24. 50건 100회 100회 100회 이상 100회 이상 3 2 1 X O X O 1 : M 조인의 종류(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%’ (조인 조건 ) 1 (인덱스 SCAN ) 2 (결과 검증 ) 3 TAB1 Index (col5) TAB2 Index (FK) ● ● ● ● ● ● ● ● ● ● ●

  25. 200회 200회 50건 200회 200회 2 3 1 X X O 1 : 1 조인의 종류(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%’ (조인 조건 ) 1 (결과 검증 ) 2 (인덱스 SCAN) 3 TAB2 Index (col6) TAB1 Index (PK) ● ● ● ● ● ● ● ● ●

  26. Index (col6) Index (col5) TAB2 TAB1 MERGE 200 200 1 200건 100 100 SORT 100건 SORT ● ● ● ● ● ● ● ● ● 3 O 2 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%’ (SORT +중복데이터 삭제 ) 1 (인덱스 SCAN ) 2 (인덱스 SCAN ) 3

  27. Index (col6) Index (PK) TAB2 TAB1 200 200 200건 100 100 Hashing 100건 ● ● ● Hash Function ● ● ● ● ● ● 3 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%’ (해쉬조인 ) 1 (인덱스 SCAN ) 2 (인덱스 SCAN ) 3

  28. 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’

  29. Driving Table 5% And col2 > 10 And col3 like ‘a%’ B And col1 = ‘KKK’ 1,000 = Cardinality 10,000 0.1% 1,000,000 20% C And col4 between … A 100,000 1. 튜닝 시 드라이빙 테이블의 선택 Driving Table의 결정 추출성능이 가장 좋은 테이블* 조인절을 제외한 조건절에 의한 선택도가 가장 낮은 테이블 Cardinality = Table의 전체 로우수 * 조건절Selectivity

  30. 결과건수 1:4 (0.04%) 5% And col2 > 10 And col3 like ‘a%’ B a.jcol1 = b.jcol1 1,000 4 * 0.05 = 0.2 (20%) 10,000 1: 5 (0.005%) 20% C And col4 between … a.jcol2 = b.jcol2 A 5 * 0.2 = 1 (100%) 100,000 Next Join Table 2. 튜닝 시 조인순서의 선택 조인조건으로 연결이 되는 테이블 중에서 조인결과 비율이 가장 적은 테이블 (아래 결과 A, B, C순서로 해석) Next Join Table의 결정 비율 = Join Column의 대응비 * 조건절 Selectivity Cardinality = 결과건수 = Driving Table의 전체 로우수 * 조건절 Selectivity

  31. 결과 건수 1:4 (0.04%) 5% And col2 > 10 And col3 like ‘a%’ A B a.jcol1 = b.jcol1 Hash Join 1,000 1,000 * 4 / 10,000 = 0.4 (40%) 1,000 * 4 * 0.05 = 200 10,000 1: 5 (0.005%) A+B 20% C And col4 between … a.jcol2 = b.jcol2 200 * 5 / 100,000 = 0.01 (1%) Nested Loop 200 100,000 대상 Table 3. 튜닝 시 조인방법의 선택 조인방법의 결정 조인을 통한 선택도가 조인대상 테이블의 일정비율(5-10 %)를 초과 시 hash join, 반대의 경우에는 nested loop join 비율 = ( (Cardinality * 대응비) / 대상테이블 전체로우수 ) * 100% = ( Cardinality * 조인 컬럼의 Selectivity ) * 100% Cardinality = 결과건수 = (Cardinality * 대응비) * 조건절 Selectivity

  32. 조인방법과 순서(예제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테이블 : bofimstA(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'

  33. 조인방법과 순서(예제1) 1. Driving Table위 선택 : 조건절에 의한 선택도가 가장 낮은 Table 2. Next 조인 Table의 선택 : 조인결과 비율이 가장 낮은 Table 3. 조인방법의 선택 : 조인비율이 10% 이상이면 Hah Join, 이하이면 Nested Loop 결과건수 = 전체 로우수 * 조건절 선택도 = Cardinality * 대응비 * 조건절 선택도 조인결과비율 = 대응비 * 조건절 선택도 조인비율 = (Cardinality * 대응비) / 대상테이블 전체로우수 4:1( < 1:1 ) expire_kind >= 5 expire_kind < 100 50% C std_code = std_code A trade_date = :A 7,766 1: 1 3% 20,000 B trade_date = trade_date Std_code = std_code 100% 1,201,800 bofjmst = A, bofjbdeal = B, bmsms = C

  34. 조인방법과 순서(예제1) Driving Table pk_bofjmst bofjmst bmsms bofjdeal Hash Function Hash Function          625 7766 1201800 1차 hashing 2차 hashing • 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 Cost High

  35. 조인방법과 순서(예제1) SELECT /*+ordered use_nl(bofjmstbmsmsbofjbdeal) */ A.std_code, B.sel_cnt+ B.buy_cnt, C.expire_kind …… FROM bofjmstA, bmsmsC, bofjbdealB 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)

  36. 조인방법과 순서(예제1) 조인순서(Nested Loop) Driving Table pk_bofideal bofideal pk_bofjmst bofjmst pk_bmsms bmsms    625 360 625 625 360 • 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 Cost Low

  37. Tuning Tips - Overview • SQL문의 성능저하에 대한 일반적인 원인 • Sub-optimal access methods • 부적합한 통계정보 • Bind 변수 • 불필요한 sorting • Skewed data

  38. Suboptimal Access Methods • 필요 없는 rows을 가급적 빨리 제거하라 • 불필요한 FTS을 피하라 • 인덱스가 제대로 사용되는 지 확인하라 • Leading컬럼의 selectivity가 좋은가? • Column 순서는 제대로인가? • Best plan을 위해 다른 join순서를 조사하라

  39. 부적합한 통계정보 • 정기적으로 analyze • OLTP환경에는 문제 • Large table에 대해서는 estimate로 analyze • Query에 있는 모든 테이블을 analyze • Join컬럼에 대해서 analyze

  40. Bind 변수 • Bind변수 사용시 trade-off: • 공유에 의한 parse time 절약 • 최적화된 수행계획 • 원하는 access path를 위해 hint를 사용

  41. Sorting • 불필요한 sort를 제거하라 • 인덱스 Access 사용 • UNION ALL사용 • Sort-Merge Join을 피하라 (Hah Join, HASH_AREA_SIZE) • Sort를 위한 튜닝 • SORT_AREA_SIZE • TEMPORARY tablespaces

More Related