Oracle 数据库跟踪 SQL 的几种方法

1. 使用 AUTOTRACE 查看执行计划

set autotrace ON | ON EXPLAIN | ON STATISTICS | TRACEONLY | TRACEONLY EXPLAIN

set autotrace OFF

Autotrace Setting	Result
SET AUTOTRACE OFF	No AUTOTRACE report is generated. This is the default.
SET AUTOTRACE ON EXPLAIN	The AUTOTRACE report shows only the optimizer execution path.
SET AUTOTRACE ON STATISTICS	The AUTOTRACE report shows only the SQL statement execution statistics.
SET AUTOTRACE ON	The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
SET AUTOTRACE TRACEONLY	Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any. If STATISTICS is enabled, query data is still fetched, but not printed.

2. 启用 sql_trace 跟踪当前 session

• 开启会话跟踪：alter session set sql_trace=true;
• 关闭会话跟踪：alter session set sql_trace=false;

3. 启用 10046 事件跟踪当前 session

• 开启会话跟踪：alter session set events '10046 trace name context forever, level 12';
• 关闭会话跟踪：alter session set events '10046 trace name context off';
• 对跟踪文件加标识：alter session set tracefile_identifier='dragon';

SQL> host dir E:\ORACLE\PRODUCT\10.2.0\ADMIN\BYISDB\UDUMP\

 驱动器 E 中的卷是 DISK1_VOL3

 卷的序列号是 609E-62D9

 E:\ORACLE\PRODUCT\10.2.0\ADMIN\BYISDB\UDUMP 的目录 
2012-07-19  17:58    <DIR>          .
2012-07-19  17:58    <DIR>          ..
2012-07-19  17:58             3,057 byisdb_ora_704.trc
2012-07-19  17:58           169,447 byisdb_ora_704_dragon.trc
               2 个文件        172,504 字节
               2 个目录 22,060,634,112 可用字节

4. 启用 10046 事件跟踪全局 session

这将会对整个系统的性能产生严重的影响，所以一般不建议开启。

• 开启会话跟踪：alter system set events ‘10046 trace name context forever, level 12’;
• 关闭会话跟踪：alter system set events ‘10046 trace name context off’;

5. 使用 Oracle 系统包 DBMS_SYSTEM.SET_EV 跟踪指定 session

PROCEDURE SET_EV

参数名称 类型 输入/输出默认值?

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

SI BINARY_INTEGER IN

SE BINARY_INTEGER IN

EV BINARY_INTEGER IN

LE BINARY_INTEGER IN

NM VARCHAR2 IN

参数说明：

SI-指定SESSION的SID;

SE-指定SESSION的SE;

EV-事件ID(如：10046);

LE-表示TRACE的级别;

NM-指定SESSION的username;

SQL> select userenv('sid') sid from dual;
       SID
----------
       143

SQL> select sid, serial#, username from v$session where sid=143;
       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
       143        112 UNA_HR

• 开启会话跟踪：SQL> exec dbms_system.set_ev(143, 112, 10046, 12, '');
• 关闭会话跟踪：SQL> exec dbms_system.set_ev(143, 112, 10046, 0, '');

6. 获取跟踪文件

SQL> select pr.value || '\' || i.instance_name || '_ora_' || to_char(ps.spid) 
|| '.trc' "trace file name" from v$session s, v$process ps, v$parameter pr, v$instance i 
where s.paddr = ps.addr and s.sid = userenv('sid') and pr.name = 'user_dump_dest';

trace file name
--------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ADMIN\BYISDB\UDUMP\byisdb_ora_372.trc

7. 使用 TKPROF 工具格式化

tkprof tracefile outputfile [options]

E:\oracle\product\10.2.0\admin\byisdb\udump>tkprof byisdb_ora_704.trc 10046.txt sys=no sort=prsela, exeela, fchela