요약
- recursive call 을 줄여라
- hard parse 를 줄이고, soft parse 를 유도
= db block gets 나만볼 수 있음
= consistent gets 모든 사람이 봄
= physical reads 디스크에서 읽음
실행계획 순서
가장 오른쪽으로 들여쓰기 된 놈부터
위로 차례대로 가다가
같은 depth가 있으면 확인 후
더 낮은 depth 가 있으면 거기로 고
다시 차례로 실행
SQL 실행 계획 확인 방법
• SQL 튜닝을 위한 기본적인 툴
• SQL Plus의 Autotrace 기능
– 가장 일반적인 방법
– 실행 결과, 실행 계획, 통계 정보
• Oracle Enterprise Manager
– SQL 스크래치 패드(GUI 환경)
• EXPLAIN PLAN 명령어 사용
– PLAN_TABLE에 실행 정보 저장
• SQL Trace 파일 생성 및 TKPROF 사용
– 모든 SQL 문장에 대한 추적 파일 생성
– TKPROF로 추적 파일의 내용 분석
– 실제 튜닝에서 가장 많이 활용
SQL*Plus Autotrace 기능 활성화
• PLUSTRACE 권한 생성
SQL> conn / as sysdba
SQL> @?/sqlplus/admin/plustrce.sql
• 해당 사용자에게 PLUSTRACE 권한 부여
SQL> conn / as sysdba
Connected.
SQL> grant PLUSTRACE to scott;
• 해당 사용자로 로그인 후 PLAN Table 생성
SQL> @?/rdbms/admin/utlxplan.sql
Autotrace 모드 설정
SQL> set autotrace on
SQL> set autot off
SQL> set autotrace traceonly
SQL> set autotrace traceonly explain
SQL> set autotrace traceonly statistics
┌ OFF
SET AUTOTRACE ─├ ON
└ TRACE[ONLY]
─┬─
├ EXPLAIN
└ STATISTICS
SHOW AUTOTRACE
SQL*Plus Autotrace 예제
SQL> set autotrace on
SQL> select * from dept where deptno = 10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
Execution Plan
------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DEPT'
Statistics
-------------------------------------------------------
178 recursive calls
0 db block gets
27 consistent gets
7 physical reads
0 redo size
629 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
비유
Auto Trace - X-ray
SQL Trace - MRI
SQL Trace 기능
• 모든 SQL 수행에 대한 Trace 파일 생성
– 인스턴스 레벨 추적(많은 부하 발생)
• 각 프로세스별 추적 파일 생성
– 세션 레벨 추적
• 구문 분석, 실행 및 인출 단계에 대한 크기 및 시간 통계 정보
• 시스템의 전체적인 분석에 필요
• 관련 초기화 파일
– USER_DUMP_DEST에 추적 파일 생성 됨
– TIMED_STATISTICS = true
• 정확한 시간 통계치
SQL Trace 기능 활성화
• 인스턴스 레벨
– 초기화 파라미터 수정, 재 시동
SQL_TRACE = TRUE
• 현재 접속 중인 세션
10g 부터는 아래 명령을 써주어야 한다.
SYS> grant alter session to scott;
9i 까지는 위 명령어는 안쳐도 된다.
SQL> alter session set sql_trace = true;
SQL> alter session set timed_statistics = true ;
SQL> EXECUTE dbms_session.set_sql_trace(true)
찾기 쉬우라고 trace file 에 abc 를 붙여보았다.
SCOTT> alter session set tracefile_identifier = 'abc'
trace 경로 보기
SYS>show parameter user_dump_dest ;
NAME TYPE VALUE
-------------------------- ----------- ------------------------------
user_dump_dest string /app/oracle/diag/rdbms/testdb/
testdb/trace
• 임의의 세션 추적
– v$session을 통해 SID와 SERIAL# 구함
SQL> desc v$session
SQL> EXECUTE dbms_system.set_sql_trace_in_session (sid, serial#, true);
↑sid, serial# 찾아서 입력
SQL Trace 리포트 생성
• USER_DUMP_DEST에서 해당 trace 파일 확인
• TKPROF를 통해 리포트 생성
$ tkprof ora92_ora_28201.trc scott_stat.txt explain=scott/tiger sys=no
↑ 파일명
$ vi scott_stat.txt
Sample TKPROF Output
*************************************************************
SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno
call count cpu elapsed disk query current rows
------- ------ ------- -------- ----- ------ -------- -----
Parse 1 0.05 0.05 1 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.04 11 15 0 14
------- ------ ------- -------- ----- ------ -------- -----
total 4 0.06 0.10 12 17 0 14
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 62 (SCOTT)
Rows Row Source Operation
------- ---------------------------------------------------
14 HASH JOIN (cr=15 r=11 w=0 time=48250 us)
14 TABLE ACCESS FULL EMP (cr=7 r=6 w=0 time=12349 us)
5 TABLE ACCESS FULL DEPT (cr=8 r=5 w=0 time=33294 us)
*************************************************************
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.00 0 15 0 14
------- ------ ------- -------- ----- ------ -------- -----
total 4 0.01 0.00 0 16 0 14
단계별 통계치 추출
• PARSE
– SQL 문을 실행 계획으로 변환하는 과정에서 발생하는 통계치
• EXECUTE
– 명령문을 실행하면서 발생하는 통계치
– INSERT, UPDATE, DELETE에 관련된 정보
• FETCH
– 인출 시에 발생하는 통계치
– SELECT에 관계된 정보
TKPROF 통계 정보 설명
• 구문 분석, 실행, 인출 단계 별로 통계 정보 표시
통계 정보 설 명
Count 각 처리 단계 별 실행된 수
CPU 각 처리 단계 별 CPU 소모 시간(초)
Elapsed 각 처리 단계의 시작에서 종료까지 총 경과 시간(초)
* cpu타임과 elapsed 타임의 차이가 많이 나면 문제가 있다는 뜻, 대기 시간이 길었다
Disk 각 처리 단계 별 물리적인 디스크 블록 접근한 횟수
Query 각 처리 단계 별 읽은 변경된 버퍼 블록 수(Consistent Read) - 메모리
읽기 일관성 관련/주로 SELECT 문
Current 각 처리 단계 별 현 세션에만 유효한 버퍼 블록을 접근한 수(Current Read)
주로 INSERT,UPDATE,DELETE 작업 시 발생
Rows 각 처리 단계 별 읽은 총 행수
Fetch 단계 : SELECT에 의해 질의된 행 수
Execute 단계 : INSERT, UPDATE, DELETE 문에서 처리된 행수 (DML)
TKPROF DML 예
insert into dept values (80,'Human Resource','HQ')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.01 1 4 0 0
Execute 1 0.00 0.00 3 1 4 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.04 0.02 4 5 4 1
update dept set dname = 'Financial' where deptno = '80'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.01 1 4 0 0
Execute 1 0.00 0.00 5 9 4 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.02 0.02 6 13 4 1
delete from dept where deptno = '80'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.01 1 4 0 0
Execute 1 0.00 0.00 5 8 4 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.02 0.02 6 12 4 1
TKPROF 전체 통계
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ ------- -------- ----- ------ -------- -----
Parse 13 0.10 0.08 1 5 0 0
Execute 14 0.02 0.04 3 25 24 5
Fetch 4 0.02 0.05 11 30 0 28
------- ------ ------- -------- ----- ------ -------- -----
total 31 0.14 0.18 15 60 24 33
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ ------- -------- ----- ------ -------- -----
Parse 20 0.02 0.02 0 0 0 0
Execute 40 0.01 0.01 0 0 0 0
Fetch 55 0.02 0.04 9 108 0 34
------- ------ ------- -------- ----- ------ -------- -----
total 115 0.05 0.09 9 108 0 34
SQL Trace 예제
SQL*Plus > alter session set sql_trace=true;
# tkprof ora_09136.trc output.prf sys=no explain=scott/tiger
SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.sal > 1000
AND e.deptno = d.deptno
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 3 8 12
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.02 0 3 8 12
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 20 (SCOTT)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 HASH JOIN
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'DEPT'
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'EMP'
SQL Trace 실습
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 56019 132.68 339.52 64 1463 35 0
Execute 1148333 7999.77 14251.94 310078 720818303 31207124 262166
Fetch 1809413 14696.39 85047.26 19974039 545566678 4428519 2819520
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3013765 22828.84 99638.72 20284181 1266386444 35635678 3081686
Misses in library cache during parse: 3376
Misses in library cache during execute: 1
- Parse count 와 Execute Count의 차이가 현저하므로 Loop query 포함, hold-cursor 사용 점검 필요.
- 1 의 내용에 의해서 CPU 시간이 많이 걸림을 알 수 있으므로, Sort , Distinct 사용 등의 SQL 을 점검 필요.
- 2의 값이 1의 2~3배를 초과하므로 I/O Bottleneck 이나 network 상의 지체로 인하여 전체 응답속도의 저하
- 5 에 의해서 Index 등의 optimizer 전략 점검 필요.