[Tuning] SQL Trace

사용자가 실행한 SQL 문의 실행계획과 실행시 읽기, 쓰기 등 작업 부하량 검사

▶ 사전 작업

1. timed_statistics = true
SQL> show parameter timed_statistics

NAME                     TYPE            VALUE
------------------------------------ ---------------------- ------------------------------
timed_statistics             boolean            TRUE

2. sql_trace = true, 세션단위로 작업할수 있기때문에 생략가능
SQL> show parameter sql_trace

NAME                     TYPE            VALUE
------------------------------------ ---------------------- ------------------------------
sql_trace                 boolean            FALSE

3. trace 파일이 생성되는 위치확인
SQL> show parameter user_dump_dest

NAME                     TYPE            VALUE
------------------------------------ ---------------------- ------------------------------
user_dump_dest                 string            /Users/oracle/admin/ORCL/udump


4. trace 파일의 최대 사이즈 확인
SQL> show parameter max_dump

NAME                     TYPE            VALUE
------------------------------------ ---------------------- ------------------------------
max_dump_file_size             string            UNLIMITED

▶ SQL TRACE를 이용하여 분석하기

세션레벨에서 sql*trace 사용하기
SQL> alter session set sql_trace = true;

Session altered.

분석하려는 SQL실행
SQL> select count(*) from big_emp where deptno = 10;

  COUNT(*)
----------
      5336

분석결과가 저장된 파일 확인
SQL> !ls -lrt /Users/oracle/admin/ORCL/udump
-rw-r-----  1 oracle  dba  1317 Dec 20 11:26 orcl_ora_328.trc  <== 파일형식 : <SID>_ora_<PID>,trc


분석결과 파일을 텍스트파일로 포맷팅
SQL> !tkprof /Users/oracle/admin/ORCL/udump/orcl_ora_328.trc sqltrace01.txt sys=no

TKPROF: Release 10.2.0.4.0 - Production on Sun Dec 20 11:30:14 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


텍스트파일로 받아진 파일보기
SQL> !more sqltrace01.txt

TKPROF: Release 10.2.0.4.0 - Production on Sun Dec 20 11:30:14 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Trace file: /Users/oracle/admin/ORCL/udump/orcl_ora_328.trc
Sort options: default

********************************************************************************
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 count(*) from  big_emp where deptno = 10 <== 실행된 SQL


call     count       cpu    elapsed       disk      query    current        rows <== query + current = logical read
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         12          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         12          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 65 



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

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

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        2      0.00       0.00          0         12          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         12          0           1

Misses in library cache during parse: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0

    1  user  SQL statements in session.
    0  internal SQL statements in session.
    1  SQL statements in session.
********************************************************************************
Trace file: /Users/oracle/admin/ORCL/udump/orcl_ora_328.trc
Trace file compatibility: 10.01.00
Sort options: default

       1  session in tracefile.
       1  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       1  SQL statements in trace file.
       1  unique SQL statements in trace file.
      28  lines in trace file.
       0  elapsed seconds in trace file.