SQL TRACE FACILITY 는SQL 문 사용에대한 성능을 분석하기위해서 사용된다.
이러한SQL TRACE FACILITY 를 이용하면 각SQL 문에 대해서 다음과 같은 정보를 얻을수 있다.
. parse,execute,fetch count
. CPU 와elapsed 시간
. physical reads 와 logical reads
. 처리된row 의 수
SQL TRACE FACILITY 는SESSION 혹은INSTANCE 단위로 할 수 있고TRACE 결과 화일은tkprof UTILITY 에의해 사용자가 읽을수 있는 형태로 변환시킨다.
SQL TRACE SET UP 하여 사용하는방법
———————————-
- SQL TRACE enable 및TRACE 화일 디렉토리 지정
< INSTANCE 단위 >
?/dbs/initSID.ora 화일에 다음 두개의PARAMETER 를 추가하고DATABASE 를 다시STARTUP 시킨다.
sql_trace = true
timed_statistics = true
timed_statistics 은 시스템에 많은LOAD 가 걸리므로 사용하지 않는 것이 좋다.
< SESSION 단위 >
SQL*PLUS
$ sqlplus scott/tiger
SQL > ALTER SESSION SET SQL_TRACE = TRUE;
SQLFORMS30, RUNFORM30 : -s OPTION 사용
$ runform30 -s frmfile scott/tiger -c vt220
PRO*C
EXEC SQL CONNECT :username;
EXEC SQL ALTER SESSION SET SQL_TRACE = TRUE;
TRACE FILE 디렉토리는 initSID.ora 화일에 다음과 같이 지정하고 지정하지 않으면?/rdbms/log 디렉토리에 생긴다.
user_dump_dest = /user/dump
- SQL 문 실행
TRACE 화일은SESSION 단위로1 개씩 생김으로 한SESSION 에서 사용된 모든SQL 문의TRACE 결과는1개의 화일에 존재하게된다.
예. SQL> ALTER SESSION SET SQL_TRACE = TRUE;
SQL> SELECT COUNT(*) FROM EMP E,DEPT D
WHERE E.DEPTNO = D.DEPTNO;
SQL> EXIT;
- TRACE 화일 변환
- 단계에서SQL 문을 실행하면 user_dump_dest 에 지정된 디렉토리에TRACE 화일이 생기고tkprof 를이용하여 화일을 변환시킨다. TRACE 화일은 쉽게 찾을 수 있는 형태가 아니므로SQL문을 실행하기 전에 dump 디렉토리에 있는ora_xxxx.trc화일을 모두 삭제하거나 가장최근에 생긴 화일중에서 찾아야 한다.
예. $ cd $ORACLE_HOME/rdbms/log
$ tkprof ora_1111 out sort=fchqry,fchcu explain=scott/tiger print=20
ora_1111 : TRACE 화일
out : OUTPUT 화일. 디렉토리에out.prf 로 생긴다.
sort : 지정된OPION(fchqry,fchcu) 에ASCENDING 순으로SQL 문을
SORTING 한다.
explain : SQL 문의EXECUTION PLAN 을 발생시킨다.
print : 지정된 갯수의SQL문에대해서만TRACE 결과를PRINT 한다.
- SQL TRACE 결과 분석
**********************************************************************
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 forupdate)
rows = number of rows processed by the fetch or execute call
**********************************************************************
SELECT COUNT(*)
FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO
call count cpu elapsed disk query current rows
—————————————————————–
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 33 2 1
Misses in library cache during parse: 1
Parsing user id: 8 (SCOTT)
Rows Execution Plan
——- —————————————————
0 SELECT STATEMENT
0 SORT (AGGREGATE)
16 NESTED LOOPS
16 TABLE ACCESS (FULL) OF ‘EMP’
16 INDEX (UNIQUE SCAN) OF ‘DEPT_PRIMARY_KEY’ (UNIQUE)
*********************************************************************
분석 예)
- cpu, elapsed 정보가없는 경우는initSID.ora 에timed_stattistic = false 로 되어 있기
때문이다.
- Execute count 와Fectch count 가 동일하게 크다고하면ARRAY FETCH 사용을 고려함.
- fetch 된 rows 수: query + current = 1 : 4 이하이면SQL 문은 적절히 사용된 경우이고,
row 수에 비하여query + count 가 상당히 크면 부적하게 사용된SQL 문이므로
(count,sum,distinct 등Group function 을 사용하는경우는 예외) 다음 내용들에대해서 재검토
가 필요하다.
.INDEX 사용,구성여부
.ROWID 사용
.COST_BASED OPTIMIZER 사용(ORACLE7)
.ARRAY FETCH 사용
.SORTING 을 피할수 있는SQL 문 구사
- Parse count,Execute count 가 비슷한경우 RELEASE_CURSOR,
HOLE_CURSOR OPTION 사용하여Parse count 를 줄임.