출처 :
정현호님 블로그(https://hoing.io/archives/236)
친절한 SQL 튜닝<조시형>
박영민님 블로그(https://positivemh.tistory.com/364)
trace file 사용
10046 event
기본 trace 정보를 확장하여 더 세부적인 정보를 확인할 때 사용하는 이벤트.
level 0 : SQL_TRACE=FALSE 와 동일
level 1 : SQL_TRACE=TRUE 와 동일
level 4 : level 1 + bind 변수 정보
level 8 : level 1 + wait event 정보
level 12 : level 1 + bind 변수 정보 + wait event 정보
10053 event
Optimizer가 내부적인 비용 계산에 대한 정보를 확인할 때 사용하는 이벤트.
CBO(Cost-Based Optimizer, 11g이후. cost를 예측하여 가장 낮은 cost로 동작.) 가 무엇을 기준으로 동작했는지 확인 가능.
alter session set events '10046 trace name context forever, level 12';
-> 10046 trace level 12로 생성 시작.
alter session set tracefile_identifier='SQLPLAN1';
-> trace 파일 네이밍해서 구분 편하게.
<실행계획 뽑고 싶은 쿼리 수행>
alter session set events '10046 trace name context off';
-> 10046 trace 생성 중지.
tkprof <.trc file> <원하는 output> sys=no explane=유저/비밀번호
-> 뽑힌 trace file을 가지고 보기 편한 형태로 다시 만들어주는 OS 명령어
-> sys=no 옵션은 SQL을 파싱하는 과정에서 내부적으로 수행되는 SQL문을 제거해준다.
-> 다만 sys 유저로 trace 파일을 뽑았을 경우 sys=no를 해버리면 수행한 SQL에 대한 정보도 나오지 않게된다...
-> explane은 추출하길 원하는 대상 지정.## 실습
alter session set events '10046 trace name context forever, level 12';
alter session set tracefile_identifier='SQLPLAN1';
select table_name
from dict
where table_name like '%NLS_%';
alter session set events '10046 trace name context off';
-> 이 뒤에는 쿼리를 수행시켜도 trc file이 생성되지 않는다.
tkprof kimtest_ora_4070_SQLPLAN1.trc trc.rpt sys=no
vi trc.rpttrc.rpt
TKPROF: Release 19.0.0.0.0 – Development on Fri Dec 5 10:49:53 2025
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Trace file: kimtest_ora_4070_SQLPLAN1.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
select table_name
from dict
where table_name like ‘%NLS_%’
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.03 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 1 270 0 7
——- —— ——– ———- ———- ———- ———- ———-
total 4 0.03 0.03 1 270 0 7
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 9
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
———- ———- ———- —————————————————
7 7 7 VIEW DICTIONARY (cr=288 pr=1 pw=0 time=1064 us starts=1 cost=465 size=1914 card=29)
7 7 7 UNION-ALL (cr=288 pr=1 pw=0 time=1063 us starts=1)
0 0 0 VIEW VW_ORE_38E573D6 (cr=59 pr=0 pw=0 time=997 us starts=1 cost=83 size=1320 card=20)
0 0 0 UNION-ALL (cr=59 pr=0 pw=0 time=997 us starts=1)
0 0 0 FILTER (cr=7 pr=0 pw=0 time=158 us starts=1)
0 0 0 NESTED LOOPS OUTER (cr=7 pr=0 pw=0 time=158 us starts=1 cost=17 size=220 card=4)
0 0 0 INDEX RANGE SCAN I_OBJ2 (cr=7 pr=0 pw=0 time=154 us starts=1 cost=13 size=188 card=4)(object id 37)
0 0 0 INDEX RANGE SCAN I_COM1 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1 size=72 card=9)(object id 260)
0 0 0 FILTER (cr=7 pr=0 pw=0 time=126 us starts=1)
0 0 0 NESTED LOOPS OUTER (cr=7 pr=0 pw=0 time=126 us starts=1 cost=10 size=110 card=2)
0 0 0 INDEX RANGE SCAN I_OBJ2 (cr=7 pr=0 pw=0 time=126 us starts=1 cost=8 size=94 card=2)(object id 37)
0 0 0 INDEX RANGE SCAN I_COM1 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1 size=72 card=9)(object id 260)
0 0 0 FILTER (cr=31 pr=0 pw=0 time=434 us starts=1)
0 0 0 FILTER (cr=13 pr=0 pw=0 time=221 us starts=1)
0 0 0 NESTED LOOPS OUTER (cr=13 pr=0 pw=0 time=221 us starts=1 cost=28 size=385 card=7)
0 0 0 INDEX RANGE SCAN I_OBJ2 (cr=13 pr=0 pw=0 time=221 us starts=1 cost=21 size=329 card=7)(object id 37)
0 0 0 INDEX RANGE SCAN I_COM1 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1 size=72 card=9)(object id 260)
1 1 1 FIXED TABLE FULL X$KZSPR (cr=18 pr=0 pw=0 time=211 us starts=1 cost=0 size=26 card=1)
0 0 0 FILTER (cr=14 pr=0 pw=0 time=274 us starts=1)
0 0 0 FILTER (cr=14 pr=0 pw=0 time=254 us starts=1)
0 0 0 NESTED LOOPS OUTER (cr=14 pr=0 pw=0 time=254 us starts=1 cost=28 size=385 card=7)
0 0 0 INDEX RANGE SCAN I_OBJ2 (cr=14 pr=0 pw=0 time=254 us starts=1 cost=21 size=329 card=7)(object id 37)
0 0 0 INDEX RANGE SCAN I_COM1 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1 size=72 card=9)(object id 260)
1 1 1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=18 us starts=1 cost=0 size=26 card=1)
3 3 3 FILTER (cr=52 pr=0 pw=0 time=63 us starts=1)
9 9 9 NESTED LOOPS OUTER (cr=52 pr=0 pw=0 time=70 us starts=1 cost=24 size=51 card=1)
3 3 3 INLIST ITERATOR (cr=46 pr=0 pw=0 time=78 us starts=1)
3 3 3 INDEX RANGE SCAN I_OBJ2 (cr=46 pr=0 pw=0 time=114 us starts=21 cost=23 size=43 card=1)(object id 37)
9 9 9 INDEX RANGE SCAN I_COM1 (cr=6 pr=0 pw=0 time=25 us starts=3 cost=1 size=72 card=9)(object id 260)
4 4 4 FILTER (cr=177 pr=1 pw=0 time=2095 us starts=1)
4 4 4 NESTED LOOPS (cr=169 pr=0 pw=0 time=1382 us starts=1 cost=359 size=12090 card=62)
4 4 4 NESTED LOOPS (cr=159 pr=0 pw=0 time=1360 us starts=1 cost=167 size=8160 card=96)
7 7 7 INDEX RANGE SCAN I_OBJ2 (cr=143 pr=0 pw=0 time=1326 us starts=1 cost=151 size=4512 card=96)(object id 37)
4 4 4 TABLE ACCESS BY INDEX ROWID SYN$ (cr=16 pr=0 pw=0 time=61 us starts=7 cost=1 size=38 card=1)
7 7 7 INDEX UNIQUE SCAN I_SYN1 (cr=9 pr=0 pw=0 time=28 us starts=7 cost=0 size=0 card=1)(object id 103)
4 4 4 INDEX RANGE SCAN I_OBJ2 (cr=10 pr=0 pw=0 time=23 us starts=4 cost=2 size=110 card=1)(object id 37)
4 4 4 HASH JOIN SEMI (cr=8 pr=1 pw=0 time=620 us starts=4 cost=2 size=22 card=1)
5 5 5 INDEX RANGE SCAN I_OBJAUTH1 (cr=8 pr=1 pw=0 time=201 us starts=4 cost=2 size=9 card=1)(object id 62)
4 4 4 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=20 us starts=4 cost=0 size=1300 card=100)
0 0 0 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us starts=0 cost=0 size=26 card=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
—————————————- Waited ———- ————
SQL*Net message to client 2 0.00 0.00
PGA memory operation 1 0.00 0.00
SQL*Net message from client 2 3.87 3.87
db file sequential read 1 0.00 0.00
********************************************************************************
SQL ID: 06nvwn223659v Plan Hash: 0
alter session set events ‘10046 trace name context off’
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
——- —— ——– ———- ———- ———- ———- ———-
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Parsing user id: 9
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 2 0.03 0.02 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 1 270 0 7
——- —— ——– ———- ———- ———- ———- ———-
total 6 0.03 0.03 1 270 0 7
Misses in library cache during parse: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
—————————————- Waited ———- ————
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 6.07 9.94
db file sequential read 1 0.00 0.00
PGA memory operation 2 0.00 0.00
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 9 0.00 0.00 0 0 0 0
Execute 18 0.00 0.00 0 0 0 0
Fetch 29 0.00 0.00 2 50 0 12
——- —— ——– ———- ———- ———- ———- ———-
total 56 0.00 0.00 2 50 0 12
Misses in library cache during parse: 9
Misses in library cache during execute: 9
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
—————————————- Waited ———- ————
db file sequential read 2 0.00 0.00
2 user SQL statements in session.
13 internal SQL statements in session.
15 SQL statements in session.
********************************************************************************
Trace file: kimtest_ora_4070_SQLPLAN1.trc
Trace file compatibility: 12.2.0.0
Sort options: default
1 session in tracefile.
2 user SQL statements in trace file.
13 internal SQL statements in trace file.
15 SQL statements in trace file.
15 unique SQL statements in trace file.
390 lines in trace file.
9 elapsed seconds in trace file.
dbms_xplan.display_cursor() 사용
방법 1. statistics_level=all 설정.
alter session set statistics_level=all;
-> 통계정보 수집
<실행계획 뽑고 싶은 쿼리 수행>
select *
from table(dbms_xplan.display_cursor(null, null, 'advanced allstats last'));
-> 인수는 table_name, statement_id, format 순서대로
-> advanced allstats last나 allstats last advanced 나 상관 없는 듯방법 2. /*+ gather_plan_statistic */ 힌트 사용
select /*+ gather_plan_statistic */ <원하는 쿼리>;
select *
from table(dbms_xplan.display_cursor(null, null, 'advanced allstats last'));