Oracle 數(shù)據(jù)庫跟蹤 SQL 的幾種方法

印度阿三17 2019-07-15

展開全文

原文鏈接：http://www.cnblogs.com/oradragon/archive/2012/09/03/2669700.html

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

??? 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
 驅(qū)動器 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 字節(jié)
               2 個目錄 22,060,634,112 可用字節(jié)

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

????這將會對整個系統(tǒng)的性能產(chǎn)生嚴重的影響，所以一般不建議開啟。

開啟會話跟蹤：alter system set events ‘10046 trace name context forever, level 12’;
關閉會話跟蹤：alter system set events ‘10046 trace name context off’;

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

PROCEDURE SET_EV

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

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

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

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

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

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

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

參數(shù)說明：

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

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

來源：https://www./content-2-329301.html