Tuning - SQL Trace

- Autotrace와는 별개로 별도의 세팅이 필요하며 즉시 볼 수 없으며 덤프 파일이 0생성됨
=> 다른 사람의 사용 내역도 추적할 수 있음

$ cd /home/oracle/admin/testdb/udump/
$ mkdir backup
$ mv *.trc backup/

SQL> conn scott/tiger
Connected.
SQL> alter session set sql_trace=true;
alter session set sql_trace=true
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> conn / as sysdba
Connected.
SQL> grant alter session to scott;

Grant succeeded.

SQL> conn scott/tiger
Connected.
SQL> alter session set sql_trace=true;

Session altered.

- 덤프 파일 내용

=====================
PARSING IN CURSOR #1 len=17 dep=0 uid=54 oct=3 lid=54 tim=1239642295151919 hv=1745700775 ad='299a3ddc'
select * from emp
END OF STMT
PARSE #1:c=248962,e=415408,p=22,cr=366,cu=0,mis=1,r=0,dep=0,og=1,tim=1239642295151916
EXEC #1:c=0,e=14,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1239642295152659
FETCH #1:c=1000,e=852,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=1,tim=1239642295153560
FETCH #1:c=0,e=25,p=0,cr=1,cu=0,mis=0,r=13,dep=0,og=1,tim=1239642295154613
STAT #1 id=1 cnt=14 pid=0 pos=1 obj=51148 op='TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=845 us)'

=> 보기가 힘들어서 TKPROF라는 툴을 사용해서 봄

$ tkprof testdb_ora_4394.trc abc.txt explain=scott/tiger sys=no (scott의 결과만 recursive는 제외)

select *
from
 emp

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.04       0.08          2          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          8          0          14
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.04       0.08          2         10          0          14

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54  (SCOTT)

Rows     Row Source Operation
-------  ---------------------------------------------------
     14  TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=845 us)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
     14   TABLE ACCESS (FULL) OF 'EMP' (TABLE)

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

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.04       0.08          2          2          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          8          0          14
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.04       0.08          2         10          0          14

Misses in library cache during parse: 1 => hard parsing
Misses in library cache during execute: 1

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       17      0.03       0.04          0          0          0           0
Execute    124      0.13       0.14          0          0          0           0
Fetch      141      0.03       0.14         20        364          0         465
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      282      0.20       0.32         20        364          0         465

Misses in library cache during parse: 15
Misses in library cache during execute: 15

    3  user  SQL statements in session.
  123  internal SQL statements in session.
  126  SQL statements in session.
    2  statements EXPLAINed in this session.
********************************************************************************
Trace file: testdb_ora_4394.trc
Trace file compatibility: 10.01.00
Sort options: default

       1  session in tracefile.
       3  user  SQL statements in trace file.
     123  internal SQL statements in trace file.
     126  SQL statements in trace file.
      17  unique SQL statements in trace file.
       2  SQL statements EXPLAINed using schema:
           SCOTT.prof$plan_table
             Default table was used.
             Table was created.
             Table was dropped.
    1074  lines in trace file.
      32  elapsed seconds in trace file.