SQL TRACE FACILITY (TKPROF UTILITY 사용)
========================================

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 하여 사용하는방법

1. SQL TRACE enable 및 TRACE 화일 디렉토리 지정

< INSTANCE 단위 >

?/dbs/initSID.ora 화일에 다음 두개의 PARAMETER 를 추가하고
DATABASE 를 다시 STARTUP 시킨다.

sql_trace = true

timed_statistics = true

timed_statistics는 시스템에 많은 LOAD가 걸리므로 사용하지
않는 것이 좋다.


< SESSION 단위 >

SQL*PLUS
timed_statistics = true initSID.ora
(V7.3 이상에서는
SQL> ALTER SESSION SET TIMED_STATISTICS = TRUE;)

$ sqlplus scott/tiger

SQL> ALTER SESSION SET SQL_TRACE = TRUE;
SQL> sql문장 실행
SQL> exit


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;


이렇게 하면 user_dump_dest directory에 trace file이 생성된다.
user_dump_dest가 어디로 지정되어 있는지는 다음과 같이 확인한다.
sqlplus system/manager

SQL>select value from v$parameter where name = 'user_dump_dest';


2. TRACE 화일 변환

2. 단계에서 SQL 문을 실행하면 user_dump_dest 에 지정된 디렉토리에
TRACE 화일이 생기고 tkprof 를 이용하여 화일을 변환시킨다. TRACE 화일은
쉽게 찾을 수 있는 형태가 아니므로 SQL 문을 실행하기 전에 dump 디렉토리에
있는 ora_xxxx.trc 화일을 모두 삭제하거나 가장 최근에 생긴 화일 중에서
찾아야 한다.

(이 때 해당 user에 plan_table 이라는 table이 없으면
@$ORACLE_HOME/rdbms/admin/utlxplan.sql 을 수행하여 table을 만든다)

예. $ 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 한다.



3. 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)

*********************************************************************


분석 예)

a. CPU, elapsed 정보가 없는 경우는 initSID.ora 에
Timed_statistics = false로 되어 있기 때문이다.

b. Execute count와 Fetch count가 동일하게 크다고 하면 ARRAY FETCH
사용을 고려함.

c. fetch된 rows 수 : query + current = 1 : 4 이하이면 SQL 문은 적절히
사용된 경우이고, row 수에 비하여 query + count가 상당히 크면 부적절하게
사용된 SQL 문이므로(count, sum, distinct 등 Group function을 사용하는
경우는 예외) 다음 내용들에 대해서 재검토가 필요하다.

.INDEX 사용, 구성 여부
.ROWID 사용
.COST_BASED OPTIMIZER 사용(ORACLE 7)
.ARRAY FETCH 사용
.SORTING을 피할 수 있는 SQL 문 구사

d. Parse count, Execute count가 비슷한 경우 RELEASE_CURSOR,
HOLE_CURSOR OPTION 사용하여 Parse count를 줄임.