會話級SQL跟蹤

1 Sql_trace

Sql_trace 是oracle 提供的會話級跟蹤工具。啟動會話跟蹤後oracle會將指定會話執行的所有SQL語句記錄在一個trace檔案中，這個trace檔案記錄是SQL語句真實的執行計劃和各項效能資料。

1.1 Top命令找到最消耗資源的oracle程式

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

22139 oracle 15 0 2336 1120 804 R 0.7 0.1 0:00.07 top

3223 root 15 0 38156 3944 3224 S 0.3 0.4 0:04.68 vmtoolsd

21826 oracle 18 0 573m 18m 17m S 0.3 1.8 0:06.97 oracle

1 root 15 0 2072 632 544 S 0.0 0.1 0:01.09 init

2 root RT -5 0 0 0 S 0.0 0.0 0:06.43 migration/0

1.2 檢視sql_trace狀態並得到引數 sid 和serial#，這兩個值是會話的唯一標識

SQL> select s.sid,s.SERIAL#,sql_trace from v$process p,v$session s where p.addr=s.paddr and p.sPID=21826 ;

SID SERIAL# SQL_TRACE

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

145 195 DISABLED

1.3 開啟跟蹤指定會話

SQL> exec dbms_system.set_sql_trace_in_session(145,195,true);

PL/SQL procedure successfully completed.

SQL> select s.sid,s.SERIAL#,sql_trace from v$process p,v$session s where p.addr=s.paddr and p.sPID=21826 ;

SID SERIAL# SQL_TRACE

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

145 195 ENABLED

1.4 檢視生成trace檔案路徑

Trace檔案一般放在user_dump_dest路徑下

SQL> show parameter dump

NAME TYPE VALUE

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

background_core_dump string partial

background_dump_dest string /u01/app/oracle/diag/rdbms/enm

oedu/ENMOEDU/trace

core_dump_dest string /u01/app/oracle/diag/rdbms/enm

oedu/ENMOEDU/cdump

max_dump_file_size string unlimited

shadow_core_dump string partial

user_dump_dest string /u01/app/oracle/diag/rdbms/enm

oedu/ENMOEDU/trace

1.5 查詢生成的trace檔案

使用oradebug 按pid 找到trace檔案

SQL> oradebug setospid 21826;

Oracle pid: 35, Unix process pid: 21826, image: oracle@ENMOEDU (TNS V1-V3)

SQL> oradebug tracefile_name;

/u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace/ENMOEDU_ora_21826.trc

為了方便查詢也可以為生成的trace檔案增加識別符號

SQL> alter session set tracefile_identifier=MY;

Session altered.

[oracle@ENMOEDU trace]$ ls -trl *_MY.trc

-rw-r----- 1 oracle oinstall 31965 Jan 19 02:11 ENMOEDU_ora_21826_MY.trc

1.6 關閉會話跟蹤

SQL> exec dbms_system.set_sql_trace_in_session(145,195,false);

PL/SQL procedure successfully completed.

SQL> select s.sid,s.SERIAL#,sql_trace from v$process p,v$session s where p.addr=s.paddr and p.sPID=21826;

SID SERIAL# SQL_TRACE

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

145 195 DISABLED

1.7 使用tkprof格式化trace檔案

[oracle@ENMOEDU trace]$ tkprof ENMOEDU_ora_21826_MY.trc ENMOEDU_ora_21826_MY.trc.txt

TKPROF: Release 11.2.0.3.0 - Development on Sun Jan 19 02:18:03 2014

附：啟用或關閉跟蹤當前會話

SQL> alter session set sql_trace=true|false;

SQL> exec dbms_session.set_sql_trace(true|false);

2 10046診斷事件

1046診斷事件是sql_trace功能的擴充套件，主要增加了繫結變數和等待事件的跟蹤。

2.1 跟蹤級別

1046事件可設定的四個級別:

Level 1－與sql_trace記錄內容相同

Level 4－level 1 + 繫結變數資訊

Level 8－level 1 + 等待事件資訊

Level 12－level 1 + Level 4 + Level 8

2.2 開啟會話跟蹤

Exec dbms_monitor.session_trace_enable(sid,serial#,waits,binds);

Exec dbms_monitor.session_trace_disable(sid,serial#,waits,binds);

waits表示是否記錄等待事件,值為 true 或 false;

binds表示是否記錄繫結變數,值為 true 或 false;

Exec sys.dbms_system.set_ev(sid,serial#,10046,12,””);

附：設定當前會話跟蹤事件

Alter session set events ‘10046 trace name context forever,level 12’;

Alter session set events ‘10046 trace name context off;

3 小結

Explain 和 auto trace 可以對單條SQL語句分析。而Sql_trace 與 10046事件會將使用者session中全部SQL執行的整個過程輸出到一個trace檔案中，我們可以讀這個trace 檔案來了解使用者會話中所有SQL語句真實的執行計劃和各項效能資訊，方便我們全面直觀分析系統整體執行狀況。

DBA_建瑾

2014.1.19

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26175573/viewspace-1097539/，如需轉載，請註明出處，否則將追究法律責任。

會話級SQL跟蹤

1 Sql_trace

1.1 Top命令找到最消耗資源的oracle程式

1.2 檢視sql_trace狀態並得到引數 sid 和serial#，這兩個值是會話的唯一標識

1.3 開啟跟蹤指定會話

1.4 檢視生成trace檔案路徑

1.5 查詢生成的trace檔案

1.6 關閉會話跟蹤

1.7 使用tkprof格式化trace檔案

2 10046診斷事件

2.1 跟蹤級別

2.2 開啟會話跟蹤

3 小結

相關文章