使用dbms_system 追踪sql

在追寻某个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

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

看下我们解析后得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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21416913/viewspace-719343/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21416913/viewspace-719343/