Site icon DBA의 정석

SQL TRACE FACILITY (TKPROF UTILITY 사용)

haisins

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

 

  1. 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;

 

  1. 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 한다.

 

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

 

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

분석 예)

  1. cpu, elapsed   정보가없는 경우는initSID.ora 에timed_stattistic = false 로 되어 있기

때문이다.

  1. Execute count 와Fectch count 가 동일하게 크다고하면ARRAY FETCH 사용을 고려함.
  2. 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 문 구사

  1. Parse count,Execute count 가 비슷한경우 RELEASE_CURSOR,

HOLE_CURSOR OPTION 사용하여Parse count 를 줄임.

Exit mobile version