1. 使用 AUTOTRACE 查看執(zhí)行計(jì)劃

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

??? set autotrace OFF

?

?

2. 啟用 sql_trace 跟蹤當(dāng)前 session

• 開(kāi)啟會(huì)話跟蹤：alter session set sql_trace=true;
• 關(guān)閉會(huì)話跟蹤：alter session set sql_trace=false;

?

3. 啟用 10046 事件跟蹤當(dāng)前 session

• 開(kāi)啟會(huì)話跟蹤：alter session set events '10046 trace name context forever, level 12';
• 關(guān)閉會(huì)話跟蹤：alter session set events '10046 trace name context off';
• 對(duì)跟蹤文件加標(biāo)識(shí)：alter session set tracefile_identifier='dragon';

SQL> host dir E:\ORACLE\PRODUCT\10.2.0\ADMIN\BYISDB\UDUMP
 驅(qū)動(dòng)器 E 中的卷是 DISK1_VOL3

 卷的序列號(hào)是 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 個(gè)文件        172,504 字節(jié)
               2 個(gè)目錄 22,060,634,112 可用字節(jié)

?

4. 啟用 10046 事件跟蹤全局 session

????這將會(huì)對(duì)整個(gè)系統(tǒng)的性能產(chǎn)生嚴(yán)重的影響，所以一般不建議開(kāi)啟。

• 開(kāi)啟會(huì)話跟蹤：alter system set events ‘10046 trace name context forever, level 12’;
• 關(guān)閉會(huì)話跟蹤：alter system set events ‘10046 trace name context off’;

5. 使用 Oracle 系統(tǒng)包 DBMS_SYSTEM.SET_EV 跟蹤指定 session

PROCEDURE SET_EV

參數(shù)名稱?????????????????????? 類型??????????????????? 輸入/輸出默認(rèn)值?

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

?SI???????????????????????????? BINARY_INTEGER????????? IN

?SE??????????????????????????? BINARY_INTEGER????????? IN

?EV??????????????????????????? BINARY_INTEGER??????????IN

?LE??????????????????????????? BINARY_INTEGER??????????IN

?NM?????????????????????????? VARCHAR2????????????????????IN

參數(shù)說(shuō)明：

SI-指定SESSION的SID;

SE-指定SESSION的SE;

EV-事件ID(如：10046);

LE-表示TRACE的級(jí)別;

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

?

• 開(kāi)啟會(huì)話跟蹤：SQL> exec dbms_system.set_ev(143, 112, 10046, 12, '');
• 關(guān)閉會(huì)話跟蹤：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

?

?

??

轉(zhuǎn)載于:https://www.cnblogs.com/oradragon/archive/2012/09/03/2669700.html