💡 SQL TRACE란?
특정 SQL이 느릴 때 느린 이유를 정확하게 분석할 수 있게 해주는 오라클 툴
[실습1] 오렌지에서 SQL Trace 를 생성하시오
먼저 ftp가 되게 해줘야해서 root 유져로 접속해서 다음과 같이 설치
yum install -y vsftpd
systemctl start vsftpd
systemctl enable vsftpd
firewall-cmd --permanent --add-port=21/tcp
firewall-cmd --reload
select ename, sal
from emp
where ename='SCOTT'
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- --------- ------------ --------------- ------------ ------------ ------------ ------------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.000 0 8 0 1
------- --------- ------------ --------------- ------------ ------------ ------------ ------------
Total 4 0.000 0.000 0 8 0 1💡 elapsed time(총 걸린 시간) = cpu time(서비스 시간) + wait time(기다린 시간)
--> 대부분은 wait time 때문에 SQL이 느림. 느린 원인을 분석하려면 대기 이벤트를 봐야함
대기이벤트를 보려면 다음과 같이 설정
option - Trace Level - 12번
아래쪽에 대기 이벤트 정보가 나옴
문제1. 아래의 SQL의 튜닝전과 튜닝후를 SQL trace로 비교하시오
@demo
select ename, sal
from emp
where sal = ( select max(sal)
from emp ); 튜닝 전:
select ename, sal
from emp
where sal = ( select max(sal)
from emp )
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- --------- ------------ --------------- ------------ ------------ ------------ ------------
Parse 1 0.008 0.023 2 16 12 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.000 0 15 0 1
------- --------- ------------ --------------- ------------ ------------ ------------ ------------
Total 4 0.009 0.023 2 31 12 1
Misses in library cache during parse : 1
Optimizer Goal : ALL_ROWS
Parsing user : SCOTT (ID=106)
Rows Row Source Operation
------- -----------------------------------------------------------------------------------------
1 TABLE ACCESS FULL EMP (cr=15 pr=0 pw=0 str=1 time=213 us cost=3 size=20 card=1)
1 SORT AGGREGATE (cr=7 pr=0 pw=0 str=1 time=103 us)
14 TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 str=1 time=88 us cost=3 size=182 card=14)
Wait Event Name Count Wait(sec) Max Wait
--------------------------------------------------------- ---------- ------------- ---------------
SQL*Net message to client 2 0.000 0.000
log file sync 1 0.000 0.000
SQL*Net message from client 2 0.001 0.001
--------------------------------------------------------- ---------- ------------- ---------------튜닝 후:
create index emp_sal
on emp(sal);
select /*+ index_desc(emp emp_sal) */ ename, sal
from emp
where sal >= 0 and rownum=1
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- --------- ------------ --------------- ------------ ------------ ------------ ------------
Parse 1 0.009 0.010 0 4 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.000 0 2 0 1
------- --------- ------------ --------------- ------------ ------------ ------------ ------------
Total 4 0.009 0.010 0 6 0 1
Misses in library cache during parse : 1
Optimizer Goal : ALL_ROWS
Parsing user : SCOTT (ID=106)
Rows Row Source Operation
------- -----------------------------------------------------------------------------------------
1 COUNT STOPKEY (cr=2 pr=0 pw=0 str=1 time=34 us)
1 TABLE ACCESS BY INDEX ROWID BATCHED EMP (cr=2 pr=0 pw=0 str=1 time=33 us cost=2 size=280 card=14)
1 INDEX RANGE SCAN DESCENDING EMP_SAL (cr=1 pr=0 pw=0 str=1 time=21 us cost=1 size=0 card=14)
Wait Event Name Count Wait(sec) Max Wait
--------------------------------------------------------- ---------- ------------- ---------------
SQL*Net message to client 3 0.000 0.000
SQL*Net message from client 3 0.002 0.001
--------------------------------------------------------- ---------- ------------- ---------------
Total 6 0.002
**************************************************************************************************[실습2] 오렌지를 쓰지 않고 위의 SQL trace 결과를 출력하시오
alter session set sql_trace=true;
alter session set events '10046 trace name context forever, level 12';
select /*+ index_desc(emp emp_sal) */ ename, sal
from emp
where sal >=0 and rownum = 1;
alter session set events '10046 trace name context off';putty에서 아래의 위치로 이동
[oracle@ora19c trace]$ cd /u01/app/oracle/diag/rdbms/ora19dw/ora19dw/trace
[oracle@ora19c trace]$
[oracle@ora19c trace]$ ls -rlt
-rw-r-----. 1 oracle oinstall 1402 9월 9 10:52 ora19dw_ora_4876.trm
-rw-r-----. 1 oracle oinstall 3258 9월 9 10:52 ora19dw_ora_4876.trc
-rw-r-----. 1 oracle oinstall 46963 9월 9 11:05 ora19dw_ora_10058.trm
-rw-r-----. 1 oracle oinstall 207626 9월 9 11:05 ora19dw_ora_10058.trc
[oracle@ora19c trace]$ tkprof explain=scott/tiger sys=no trace=ora19dw_ora_10058.trc
output = result01.txt
TKPROF: Release 19.0.0.0.0 - Development on 화 9월 9 11:10:05 2025
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
[oracle@ora19c trace]$ vi result01.txt