|
SQL TRACE FACILITY 를 사용하여 APPLICATION TUNING 하는 방법
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 하여 사용하는방법
[b]1. SQL TRACE enable 및 TRACE 화일 디렉토리 지정[/b]
< 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
[b]2. SQL 문 실행[/b]
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;
[b]3. TRACE 화일 변환[/b]
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 한다.
[b]4. SQL TRACE 결과 분석[/b]
**********************************************************************
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
Parse
Execute
Fetch
Count
-------
2
2
2
Cpu
------
0.0
0.0
0.0
elapsed
-----
0.0
0.0
0.0
disk
-----
0
0
0
query
----
0
0
0
current
-------
0
0
33
rows
----
0
0
2
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 를 줄임.
Oracle Korea Customer Support Technical Bulletins
|
