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