| |
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
$ 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
2. 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;
3. TRACE 화일 변환
2. 단계에서 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 한다.
4. 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_stattistic = false 로 되어 있기때문이다.
b. Execute count 와 Fectch 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 사용(ORACLE7)
.ARRAY FETCH 사용
.SORTING 을 피할수 있는 SQL 문 구사
d. Parse count,Execute count 가 비슷한경우 RELEASE_CURSOR,
HOLE_CURSOR OPTION 사용하여 Parse count 를 줄임.
|