會話級SQL跟蹤

羽化殘虹發表於2014-03-01
會話SQL

會話級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

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

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

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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章