SQL Statement Tuning e-Architecture 팀 임성욱.

Presentation on theme: "SQL Statement Tuning e-Architecture 팀 임성욱."— Presentation transcript:

1 SQL Statement Tuning e-Architecture 팀 임성욱

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

3 INDEX (JOB) TABLE (EMP) 1.Index 구성 및 Access방식 SELECT empno, ename, job
FROM emp WHERE job = '부장' ORDER BY empno; INDEX-KEY ROWID EMPNO ENAME JOB 과장 A95B 어진수 부장 과장 A95B 최종욱 과장 과장 E62E 홍길동 과장 과장 E9BE 박문수 부장 부장 BE 장민식 차장 부장 BE 김태우 이사 부장 A 심정보 차장 부장 B2.000B.0001 최연준 과장 이사 C 정연수 차장 차장 E9BE 김인호 부장 SORT된 결과 차장 E9BE 정명호 과장 차장 E9BE.000B.0001 최민준 부장 INDEX (JOB) TABLE (EMP)

4 종속적 (먼저 처리되는 테이블의 처리범위에 따라 처리량 결정)
2. Join 의 종류 2.1 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 FLD2 ='10' check FLD1='AB' KEY2= KEY1 o o o 순차적 (부분범위처리 가능) 종속적 (먼저 처리되는 테이블의 처리범위에 따라 처리량 결정) 랜덤(Random) 액세스 위주 연결고리 상태에 따라 영향이 큼 주로 좁은 범위 처리에 유리 SQL문장과 그림 설명 Index 는 TAB1 의 FLD2 , TAB2 의 Key2 컬럼에 존재 2. 특징 1) 선행 테이블의 처리범위가 일량을 결정한다.(방향성) 2) 선행 테이블의 값을 받아서 후행 테이블의 처리범위가 결정된다.(종속적) 3) 주로 랜덤 액세스 방식으로 처리된다.(랜덤액세스) 4) 후행 테이블의 조인컬럼의 인덱스의 유무 및 조건의 인덱스 참여의 정도에 따라 수행속도가 많이 차이 난다.(연결고리 상태) 3. 사용기준 1) 부분범위처리를 하는 경우에 유리하다. 2) 처리량이 적은 경우에 유리하다.- 랜덤액세스가 많을 경우 수행속도를 보장할 수 없으므로 Sort Merge Join이나 Hash Join으로 유도 3) 선행 테이블의 결과를 받아야만 후행 테이블의 처리범위를 줄일 수 있는 경우에 유리하다. - 연결고리에 Index가 반드시 존재하여야 함 4) 선행 테이블의 처리범위가 수행속도에 절대적 영향을 미치므로 최적의 조인 순서가 될 수 있도록 유도해야 한다. 운반 단위 x INDEX (FLD1) TAB TAB2 INDEX (KEY2)

5 최소 10,000회 이상 ACCESS 최대 6회 이하 ACCESS TABLE1 TABLE2 TABLE3
2. Join 의 종류 2.1 Nested Loop Join (계속) TABLE TABLE TABLE3 (10000 row) (1000 row) (2 row) . . . 1 A 2 C 3 D 4 K 5 M 6 F 7 E 8 M A 가 P 나 C 라 H 사 E 바 라 10 마 20 최소 10,000회 이상 ACCESS TABLE TABLE TABLE1 S 마 E 마 최대 6회 이하 ACCESS Nested Loop Join시는 Join의 순서에 영향을 받음 처리 범위가 가장 좁은 범위를 먼저 처리해야 유리함.

6 . TAB1 TAB2 2. Join 의 종류 INDEX (FLD1) 운반단위 S O R T (FLD2)
2.2 Sort Join INDEX (FLD1) TAB TAB2 FLD1='AB' TABLE ACCESS BY ROWID 운반단위 . S O R T (FLD2) FLD2='10' a.KEY1= b.KEY2 를 조건으로 Merge 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' Sort Merge Join의 처리순서 설명 특징 1) 상대 테이블로부터 결과 값을 제공받지 않고, 자신에게 주어진 조건으로만 처리범위를 결정한다.(독립적). 2) 각자 SORT후에 조인을 하게 되므로 부분범위 처리가 아닌 전체범위 처리를 하게 된다.(전체범위 처리) 3) 조인의 순서에는 상관없다.(무방향성) 4) 인덱스가 아닌 컬럼도 Merge할 작업 대상을 줄이므로 중요한 의미를 가진다. 3. 사용기준 1) 처리량이 많거나 전체범위 처리 시에 유리하다. 랜덤액세스가 많은 Nested Loop Join은 불리 2) 스스로 자신의 처리범위를 많이 줄일 수 있을 때 유리하다. 3) 연결고리 이상 상태에 영향을 받지 않으므로 연결고리 컬럼을 위한 인덱스를 생성하지 않고도 유용하게 사용할 수 있다. 4) 처리할 데이터량이 적은 온라인 어플리케이션에서는 Nested Loop Join이 유리한 경우가 많으므로 Sort Merge Join은 주의하여 사용한다. <처리순서> 1. a.FLD1 = ‘AB’ 조건으로 인덱스를 경유하여 TAB1 Access 후 결과 집합 Sort 2. b.FLD2 = ’10’ 조건으로 인덱스를 경유하여 TAB2 Access 후 결과 집합 Sort 3. 1,2 에서 Sort된 2개의 집합을 Merge

7 . . . . . . TAB1 TAB2 2. Join 의 종류 SELECT /*+ use_hash(a b) */
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 파 티 션 1 파 티 션 2 수행순서 1) 두 테이블 중 적은 테이블을 선행 테이블로 결정한다. 2) 선행 테이블을 Hash Function을 이용하여 Hash Area에 Hash Table을 구성한다.(Build Input) 3) 만약 Hash Area만으로 생성 가능하다면 후행테이블은 크기에 상관없이 차례로 Hash Function을 이용하여 Hash Table과 조인(Probe Input)하면서 성공한 결과값을 운반단위로 이동한다. 4) 만약 Hash Area만으로 Hash Table 생성이 불충분 하다면 Hash Table Overflow가 발생하여 데이터를 나눠서 저장 할 Partition 수를 결정한다.(Fan -out) 5) 선행 테이블의 조인 컬럼과 Select List 컬럼을 메모리로 읽어 들여 첫번째 해쉬 함수를 이용하여 Partition을 Mapping하고, 두번째 해쉬 함수를 이용하여 해쉬 테이블 생성시 사용 할 해쉬 값을 생성한다. 6) 선행 테이블의 조인 컬럼의 유일 값만으로 Bit-Vector을 생성한다. –추후 Bit-Vector filtering에 사용하기 위함. 7) Partition에 데이터를 MOVE하고 채워진 Partition은 디스크로 내려간다. 8) 선행 테이블이 모두 읽혀지면 Partition 테이블을 완성하고, Partition 크기순으로 정렬한 후 작은 Partition N개를 메모리에 로드한다. 9) 후행 테이블을 읽으면서 조인컬럼으로 Bit-Vector와 Filtering에 성공하였다면, 첫번째 해쉬함수로 Partition을 결정하고, 두번째 해쉬 함수를 이용하여 메모리 상에 있는 선행테이블과 조인하고 성공하면 운반단위로 이동하고, 해당 Partition이 메모리에 존재하지 않는다면 해쉬 키값,조인컬럼, Select List를 디스크에 쓴다. 10) 후행 테이블이 모두 읽혀지면, Bit-Vector Filtering에 성공했지만, 조인에 성공하지 못해 미 처리된 선행 파티션과 후행 파티션을 메모리에 올려 차례로 반복수행 한다. 액세스해야 할 데이터가 많을 경우 Sort Merge Join은 Merge 단계에 들어 가기 위해 양쪽 테이블의 처리 범위가 SORT 되어야 하므로 SORT에 대한 부담이 크고, Nested Loop Join은 선행 테이블의 처리 범위가 넓을 경우 그 만큼 랜덤 액세스의 발생으로 수행속도를 보장할 수 없다. 이해 반해 Hash Join은 다른 조인에 시스템 리소스를 가장 많이 사용하지만 Hash Function을 이용함으로써 Sort를 하지 않고, 각 테이블에 한번만 액세스하여 조인이 이루어 지므로 큰 테이블간이나, 큰 테이블과 적은 테이블의 조인에 효율적인 조인 방법이다. Hash Join의 특징 및 사용기준은 다음과 같다. 특징 1) 다른 테이블의 결과 값을 제공받지 않고, 자신에게 주어진 조건으로만 처리범위를 결정한다.(독립적). 2) 해쉬 함수를 이용 하게 되므로 부분범위 처리를 할 수 없으며 전체범위 처리를 하게 된다.(전체범위 처리) 3) 메모리 영역만으로 해쉬 테이블을 생성시 최적의 효과를 낼 수 있으므로 적은 테이블이 선행으로 온다. 4) 해쉬 함수를 이용하므로 결과값의 정렬을 보장 받을 수 없다. 사용기준 1) 대량의 데이터 액세스 시, 배치 처리, Full Table Scan 하면서 조인 해야 할 때 유리하다. 2) 비용은 많이 들지만 수행속도를 보장해야 하는 작업에 유용하다.(Parallel Query 사용) <처리순서> 1. a.FLD1 = ‘AB’ 조건으로 인덱스를 경유하여 TAB1 Access 후 결과 집합 생성 2. b.FLD2 = ’10’ 조건으로 인덱스를 경유하여 TAB2 Access 후 결과 집합 생성 3. 1,2 에서 생성된 2개의 집합으로 파티션 짝을 생성 4. 크기가 작은 파티션을 메모리에 로딩(Building) 하여 Hash Table 생성 5. 나머지 파티션의 로우을 읽어 Hash Table에 대응되는 로우가 있는지를 체크 . . . . . . 운반단위 INDEX (FLD1) TAB TAB2 INDEX (FLD2)

8 CHOOSE ALL_ROWS FIRST_ROWS RULE
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 Optimizer Mode Setting Optimizer Approach 에 영항을 주는 요인
Initial Paramemer ($ORACLE_HOME/dbs/initSID.ora)에 기술 OPTIMIZER_MODE = CHOOSE Instance Level ALTER SESSION SET OPTIMIZER_MODE = RULE; ALTER SESSION SET OPTIMIZER_GOAL = RULE; Session Level Statement Level ORACLE Hint 사용 (/*+ hint */) RULE, CHOOSE, FIRST_ROWS, ALL_ROWS Optimizer Approach 에 영항을 주는 요인 Optimizer Mode Analyze (통계정보) Oracle Hints

10 Rule-Based Optimizer 의 Ranking
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 SELECT /*+ INDEX(EMP JOB_IDX) */ *
3. Optimizer 3.3 Optimizer의 원리(계속) 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 ※ FIRST_ROWS 힌트가 무시되는 경우 DELETE, UPDATE Statements
4. Hint 의 종류 및 내용 4.1 Optimization Approaches and Goals Hint Description 사용예 ALL_ROWS Best Troughput /*+ ALL_ROWS */ FIRST_ROWS Best Response-Time /*+ FIRST_ROWS */ ※ FIRST_ROWS 힌트가 무시되는 경우 DELETE, UPDATE Statements Set Operators (UNION, INTERSECT, MINUS, UNION ALL) GROUP BY clause FOR UPDATE clause Aggregate functions DISTINCT operator CHOOSE 통계정보가 있는 경우 ALL_ROWS, 통계정보가 없는 경우 RULE /*+ CHOOSE */ RULE /*+ RULE */

13 SELECT /*+ ROWID(emp) */ * FROM emp
4. Hint 의 종류 및 내용 4.2 Hint for Access Methods Hint Description 사용예 FULL Full Table Scan /*+ FULL(emp) */ ROWID Table scan by rowid /*+ ROWID(emp) */ SELECT /*+ ROWID(emp) */ * FROM emp WHERE ROWID > ‘AAAtkBBBDDDFEEFFFE’ AND empno = 123; CLUSTER Cluster scan /*+ CLUSTER(emp) */ INDEX Index scan /*+ INDEX(emp emp_idx) */ INDEX 힌트 다음에 인덱스명을 여러 개 나열할 경우 해당 인덱스중 Cost가 가장 낮은 인덱스를 선택하고, 인덱스명을 생략할 경우 사용가능한 인덱스중 Cost가 가장 낮은 인덱스를 선택 INDEX_ASC INDEX 힌트와 동일 /*+ INDEX_ASC(emp emp_idx) */ INDEX_COMBINE BITMAP 인덱스의 Boolean Combination 사용 (BITMAP AND, OR Operation등) /*+ INDEX_COMBINE(emp sal_bmi hiredate_bmi) */ INDEX_DESC Index Descending Range Scan /*+ INDEX_DESC(emp emp_idx) */

14 Fast Full Index Scan (rather than a full table scan)
4. Hint 의 종류 및 내용 4.2 Hint for Access Methods(계속) Hint Description 사용예 INDEX_FFS Fast Full Index Scan (rather than a full table scan) /*+ INDEX_FFS(emp emp_idx) */ NO_INDEX 지정된 인덱스를 사용하지 않음 /*+ NO_INDEX(emp emp_idx) */ AND_EQUAL Single Column 인덱스 Merge (2개부터 Max 5개 까지 지정가능) /*+ AND_EQUAL(table index1 index2 .. Index5) */ USE_CONCAT Union all 집합 연산자를 사용하여 질의의 Where 절에 있는 결합된 OR 조건을 혼합질의로 변환 /*+ USE_CONCAT */ NO_EXPAND Where절에 있는 OR 조건 또는 In-list를 Union all을 이용한 혼합질의로 변환하지 않음 /*+ NO_EXPAND */ Hints for Join Orders Hint Description 사용예 ORDERED FROM절에 나타난 순서대로 Join 수행 /*+ ORDERED */ STAR Star Query Plan 사용 /*+ STAR */

15 Hints for Parallel Executions
4.3 Hint for Join Operations and Parallel Exec. Hint Description 사용예 USE_NL Nested Loop Join 사용 /*+ USE_NL(emp dept) */ USE_MERGE Sort Merge Join 사용 /*+ USE_MERGE(emp dept) */ USE_HASH Hash Join 사용 /*+ USE_HASH(emp dept) */ DRIVING_SITE ORACLE이 선택한 Site외에 다른 Site를 Driving Site로 지정해서 Query를 실행 /*+ DRIVING_SITE(emp) */ LEADING Join 순서에 있어서 지정된 테이블을 먼저 Access /*+ LEADING(emp) */ HASH_AJ & MERGE_AJ Not-In Subquery 사용시 Hash Anti-Join, Sort Merge Anti-Join 사용 /*+ HASH_AJ */ /*+ MERGE_AJ */ Hints for Parallel Executions Hint Description 사용예 PARALLEL Parallel Operation 사용 /*+ PARALLEL(emp, 5) */ NOPARALLEL Parallel Operation을 사용하지 않음 /*+ NOPARALLEL(emp) */

16 View 또는 Sub-Query의 내용을 Merge /*+ MERGE(v_emp) */ NO_MERGE
4. Hint 의 종류 및 내용 4.4 Additional Hints Hint Description 사용예 CACHE Full Table Scan시 추출된 Block을 Buffer cache의 Most recently used LRU End 쪽에 위치시킴(Small Lookup 테이블에 유리) /*+ CACHE(emp) */ NOCACHE Full Table Scan시 추출된 Block을 Buffer cache의 Least recently used LRU End 쪽에 위치시킴(메모리에서 빨리 Flush됨) /*+ NOCACHE(emp) */ MERGE View 또는 Sub-Query의 내용을 Merge /*+ MERGE(v_emp) */ NO_MERGE View 또는 Sub-Query의 내용을 Merge하지 않음 /*+ NO_MERGE(v_emp) */ PUSH_PRED 조건을 View내부에서 사용 /*+ PUSH_PRED(v_emp) */ PUSH_SUBQ Sub-Query를 먼저 수행 /*+ PUSH_SUBQ */ STAR_TRANSFORMATION STAR Query 수행시 Cartesian Product를 피하고 Sub-Query형태로 변환 /*+ STAR_TRANSFORMATION */

17 - receives the optimal plan from the optimizer
5. SQL Processing Architectures User Result 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 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 Statement 사용 → PLAN_TABLE에 실행계획 Insert
PLAN_TABLE에서 Query PLAN_TABLE 생성 : $ORACLE_HOME/rdbms/admin/utlxplan.sql 실행 SQL Statement Explain Plan 예 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); ID OPERATION OPTIONS OBJECT_NAME SELECT STATEMENT FILTER NESTED LOOPS TABLE ACCESS FULL EMP TABLE ACCESS BY ROWID DEPT INDEX UNIQUE SCAN PK_DEPTNO TABLE ACCESS FULL SALGRADE

19 6. Execution Plan (계속) ♣ OPERATION OPTION 설 명 AGGREGATE 그룹함수(SUM, COUNT 등)를 사용하여 하나의 로우가 추출되도록 하는처리 AND-EQUAL 인덱스 머지를 이용하는 경우 중복 제거, 단일 인덱스 컬럼을 사용하는 경우 CONNECT BY CONNECT BY를 사용하여 트리구조로 전개 CONCATENATION 단위 액세스에서 추출한 로우들의 합집합을 생성(UNION-ALL) COUNTING 테이블의 로우 수를 센다. FILTER 선택된 로우에 대해서 다른 집합에 대응되는 로우가 있다면 제거하는 작업 FIRST ROW 조회 로우 중에 첫 번째 로우만 추출한다. FOR UPDATE 선택된 로우에 LOCK을 지정한다. INDEX UNIQUE RANGE SCAN UNIQUE 인덱스를 사용(단 한 개의 로우를 추출) NON-UNIQUE한 인덱스를 사용(한개 이상의 로우) INTERSECTION 교집합의 로우를 추출한다.(같은 값이 없다) MERGE JOIN 먼저 자신의 조건만으로 액세스한 후 각각을 소트하여 머지해 가는 조인 MINUS MINUS 함수를 사용한다.

20 6. Execution Plan (계속) ♣ OPERATION OPTION 설 명 NESTED LOOPS 먼저 어떤 드라이빙 테이블의 로우를 액세스한 후 그 결과를 이용해 다른 테이블을 연결하는 조인 REMOTE 다른 분산 데이타베이스에 있는 객체를 추출하기 위해 데이타베이스 링크를 사용하는 경우 SORT AGGREGATE UNIQUE GROUP BY JOIN ORDER BY 그룹함수(SUM, COUNT 등)를 사용하여 하나의 로우가 추출되도록 하는 처리 같은 로우를 제거하기 위한 소트 액세스 결과를 GROUP BY하기 위한 소트 머지 조인을 하기 위한 소트 ORDER BY를 위한 소트 TABLE ACCESS FULL CLUSTER HASH BY ROWID 전체 테이블 스캔 클러스터 액세스 키값에 대한 해쉬 알고리즘을 사용 ROWID를 이용하여 테이블을 추출 UNION 두 집합의 합집합을 구한다.(중복없음) 항상 전체 범위를 구한다. UNION ALL 두 집합의 합집합을 구한다.(중복가능) UNOIN과 다르게 부분범위 처리를 한다. VIEW 어떤 처리에 의해 생성되는 가상의 집합(뷰)에서 추출한다.(주로 서브쿼리에 의해서 수행된 결과)

21 Initial Parameter의 USER_DUMP_DEST에 지정된 위치에 생성
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되어 있어야만 정확한 시간 산출가능 Trace Enable  Trace enable 위의 2개 : 자신의 session에 아래 2개 : 타 Session에 Trace 를 검 (이 2개는 DBA권한이 SYS.DBMS_SYSTEM Package를 실행할 수 있는 권한이 있는 user에서 가능함) 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 4 : 기본정보 + Binding 정보 출력
7. ORACLE Trace 의 활용 7.1 Trace Enabling (계속) Trace Level 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 의 활용 TKPROF ? Trace File을 쉽게 읽을 수 있는 Format으로 요약 TKPROF 로 얻을 수 있는 내용 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 사용 예 $> tkprof ora_1234_master5.trc prf explain=apps/apps sys=no $> tkprof ora_1234_master5.trc prf sort=(exeela, fchela) $> tkprof ora_1234_master5.trc prf explain=scott/tiger insert=store.sql sys=no sort=(execpu, fchcpu)

24 TKPROF Options filename1
7. ORACLE Trace 의 활용 7.2 TKPROF 의 활용(계속) TKPROF Options filename1 Trace File = USER_DUMP_DEST 에 ora_spid_sid.trc 형태로 만들어진다 filename2 Tkprof 파일 명….이것은 원하는 파일 명을 줄 수 있다. <예…skk.prf> EXPLAIN Explain=user명 / Password explain 을 사용하면, tkprof 시 다시 plan 정보를 뜬다. 따라서, plan 정보가 해당 trace 시와 다를 수도 있다. 주의 요망. SYS Sys=yes / no (Default yes ) Enables and disables the listing of SQL statements issued by the user SYS, or recursive SQL statements, into the output file. The default value of YES causes TKPROF to list these statements. PRINT print=10 (Default 지정하지 않으면 all ) Lists only the first integer sorted SQL statements into the output file. AGGREGATE aggregate=no (Default yes ) If you specify AGGREGATE = NO, then TKPROF does not aggregate multiple users of the same SQL text. SKIP!!

25 select count(*), sum(salqty) from trans t, diss d
7. ORACLE Trace 의 활용 7.2 TKPROF 결과항목 분석 select count(*), sum(salqty) from trans t, diss d where t.idno=d.idno and t.transdate=d.dissdate and t.transdept = :b3 and d.dissseq=’6’ call count cpu elapsed disk query current rows Parse Execute Fetch Total Misses in Library cache during parse : 1 Parsing user id = 12 (scott) Rows Execution Plan 0 SELECT STATEMENT SORT (AGGREGATE) NESTED LOOPS TABLE ACCESS(FULL) OF ‘TRANS’ TABLE ACCESS(BY ROWID) OF ‘DISS’       759                  INDEX(UNIQUE SCAN) OF ‘DISS_PK’ (UNIQUE) Parse, Execute, Fetch 값들 설명

26 Systax Check, Semantic Check, Execution Plan 생성 EXECUTE
7. ORACLE Trace 의 활용 7.2 TKPROF 결과항목 분석 (계속) PARSE Systax Check, Semantic Check, Execution Plan 생성 EXECUTE This step is the actual execution of the statement by Oracle. For INSERT, UPDATE, and DELETE statements, this step modifies the data. For SELECT statements, the step identifies the selected rows.   FETCH SELECT 결과를 fetch 한 회수 (row 와 구분) COUNT parse, execute, fetch 된 횟수   CPU Total CPU time in seconds for all parse, execute, or fetch calls for the statement.   ELAPSED Total elapsed time in seconds for all parse, execute, or fetch calls for the statement.   DISK Total number of data blocks physically read from the datafiles on disk for all parse, execute, or fetch calls.   QUERY Total number of buffers retrieved in consistent mode for all parse, execute, or fetch calls. Buffers are usually retrieved in consistent mode for queries. CURRENT Total number of buffers retrieved in current mode. Buffers are retrieved in current mode for statements such as INSERT, UPDATE, and DELETE.   Misses in library cache The number of library cache misses resulting from parse and execute steps for each SQL statement. 위의 Page에서 설명함

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

28 INDEX COLUMN의 변형(external)
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 = SELECT * FROM EMP WHERE SAL = / 12 SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'YYMMDD') = '940101' SELECT * FROM EMP WHERE HIREDATE = TO_DATE('940101','YYMMDD')

29 INDEX COLUMN의 변형(external)
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 || JOB = '10SALESMAN' SELECT * FROM EMP WHERE DEPTNO = '10' AND JOB = 'SALSMAN' 의도적인 SUPPRESSING SELECT * FROM EMP WHERE JOB = 'MANAGER' SELECT * FROM EMP WHERE RTRIM(JOB) = 'MANAGER' SELECT * FROM EMP WHERE EMPNO = 8978 SELECT * FROM EMP WHERE RTRIM(EMPNO) = 8978

30 INDEX COLUMN의 변형(internal)
8. 인덱스를 이용하지 않는 경우 8.2 Index Column의 변형 (계속) INDEX COLUMN의 변형(internal) SELECT * FROM SAMPLET WHERE CHA = 10 CREATE TABLE SAMPLET ( CHA CHAR(10), NUM NUMBER (12,3), VAR VARCHAR2(20), DAT DATE) SELECT * FROM SAMPLET WHERE TO_NUMBER(CHA) = 10 SELECT * FROM SAMPLET WHERE NUM LIKE '9410%' SELECT * FROM SAMPLET WHERE DAT = '01-JAN-94' SELECT * FROM SAMPLET WHERE TO_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 FROM EMP b WHERE b.JOB = 'SALES'

32 8. 인덱스를 이용하지 않는 경우 SELECT * FROM EMP WHERE ENAME IS NOT NULL SELECT *
8.4 IS NULL & IS NOT NULL SELECT * FROM EMP WHERE ENAME IS NOT NULL SELECT * FROM EMP WHERE ENAME > ' ' SELECT * FROM EMP WHERE COMM IS NOT NULL SELECT * FROM EMP WHERE COMM > 0 SELECT * FROM EMP WHERE COMM IS NULL

33 테이블과 달리 물리적인 저장공간을 가지지 않음 관련정보가 단지 Data Dictionary에 저장될 뿐임
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 공유할 수 없는 SQL SELECT * FROM EMP; 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;