TKPROF 사용 기본

1. pktrof 위치 (ex:로컬 오라클 11 Express Edition)

D:\oraclexe\app\oracle\product\11.2.0\server\bin

2. 트레이스 파일 위치는 아래와 쿼리로 확인

select r.value || '/' || lower(t.instance_name) || '_ora_'  

    || ltrim(to_char(p.spid)) || '.trc' trace_file          

from   v$process p, v$session s, v$parameter r, v$instance t

where  p.addr = s.paddr                                     

and    r.name = 'user_dump_dest'                            

and    s.sid = (select sid from v$mystat where rownum = 1) ;

3. 파일명을 찾기 쉽게 변환하기 위해 세션에 자기만의 tracefile 명칭 심기

alter session set tracefile_identifier='funora';

4.자기 세션에 trace 실행 및 쿼리 실행

alter SESSION SET SQL_TRACE = TRUE;

SELECT  DEPTNO, JOB, SUM(SAL)

FROM    EMP

GROUP BY DEPTNO, JOB

UNION ALL

SELECT  DEPTNO, NULL JOB, SUM(SAL)

FROM    EMP

GROUP BY DEPTNO, NULL

ORDER BY DEPTNO, JOB;

alter SESSION SET SQL_TRACE = FALSE;

5.tkprof를 이용해 변환

tkprof 파일명 변환할파일명

ex) tkprof xe_ora_17552_funora.trc  report.prf sys=no

6.메모장등으로 report.prf 를 열기

친절하게도 상단에 각 항목의 설명이 나와있다.

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

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 for update)

rows     = number of rows processed by the fetch or execute call

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

SELECT  DEPTNO, JOB, SUM(SAL)

FROM    EMP

GROUP BY DEPTNO, JOB

UNION ALL

SELECT  DEPTNO, NULL JOB, SUM(SAL)

FROM    EMP

GROUP BY DEPTNO, NULL

ORDER BY DEPTNO, JOB

call     count       cpu    elapsed       disk      query    current     rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute     1      0.00       0.00          0          0          0           0

Fetch        1      0.00       0.00          0         12          0          12

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total         3      0.00       0.00          0         12          0          12

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 48  

Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation

---------- ---------- ----------  ---------------------------------------------------

   12   12   12  SORT ORDER BY (cr=12 pr=0 pw=0 time=508 us cost=8 size=186 card=14)

   12   12   12   UNION-ALL  (cr=12 pr=0 pw=0 time=289 us)

    9     9    9       HASH GROUP BY (cr=6 pr=0 pw=0 time=284 us cost=4 size=165 card=11)

   14   14   14        TABLE ACCESS FULL EMP (cr=6 pr=0 pw=0 time=42 us cost=3 size=210 card=14)

    3     3     3      HASH GROUP BY (cr=6 pr=0 pw=0 time=131 us cost=4 size=21 card=3)

   14   14   14        TABLE ACCESS FULL EMP (cr=6 pr=0 pw=0 time=11 us cost=3 size=98 card=14)

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

CR : Consistent Block Read

PR : Disk Block Read

PW : Disk Block Write

TIME : Elapsed Time

오라클 성능고도화