SQL Tuning.

Presentation on theme: "SQL Tuning."— Presentation transcript:

1 SQL Tuning

2 각 튜닝요소에 잠재된 성능향상 잠재성능개선 애플리케이션 및 데이터 구조 설계 애플리케이션 (SQL) 튜닝 하드웨어 구입
오라클 서버 튜닝 O/S 튜닝 잠재성능개선

3 Performance Tuning 의 필요성
데이터, 사용자수, 애플리케이션의 증가 온라인 애플리케이션의 응답시간 저하 동일 시간대에서 배치작업 처리량의 감소 시스템 자원의 한계와 부하의 증가 튜닝전 1차튜닝후 2차튜닝후 데이터량,사용자, 애플리케이션 증가 응답시간 시스템자원 추가시점 1차튜닝 2차튜닝 경과일수

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

5 인덱스를 사용하면 빠른가? 인덱스 테이블 고객번호 이름 생일 이름 ROWID 101 A 93/10 A page 7
들어오는 순서에 관계없이 기록되는 큰 장부 인덱스 특정칼럼들의 값의 순서대로 나열된 작은 장부 고객번호 이름 생일 이름 ROWID INDEX ( 이름 ) A /10 A page 7 D page 4 D /01 K page 8 K /01 C /05 C page 3 D page 1 D /06 F /01 F page 5 X /10 X page 6 K page 2 K /10

6 인덱스 없는 테이블 Access WHERE 이름 = ‘K’ 고객번호 이름 생일 100 D 90/01 101 K 90/01
고객번호 이름 생일 FULL TABLE SCAN D /01 K /01 C /05 D /06 F /01 X /10 A /10 K /10

7 인덱스를 이용한 테이블 Access WHERE 이름 = ‘K’ TABLE ACCESS BY ROWID INDEX SCAN
고객번호 이름 생일 이름 ROWID A page 7 D /01 C page 3 K /01 D page 1 C /05 D page 4 D /06 F page 5 F /01 K page 8 X /10 K page 2 A /10 X page 6 K /10 INDEX ( 이름 )

8 인덱스 사용에 대하여... 인덱스 대상 컬럼 where 조건문에 자주 등장하는 컬럼
같은 값이 적은 컬럼(분포도가 10 % 이하인 컬럼) 조인에 참여하는 컬럼 인덱스 사용시 손해보는 경우 데이터가 적은 테이블 ( 16 Block이내인 경우) 같은 값이 많은 컬럼(분포도가 10 % 이상인 컬럼)

9 결합 인덱스 개념 WHERE 이름 = ‘C’ AND 생일 > ‘92/12’ 결합 인덱스
2개이상의 컬럼이 조건문에 자주 등장시 고객번호 이름 생일 이름 생일 ROWID INDEX ( 이름 + 생일 ) A / page 7 C / page 3 C / page 5 C / page 6 C / page 1 D / page 4 K / page 2 K / page 8 C /01 K /01 C /05 D /06 C /01 C /10 A /10 K /10

10 결합 인덱스 개념 SELECT 이름, 생일 FROM 고객테이블 WHERE 이름 = ‘C’ AND 생일 > ‘92/12’
인덱스만으로도 결과를 얻을 수 있을때 ... 고객번호 이름 생일 이름 생일 ROWID A / page 7 C /01 C / page 3 K /01 C / page 5 C /05 O C / page 6 D /06 O C / page 1 O C /01 D / page 4 C /10 K / page 2 A /10 K / page 8 K /10 INDEX ( 이름 + 생일 )

11 결합 인덱스 사용에 대하여... 결합인덱스를 사용하는 경우 자주 조건에 같이 등장하는 경우
인덱스만 읽고도 결과를 얻을 수 있을때 결합인덱스의 순서 사용빈도가 높은 컬럼 SCAN범위를 줄여주는 컬럼 자주 사용되는 컬럼

12 인덱스범위를 결정하는 조건과 검증조건 WHERE 이름 = ‘C’ AND 생일 > ‘92/12’ AND 고객번호 = 105
인덱스범위를 결정하는 조건과 검증조건 WHERE 이름 = ‘C’ AND 생일 > ‘92/12’ AND 고객번호 = 105 인덱스 범위결정 check 이름 생일 ROWID 고객번호 이름 생일 A / page 7 D /01 X C / page 3 K /01 C / page 5 C /05 C / page 6 D /06 X D / page 1 C /01 X D / page 4 C /10 O K / page 2 A /10 K / page 8 K /10 INDEX ( 이름 + 생일 )

13 인덱스범위를 결정하는 조건과 검증조건 check check WHERE 이름 = ‘C’ AND 생일 like ‘%10’
인덱스범위를 결정하는 조건과 검증조건 WHERE 이름 = ‘C’ AND 생일 like ‘%10’ AND 고객번호 = 105 인덱스 범위결정 check check 이름 생일 ROWID 고객번호 이름 생일 A / page 7 D /01 C / page 3 K /01 X C / page 5 C /05 C / page 6 D /06 D / page 1 C /10 X D / page 4 C /10 O K / page 2 A /10 K / page 8 K /10 INDEX ( 이름 + 생일 )

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

15 인덱스를 사용하지 못하는 경우 (1) Select … from department
인덱스를 사용하지 못하는 경우 (1) Select … from department where max_salary * 12 > 2500; 인덱스 컬럼의 변경 max_salary ROWID 부서번호 부서명 max_salary xxxx.xxxx.xxxxxxxx D O X xxxx.xxxx.xxxxxxxx K xxxx.xxxx.xxxxxxxx C xxxx.xxxx.xxxxxxxx D xxxx.xxxx.xxxxxxxx C xxxx.xxxx.xxxxxxxx C xxxx.xxxx.xxxxxxxx A xxxx.xxxx.xxxxxxxx K INDEX ( max_salary )

16 인덱스를 사용하지 못하는 경우 (1) Select … from department 인덱스 컬럼의 변경을 방지
인덱스를 사용하지 못하는 경우 (1) Select … from department where max_salary > 2500 / 12 ; 인덱스 컬럼의 변경을 방지 max_salary ROWID 부서번호 부서명 max_salary xxxx.xxxx.xxxxxxxx D xxxx.xxxx.xxxxxxxx K xxxx.xxxx.xxxxxxxx C xxxx.xxxx.xxxxxxxx D xxxx.xxxx.xxxxxxxx C O xxxx.xxxx.xxxxxxxx C xxxx.xxxx.xxxxxxxx A O xxxx.xxxx.xxxxxxxx K INDEX ( max_salary )

17 인덱스를 사용하지 못하는 경우 (2) Select … from Employee 부정형 조건
인덱스를 사용하지 못하는 경우 (2) Select … from Employee where 부서번호 <> 100 ; 부정형 조건 부서번호 ROWID 부서번호 사원명 max_salary xxxx.xxxx.xxxxxxxx D X O xxxx.xxxx.xxxxxxxx K xxxx.xxxx.xxxxxxxx C xxxx.xxxx.xxxxxxxx D xxxx.xxxx.xxxxxxxx C xxxx.xxxx.xxxxxxxx C xxxx.xxxx.xxxxxxxx A xxxx.xxxx.xxxxxxxx K INDEX ( 부서번호 )

18 인덱스를 사용하지 못하는 경우 (2) Select … from Employee 부정형 조건을 변형
인덱스를 사용하지 못하는 경우 (2) Select … from Employee where 부서번호 > 100 ; 부정형 조건을 변형 부서번호 ROWID 부서번호 사원명 max_salary xxxx.xxxx.xxxxxxxx D xxxx.xxxx.xxxxxxxx K xxxx.xxxx.xxxxxxxx C xxxx.xxxx.xxxxxxxx D xxxx.xxxx.xxxxxxxx C xxxx.xxxx.xxxxxxxx C xxxx.xxxx.xxxxxxxx A O xxxx.xxxx.xxxxxxxx K INDEX ( 부서번호 )

19 인덱스를 사용하지 못하는 경우 (3) NULL로 비교시 Select … from Employee
인덱스를 사용하지 못하는 경우 (3) 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 부서번호 = :A (상수) Select … from Employee where 부서번호 = to_char( :A) Select … from Employee where 생일 = :B (일자) Select … from Employee where 부서번호 = to_char( :B, ‘YY/MM/DD’)

20 부분범위(Partial Range Scan)의 처리
조건을 만족하는 Row 수가 운반단위 에 도달되면 멈춤 전 체 범 위 처 리 부 분 범 위 처 리  조건식을 만족하는 범위를 모두 스캔 STOP KEY를 활용하여 일정범위만 스캔

21 부분범위(Partial Range Scan) 의 처리
부분범위 처리의 성능 결정 주 범위 (Driving Range) 부 범위 (Checking Range) 성 능 넓다 넓다 양호 넓다 좁다 불량 좁다 넓다 양호 좁다 좁다 양호

22 부분범위(Partial Range Scan) 의 처리
TABLE INDEX TABLE O X 운반단위 X O ● ● ● ● SCAN 수 최초운반대상 선정 부분범위 처리의 성능 결정 =

23 부분범위(Partial Range Scan)의 처리
범위가 넓은데 속도가 빠르다. 대상범위가 넓다. 최초운반단위가 빨리 결정됨 Select … from ORDER Where Rownum < 2; Select … from ORDER Where Rownum < 2 order by item ; 범위는 같은데 갑자기 속도가 느려졌다. 대상범위가 넓다. SORT 가공단계를 거치기 위해 전체를 SCAN한다. 최초운반단위가 늦게 결정됨 Select … from ORDER Where item > ‘’ and Rownum < 2; 인덱스를 사용한 SORT를 하니까 속도가 빨라졌다. 대상범위가 넓다. SORT 가공단계를 거치지 않는다.(INDEX SCAN) 최초운반단위가 빨리 결정됨

24 부분범위처리 사용원칙 대상범위가 좁은 조건을 먼저 처리하는 것이 항상 유리하다. Select * from order
where ordno between 1 and < 넓다 and custno like ‘DN%’ < 좁다 인덱스 구성 : custno + ordno ( o ) ordno + custno ( x ) 인덱스를 최대한 활용하라. Select min(sal) from employee; <---- 전체범위처리 Select min(sal) from employee where sal > 0 and ROWNUM =1; < 부분범위처리

25 부분범위처리 사용원칙 부분범위 처리가능시 최초운반단위를 빨리 채우도록 한다. Select ename, sal*100
from employee where hiredate like ‘95%’ order by ename < 전체범위처리 Select ename, sal*100 from employee where hiredate like ‘95%’ and ename > ‘’ < 부분범위처리 UNION 대신에 UNION ALL을 사용하라. Select 생일, 급여 from emp_history where 생일 = ‘01-jan-70’ union select 생일, 입사일자, 급여 from emp where 급여 >10000 Select 생일, 급여 from emp_history where 생일 = ‘01-jan-70’ union all select 생일, 입사일자, 급여 from emp where 급여 >10000 and 생일 <> ‘01-jan-70’

26 조인(Join) 어떻게 해야하나? 사용자가 원하는 데이터가 2개이상의 테이블에 있는경우 조인이란 ?
각 테이블의 특정컬럼의 값이 같은것을 서로 연결하여 데이터를 가져오는 방법 조인이란 ? 조인의 대전제 어느 순서로 조인이 이루어지든지 결과는 같다. 조인의 속도 ? 조인의 속도는 일의 량(SCAN수)에 반비례하며 일의 량은 조인의 순서에 따라 다르다. 스캔범위 비율이 작은 조건이 있는 테이블을 항상 먼저 읽게 한다.

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

28 Nested Loop 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 (결과 검증 ) 3 Index (col5) Index (FK) TAB1 TAB2 100회 100회 100회 이상 50건 100회 이상 ● 2 1 3 ● ● X O X O

29 Nested Loop Join 3 1 2 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 Index (col6) Index (PK) TAB2 TAB1 200회 200회 200회 200회 50건 ● 3 1 ● 2 ● O X O

30 Sort Merge Join 1 3 2 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 3 (인덱스 SCAN ) Index (col6) Index (PK) TAB2 TAB1 MERGE 200 200 O X 50건 1 200건 SORT 100 100 SORT 100건 ● ● 3 ● 2

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

32 옵티마이져 (Optimizer) “ SQL 튜닝을 잘 하려면 옵티마이져를 잘 알아야 한다. “
“ 옵티마이져는 DBMS버젼에 따라 진보한다. 따라서 변경된 옵티마이져의 장단점과 특징을 잘 알아야 한다. “ 옵티마이져의 종류 Rule-based Optimizer 일의량을 조건문에 따라 미리 결정해 놓고 그에따라 실행계획을 수립한다. Cost-based Optimizer 일의량을 Data Dictionary의 통계정보와 조건문을 가지고 산출하여 실행계획을 수립한다.

33 OPTIMIZER MODE의 선정시 고려사항
ALL_ROWS : 최소의 자원을 이용하여 원하는 결과전체를 가져오기 위한 실행계획을 작성하도록 한다. 비용기반(Cost-based) 최적화를 사용함. FIRST_ROWS : 최소의 자원을 이용하여 첫번째 로우를 가져오기 위한 실행계획을 작성한다. 정렬(SORT)이 필요한 SQL은 최적화 할 수 없다. 비용기반(Cost-based) 최적화를 사용함. CHOOSE : Data Dictionary정보에 분석(analyze)정보의 유무에 따라 비용기반 또는 룰(Rule-based)기반으로 최적화 경로 수립. RULE : 분석(analyze)정보에 관계없이 SQL문장의 정보에 따라 최적화된 실행계획를 작성함.

34 Cost-Based 최적화에 영향을 주는 파라메터
OPTIMIZER_FEATURES_ENABLED OPTIMIZER_MODE OPTIMIZER_PERCENT_PARALLEL HASH_AREA_SIZE SORT_AREA_SIZE DB_FILE_MULTIBLOCK_READ_COUNT ALWAYS_ANTI_JOIN HASH_JOIN_ENABLED HASH_MULTIBLOCK_IO_COUNT OPTIMIZER_SEARCH_LIMIT BITMAP_MERGE_AREA_SIZE OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_INDEX_CACHING OPTIMIZER_FEATURES_ENABLED 특정버전의 옵티마이져를 그대로 사용하고자 할 경우 OPTIMIZER_MODE 인스턴스에서 사용하는 옵티마이져의 모드설정 OPTIMIZER_PERCENT_PARALLEL 오브젝트에 주어진 병렬처리수(Degree)를 cost에 반영정도(%) HASH_AREA_SIZE hash join의 cost에 영향을 줌 SORT_AREA_SIZE sort merge join의 cost에 영향을 줌 DB_FILE_MULTIBLOCK_READ_COUNT full scan의 cost에 영향을 줌 ALWAYS_ANTI_JOIN not in subquery시 실행계획을 anti hash join으로 HASH_JOIN_ENABLED hash join의 사용여부를 결정 HASH_MULTIBLOCK_IO_COUNT hash join의 cost에 영향을 줌 OPTIMIZER_INDEX_COST_ADJ 인덱스 사용의 영향도(0-100, default: 100) OPTIMIZER_INDEX_CACHING nested loop join시 사용되는 인덱스 leaf block의 캐쉬 정도 (0-100, default : 0)

35 옵티마이져 힌트(Hint) 힌트의 사용 RULE Rule based optimizer를 사용
힌트의 사용 RULE Rule based optimizer를 사용 FIRST_ROWS 첫째 레코드의 추출시간을 최소화 ALL_ROWS 모든 레코드의 추출시간을 최소화 FULL 지정된 테이블을 전체 SCAN INDEX_ASC 오름차순으로 인덱스를 SCAN INDEX_DESC 내림차순으로 인덱스를 SCAN ORDERED FROM절에 기술된 순으로 조인 USE_NL 특정 테이블로 시작해서 Nested LOOP USE_MERGE 특정 테이블로 시작해서 Merge Join PARALLEL 병렬처리시 테이블의 프로세스 갯수 힌트의 사용예 Select /*+ INDEX (Patients I_sex_index) */ 이름, 키, 몸무게 from Patients where sex = ‘M ‘ ;

36 Hash Join과 Nested Loop Join의 선택기준
Driving Table  대상범위비율 = 조건범위/ 전체범위 1차스캔후 대상

37 Hash Join과 Nested Loop Join의 선택(I)
SELECT bofjmst.std_code, bofjbdeal.sel_cnt+bofjbdeal.buy_cnt, bmsms.expire_kind …… FROM bofjmst, bofjbdeal, bmsms WHERE bofjmst.std_code= bofjbdeal.std_code AND bofjmst.std_code = bmsms.std_code AND bofjmst.trade_date=bofjbdeal.trade_date AND bofjmst.trade_date = :A AND bmsms.expire_kind >= 5 AND bmsms.expire_kind < 100; 인덱스 정보 BOFJBDEAL(PK_BOFJBDEAL) - TRADE_DATE+STD_CODE (UNIQUE) BMSMS (PK_BMSMS) - STD_CODE (UNIQUE) Driving테이블 : bofimst (5년보관) 대상범위비율 : 좁다 조인대상건수 : 많다 조인방법 : Nested Loop ROWS EXECUTION PLAN 0 SELECT STATEMENT Optimizer=CHOOSE HASH JOIN HASH JOIN TABLE ACCESS (BY INDEX ROWID) OF 'BOFJMST’ INDEX (RANGE SCAN) OF 'PK_BOFJMST' (UNIQUE) TABLE ACCESS (FULL) OF 'BOFJBDEAL’ TABLE ACCESS (FULL) OF 'BMSMS' Hash대상 역전 “ 오라클 V8.X에서 옵티마이져는 HASH JOIN을 선호한다.(hash cost is lower) “

38 Hash Join과 Nested Loop Join의 선택(I)
ROWS EXECUTION PLAN 0 SELECT STATEMENT Optimizer=CHOOSE HASH JOIN HASH JOIN TABLE ACCESS (BY INDEX ROWID) OF 'BOFJMST’ INDEX (RANGE SCAN) OF 'PK_BOFJMST' (UNIQUE) TABLE ACCESS (FULL) OF 'BOFJBDEAL’ TABLE ACCESS (FULL) OF 'BMSMS' bofideal 2차 hashing Driving Table Hash Function  1차 hashing  pk_bofjmst bofjmst 625 Hash Function  bmsms 7766 360

39 Hash Join과 Nested Loop Join의 선택(I)
all count cpu elapsed disk query current rows Parse Execute Fetch Total Cost High Disk I/O에 대한 elapsed time : 1초/100~200회 Disk Cache(1G)의 영향으로 응답속도 개선

40 Hash Join과 Nested Loop Join의 선택(I)
SELECT /*+FIRST_ROWS */ bofjmst.std_code, bofjbdeal.sel_cnt+bofjbdeal.buy_cnt, bmsms.expire_kind …… FROM bofjmst, bofjbdeal, bmsms WHERE bofjmst.std_code= bofjbdeal.std_code AND bofjmst.std_code = bmsms.std_code AND bofjmst.trade_date=bofjbdeal.trade_date AND bofjmst.trade_date = :A AND bmsms.expire_kind >= 5 AND bmsms.expire_kind < 100; ROWS EXECUTION PLAN 0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS NESTED LOOPS NESTED LOOPS TABLE ACCESS (BY INDEX ROWID) OF 'BOFJMST’ INDEX (RANGE SCAN) OF 'PK_BOFJMST' (UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF 'BOFJBDEAL’ INDEX (UNIQUE SCAN) OF 'PK_BOFJBDEAL' (UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF 'BMSMS' INDEX (UNIQUE SCAN) OF 'PK_BMSMS' (UNIQUE)

41 Hash Join과 Nested Loop Join의 선택(I)
ROWS EXECUTION PLAN 0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS NESTED LOOPS NESTED LOOPS TABLE ACCESS (BY INDEX ROWID) OF 'BOFJMST’ INDEX (RANGE SCAN) OF 'PK_BOFJMST' (UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF 'BOFJBDEAL’ INDEX (UNIQUE SCAN) OF 'PK_BOFJBDEAL' (UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF 'BMSMS' INDEX (UNIQUE SCAN) OF 'PK_BMSMS' (UNIQUE) 조인순서(Nested Loop) Driving Table pk_bofjmst bofjmst pk_bofideal bofideal pk_bmsms bmsms o x o  625 625 1248 1248 1248 360

42 Hash Join과 Nested Loop Join의 선택(I)
all count cpu elapsed disk query current rows Parse Execute Fetch Total Cost High all count cpu elapsed disk query current rows Parse Execute Fetch Total Cost Low