在追尋某個sql的效能之前我們需要先從v$session檢視中找出我們需要檢視的sql的SID,serial#

SQL> select sid,serial#,username from v$session where username is not null;

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
       139         61 SUNXIN
       146         11 SYS

而後我們需要啟動dbms_system.set_sql_trace_in_session包，因為它比較耗用系統資源，使用之後一定要記得關閉

SQL> exec dbms_system.set_sql_trace_in_session(139,61,true);

PL/SQL 過程已成功完成。
而後我們在 61使用者中執行我們的sql 語句，一段時候後關閉追蹤

SQL> exec dbms_system.set_sql_trace_in_session(139,61,false);

PL/SQL 過程已成功完成。

找出我們的udump的位置

SQL> SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest'
2 ;

VALUE
--------------------------------------------------------------------------------

D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP

由於TRACE 檔案我們直接讀取不方便，我們使用ORACLE 自帶的工具tkprof來解析我們的sql trace

C:\Documents and Settings\Administrator>tkprof D:\oracle\product\10.2.0\admin\or
cl\udump\orcl_ora_504.trc
output = D:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_504.txt

TKPROF: Release 10.2.0.1.0 - Production on 星期五 3月 23 10:25:26 2012

看下我們解析後得trace檔案

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

select *
from
scott.dept

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        4      0.03       0.02          0          0          0           0
Execute      4      0.00       0.00          0          0          0           0
Fetch        8      0.00       0.00          6         32          0          16
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total       16      0.03       0.03          6         32          0          16

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

Rows Row Source Operation
------- ---------------------------------------------------
4 TABLE ACCESS FULL DEPT (cr=8 pr=0 pw=0 time=90 us)

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

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        4      0.03       0.02          0          0          0           0
Execute      4      0.00       0.00          0          0          0           0
Fetch        8      0.00       0.00          6         32          0          16
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total       16      0.03       0.03          6         32          0          16

Misses in library cache during parse: 1

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse       16      0.00       0.01          0          0          0           0
Execute    113      0.04       0.09          0          0          0           0
Fetch      121      0.01       0.20         19        344          0         768
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total      250      0.06       0.30         19        344          0         768

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

4 user SQL statements in session.
113 internal SQL statements in session.
117 SQL statements in session.
********************************************************************************
Trace file: D:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_504.trc
Trace file compatibility: 10.01.00
Sort options: default

       1 session in tracefile.
       4 user SQL statements in trace file.
     113 internal SQL statements in trace file.
     117 SQL statements in trace file.
      16 unique SQL statements in trace file.
    1044 lines in trace file.
       3 elapsed seconds in trace file.

使用dbms_system 追蹤sql

相關文章