1 / 36

SQL Statement Tuning

SQL Statement Tuning. e-Architecture 팀 임성욱. 목 차. Index 의 구성 및 Access 방식 Join 의 종류 Optimizer 힌트의 종류 및 내용 SQL Processing Architecture Explain Plan Trace & Tkprof 사용법 인덱스를 이용하지 않는 경우 뷰의 사용 SQL 의 공유 참고자료 : 대용량 데이터베이스 솔루션 – 이화식 저. 1.Index 구성 및 Access 방식.

hannah-gilbert
Download Presentation

SQL Statement 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 Statement Tuning e-Architecture 팀 임성욱

  2. 목 차 • Index의 구성 및 Access 방식 • Join 의 종류 • Optimizer • 힌트의 종류 및 내용 • SQL Processing Architecture • Explain Plan • Trace & Tkprof 사용법 • 인덱스를 이용하지 않는 경우 • 뷰의 사용 • SQL 의 공유 • 참고자료 : 대용량 데이터베이스 솔루션 –이화식 저

  3. 1.Index 구성 및 Access방식 SELECT empno, ename, job FROM emp WHERE job = '부장' ORDER BY empno; EMPNO ENAME JOB INDEX-KEY ROWID 7654 어진수 부장 과장 0000A95B.0002.0001 7900 최종욱 과장 과장 0000A95B.0005.0001 과장 0000E62E.0009.0001 7689 홍길동 과장 과장 0000E9BE.0002.0001 7934 박문수 부장 부장 000062BE.0001.0001 7499 장민식 차장 부장 000062BE.0003.0001 7369 김태우 이사 부장 000093A6.0005.0001 7844 심정보 차장 부장 000093B2.000B.0001 7839 최연준 과장 이사 000069C5.0001.0001 7531 정연수 차장 차장 0000E9BE.0002.0001 SORT된 결과 7432 김인호 부장 차장 0000E9BE.0005.0001 7856 정명호 과장 차장 0000E9BE.000B.0001 7827 최민준 부장 TABLE (EMP) INDEX (JOB)

  4. 2. Join 의 종류 2.1 Nested Loop Join TABLE ACCESS BY ROWID SELECT a.FLD1, ..., b.FLD1,... FROM TAB1 a, TAB2 b WHERE a.KEY1 = b.KEY2 ANDa.FLD1 = 'AB' AND b.FLD2 = '10' TABLE ACCESS BY ROWID FLD2 ='10' check KEY2= KEY1 FLD1='AB' o o o 운반 단위 • 순차적 (부분범위처리 가능) • 종속적 (먼저 처리되는 테이블의 처리범위에 따라 처리량 결정) • 랜덤(Random) 액세스 위주 • 연결고리상태에 따라 영향이 큼 • 주로 좁은 범위처리에 유리 x INDEX (FLD1) INDEX (KEY2) TAB1 TAB2

  5. TABLE1 TABLE2 TABLE3 TABLE3 TABLE2 TABLE1 1 A 2 C 3 D 4 K 5 M 6 F 7 E 8 M . . . . . . . . A 가 P 나 C 라 S 마 . . . E 마 1 A 2 C 3 D 4 K 5 M 6 F 7 E 8 M . . . . . . . . A 가 P 나 C 라 H 사 . . . E 바 라 10 마 20 라 10 마 20 (2 row) (2 row) (1000 row) (1000 row) . . . (10000 row) (10000 row) 최소 10,000회 이상 ACCESS 최대 6회 이하 ACCESS 2. Join 의 종류 2.1 Nested Loop Join (계속)

  6. 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 (FLD2) INDEX (FLD1) 2. Join 의 종류 2.2 Sort 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' <처리순서> 1. a.FLD1 = ‘AB’조건으로 인덱스를 경유하여 TAB1 Access 후 결과 집합 Sort 2. b.FLD2 = ’10’조건으로 인덱스를 경유하여 TAB2 Access 후 결과 집합 Sort 3. 1,2 에서 Sort된 2개의 집합을 Merge

  7. 2. Join 의 종류 2.3 Hash Join 크기가 작은 파티션을 메모리에 로딩 SELECT /*+ use_hash(a b) */ a.FLD1, ..., b.FLD2,... 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 메모리 FLD1='AB' FLD2='10' Building 파 티 션 2 파 티 션 1 <처리순서> 1. a.FLD1 = ‘AB’조건으로 인덱스를 경유하여 TAB1 Access 후 결과 집합 생성 2. b.FLD2 = ’10’조건으로 인덱스를 경유하여 TAB2 Access 후 결과 집합 생성 3. 1,2 에서 생성된 2개의 집합으로 파티션 짝을 생성 4. 크기가 작은 파티션을 메모리에 로딩(Building) 하여 Hash Table 생성 5. 나머지 파티션의 로우을 읽어 Hash Table에 대응되는 로우가 있는지를 체크 . . . . . . . . . . . . . . . . . . . . . . . . 운반단위 TAB1 TAB2 INDEX (FLD2) INDEX (FLD1)

  8. 3. Optimizer 3.1 Optimizer 의 종류 CHOOSE 통계정보의 생성여부에 따라 Cost-Based Approach (ALL_ROWS) 와 Rule-Based Approach 중 선택 ALL_ROWS Cost-Based Approach 사용 (Best Throughput 이 목적) FIRST_ROWS Cost-Based Approach 사용 (Best Response Time 이 목적) RULE 미리 정해져 있는 Approach Rule에 의해 Access Path 결정

  9. Instance Level Session Level Statement Level Optimizer Mode Setting Optimizer Approach 에 영항을 주는 요인 3. Optimizer 3.2 Optimizer Mode의 설정 • Initial Paramemer ($ORACLE_HOME/dbs/initSID.ora)에 기술 • OPTIMIZER_MODE = CHOOSE • ALTER SESSION SET OPTIMIZER_MODE = RULE; • ALTER SESSION SET OPTIMIZER_GOAL = RULE; • ORACLE Hint 사용 (/*+ hint */) • RULE, CHOOSE, FIRST_ROWS, ALL_ROWS • Optimizer Mode • Analyze (통계정보) • Oracle Hints

  10. Rule-Based Optimizer 의 Ranking 3. Optimizer 3.3 Optimizer의 원리 ROWID에 의한 1 Row Access Cluster join 에 의한 1 Row Access Unique or Primary Key에 의한 1 Row Access Composite key Single-Column Indexes Indexed column 의 범위 검색 Indexed column full scan Full Table Scan

  11. Optimizer의 취사 선택 3. Optimizer 3.3 Optimizer의 원리(계속) EMPNO Index만 사용 SELECT * FROM EMP WHERE ENAME LIKE 'AB%' AND EMPNO = '7890' RANKING 의 차이 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 에 의한 선택

  12. 4. Hint 의 종류 및 내용 4.1 Optimization Approaches and Goals

  13. 4. Hint 의 종류 및 내용 4.2 Hint for Access Methods

  14. Hints for Join Orders 4. Hint 의 종류 및 내용 4.2 Hint for Access Methods(계속)

  15. Hints for Parallel Executions 4. Hint 의 종류 및 내용 4.3 Hint for Join Operations and Parallel Exec.

  16. 4. Hint 의 종류 및 내용 4.4 Additional Hints

  17. 5. SQL Processing Architectures User • Parser - Syntax Analysis - Semantic Analysis • Optimizer - CBO / RBO • Row Source Generator - receives the optimal plan from the optimizer - outputs the execution plan for the SQL statement Result SQL Query SQL Execution Parser Dictionary Statistics RBO CBO Rule-Based Optimizer Cost-Based Optimizer Optimizer Mode? Bind Variable Query plan Row Source Generator

  18. EXECUTION PLAN Explain Plan 예 SQL Statement 6. Execution Plan • EXPLAIN PLAN Statement 사용 → PLAN_TABLE에 실행계획 Insert • PLAN_TABLE에서 Query • PLAN_TABLE 생성 : $ORACLE_HOME/rdbms/admin/utlxplan.sql 실행 ID OPERATION OPTIONS OBJECT_NAME -------------------------------------------------------------------------------------- 0 SELECT STATEMENT 1 FILTER 2 NESTED LOOPS 3 TABLE ACCESS FULL EMP 4 TABLE ACCESS BY ROWID DEPT 5 INDEX UNIQUE SCAN PK_DEPTNO 6 TABLE ACCESS FULL SALGRADE SELECT ename, job, sal, dname FROM emp, dept WHERE emp.deptno = dept.deptno AND NOT EXISTS (SELECT * FROM salgrade WHERE emp.sal BETWEEN losal AND hisal);

  19. 6. Execution Plan (계속)

  20. 6. Execution Plan (계속)

  21. Trace Enable 7. ORACLE Trace 의 활용 7.1 Trace Enabling • Initial Parameter의 USER_DUMP_DEST에 지정된 위치에 생성 단, ALTER SYSTEM SET USER_DUMP_DEST = <new_dir> 로 변경 가능 • Initial Parameter의 MAX_DUMP_FILE_DIZE (OS Block 단위)를 초과할 수 없다. (MAX_DUMP_FILE_SIZE 의 Default Value: UNLIMITED) • TIMED_STATISTICS Initial Parameter가 TRUE로 Set되어 있어야만 정확한 시간 산출가능 • ALTER SESSION SET SQL_TRACE = TRUE; • EXEC DBMS_SESSION.SET_SQL_TRACE( TRUE); • EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid, serial#, TRUE); • EXEC SYS.DBMS_SYSTEM.SET_EV(sid, serial#, 10046, trace_level, ‘’);

  22. Trace Level 7. ORACLE Trace 의 활용 7.1 Trace Enabling (계속) • Trace Level 1 : 기본정보 • Trace Level 4 : 기본정보 + Binding 정보 출력 • Trace Level 8 : 기본정보 + Waiting 정보 출력 • Trace Level 12 : 기본정보 + Binding + Waiting 정보 출력 (Trace File의 Size 증가에 유의)

  23. TKPROF ? TKPROF 로 얻을 수 있는 내용 TKPROF 사용 예 7. ORACLE Trace 의 활용 7.2 TKPROF 의 활용 Trace File을 쉽게 읽을 수 있는 Format으로 요약 • The text of the SQL statement • The SQL trace statistics in tabular form • The number of library cache misses for the parsing and execution of the statement • The user initially parsing the statement • The execution plan generated by EXPLAIN PLAN option $> tkprof ora_1234_master5.trc 1234.prf explain=apps/apps sys=no $> tkprof ora_1234_master5.trc 1234.prf sort=(exeela, fchela) $> tkprof ora_1234_master5.trc 1234.prf explain=scott/tiger insert=store.sql sys=no sort=(execpu, fchcpu)

  24. TKPROF Options 7. ORACLE Trace 의 활용 7.2 TKPROF 의 활용(계속)

  25. 7. ORACLE Trace 의 활용 7.2 TKPROF 결과항목 분석

  26. 7. ORACLE Trace 의 활용 7.2 TKPROF 결과항목 분석 (계속)

  27. 8. 인덱스를 이용하지 않는 경우 8.1 인덱스를 사용하지 않는 경우 INDEX COLUMN의 변형 SELECT * FROM DEPT WHERE SUBSTR(DNAME,1,3) = 'ABC' SELECT * FROM EMP WHERE JOB <> 'SALES' NOT Operator NULL, NOT NULL SELECT * FROM EMP WHERE ENAME IS NOT NULL SELECT * FROM EMP WHERE JOB LIKE 'AB%' AND EMPNO = '7890' Optimizer 의 취사선택

  28. 8. 인덱스를 이용하지 않는 경우 8.2 Index Column의 변형 INDEX COLUMN의 변형(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')

  29. 8. 인덱스를 이용하지 않는 경우 8.2 Index Column의 변형 (계속) INDEX COLUMN의 변형(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

  30. 8. 인덱스를 이용하지 않는 경우 8.2 Index Column의 변형 (계속) INDEX COLUMN의 변형(internal) SELECT * FROM SAMPLET WHERECHA = 10 CREATE TABLE SAMPLET ( CHA CHAR(10), NUM NUMBER (12,3), VAR VARCHAR2(20), DAT DATE) SELECT * FROM SAMPLET WHERETO_NUMBER(CHA)= 10 SELECT * FROM SAMPLET WHERENUM LIKE '9410%' SELECT * FROM SAMPLET WHEREDAT = '01-JAN-94' SELECT * FROM SAMPLET WHERETO_CHAR(NUM)LIKE '9410%' SELECT * FROM SAMPLET WHERE DAT= TO_DATE('01-JAN-94')

  31. 8. 인덱스를 이용하지 않는 경우 8.3 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'

  32. 8. 인덱스를 이용하지 않는 경우 8.4 IS NULL & IS 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

  33. 9. View 의 활용 • 테이블과 달리 물리적인 저장공간을 가지지 않음 • 관련정보가 단지 Data Dictionary에 저장될 뿐임 • 테이블과 거의 동등하게 취급될 수 있는 논리적인 집합 • SELECT : 제한없음 • INSERT, UPDATE, DELETE : 경우에 따라 가능 • 인덱스, 클러스터링, 해쉬 클러스터 : 지정 불가 • 각종 권한(object privilege) : 부여 가능 • 하나의 테이블, 혹은 여러개의 테이블로 뷰를 생성할 수 있음 • 저장공간을 가지지 않으므로 정규화 규칙을 무시하고 목적에 따라 자유롭게 사용할 수 있음 • 최종적으로는 테이블을 엑세스하는 것 • 사용에 따라 수행속도에 문제가 발생할 수 있음

  34. 9. View 의 활용(계속) • 뷰내에 변수를 지정할 수 없음 • 테이블에 비해 특별히 수행속도를 저해하는 것은 없으며 옵티마이져에 의해 생성되는 수행경로에 영향을 받음 • 뷰의 엑세스 경로 생성원리를 숙지하여 잘 활용하면 양호한 수행속도를 보장 받을 수 있음 • 뷰는 뷰내에 사용된 테이블의 인덱스를 사용하게 되므로 인덱스 컬럼을 SQL Function으로 함부로 가공시키지 말 것 • 뷰내의 SELECT 문의 조건은 가능한 최적의 엑세스 경로를 사용할 수 있도록 하거나 그럴 수 없다면 뷰를 사용한 SQL의 WHERE 절에서는 반드시 양호한 액세스 경로가 되도록 할 것

  35. 10. SQL문의 공유 SQL 의 공유 • latch 에 대한 waiting 중library cache 와 shared pool 에 대한 waiting 이 가장 많다. 이 두가지 래치는 모두 원인이 literal sql (상수값이 그대로 사용되는 문장) 때문이거나 shared pool 이 너무 작아서 발생하는 경우이다. SQL 공유 원칙 • 바뀔 수 있는 값은 variable을 사용함. • 여러 사용자가 자주 사용하는 sql 은 variable 을 사용해야 함. • 항상 주요 조건이 있어야 함. • 사용자의 조건에 따라 driving 테이블이 결정 되야 하는 경우 dynamic SQL 을 사용하거나 literal 을 사용하여 CBO 가 최적의 경로를 찾도록 해야함.

  36. 10. SQL 문의 공유 (계속) 공유할 수 없는 SQL • SELECT * FROM EMP; • SELECT * FROM EMP; • SELECT * FROM EMP WHERE DEPTNO = :V_DEPTNO; • SELECT * FROM EMP WHERE DEPTNO = :D_NO; • SELECT * FROM EMP; • SELECT * FROM Emp; • SELECT * FROM EMP WHERE DEPTNO = '10'; • SELECT * FROM EMP WHERE DEPTNO = '20'; • SELECT * FROM EMP; • SELECT * FROM SCOTT.EMP;

More Related