Oracle sql trace

標籤：

一.SQL_TRACE說明

1.1.在全域啟用    在參數檔案(pfile/spfile)中指定:sql_trace =true    1.2.在當前session級設定    啟用當前session的跟蹤:    alter session set sql_trace=true;    結束跟蹤:    alter session set sql_trace=false;    1.3.跟蹤其他使用者進程    跟蹤其他使用者的進程，可以通過Oracle提供的系統包DBMS_SYSTEM. SET_SQL_TRACE_IN_SESSION來完成.SET_SQL_TRACE_IN_SESSION過程式要提供三個參數:        SQL> desc dbms_system    ...    PROCEDURE SET_SQL_TRACE_IN_SESSION     Argument Name                     Type                    In/Out Default?     ------------------------------           -----------------------   ------ --------     SID                               NUMBER                  IN     SERIAL#                          NUMBER                  IN     SQL_TRACE                        BOOLEAN                 IN    ...        通過v$session我們可以獲得sid、serial#等資訊:    SQL> select sid,serial#,username         2     from v$session      3  where username is not null;               SID    SERIAL#  USERNAME    ---------- ---------- -------------             8       2041  SYS             9        437  SCOTT        啟用跟蹤:    exec dbms_system.set_sql_trace_in_session(9,437,true)        停止跟蹤:    exec dbms_system.set_sql_trace_in_session(9,437,false)

二.10046事件說明

10046事件是Oracle提供的內部事件，是對SQL_TRACE的增強.10046事件可以設定以下四個層級:1 - 啟用標準的SQL_TRACE功能,等價於sql_trace4 - Level 1 加上綁定值(bind values)8 - Level 1 + 等待事件跟蹤12 - Level 1 + Level 4 + Level 8類似sql_trace，10046事件可以在全域設定，也可以在session級設定。2.1.在全域設定    在參數檔案中增加:     event="10046 trace name context forever,level 12"         此設定對所有使用者的所有進程生效、包括後台進程.2.2 對當前session設定    通過alter session的方式修改，需要alter session的系統許可權:     執行跟蹤:    SQL> alter session set events ‘10046 trace name context forever‘;    SQL> alter session set events ‘10046 trace name context forever, level 8‘;        結束跟蹤:    SQL> alter session set events ‘10046 trace name context off‘;2.3 對其他使用者session設定    通過DBMS_SYSTEM.SET_EV系統包來實現:        SQL> desc dbms_system    ...    PROCEDURE SET_EV     Argument Name                  Type                    In/Out Default?     ------------------------------ ----------------------- ------ --------     SI                             BINARY_INTEGER          IN     SE                             BINARY_INTEGER          IN     EV                             BINARY_INTEGER          IN     LE                             BINARY_INTEGER          IN     NM                             VARCHAR2                IN        ...    其中的參數SI、SE來自v$session視圖，查詢獲得需要跟蹤的session資訊:    select sid,serial#,username     from v$session     where username is not null;        SID SERIAL# USERNAME    ---------- ---------- ------------------------------    8 2041 SYS    9 437 SCOTT    執行跟蹤:    SQL> exec dbms_system.set_ev(9,437,10046,8,‘SCOTT‘);        結束跟蹤:    SQL> exec dbms_system.set_ev(9,437,10046,0,‘SCITT‘);

 

三.與SQL Trace相關的參數

在開啟10046時間的SQL Trace之前，要先設定好下面幾個參數。timed_statistics是否收集與時間相關的統計資訊，如果這個參數為FALSE的話，那麼SQL Trace的結果基本沒有多大的用處，預設情況下這個參數設定為TRUE。max_dump_file_sizedump檔案的大小，在一個很忙的系統上面做SQL Trace的話可能會產生很多的資訊，因此最好在會話層級將這個參數設定成unlimited。3.1 在全域設定這些參數可以載入spfile中，讓系統啟動時自動做全域設定。3.2 對當前session設定        ALTER SESSION SET timed_statistics=trueALTER SESSION SET max_dump_file_size=unlimited        3.3 對其他使用者session設定SYS.DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION(:sid, :serial,‘timed_statistics‘, true)SYS.DBMS_SYSTEM.SET_INT_PARAM_IN_SESSION(:sid, :serial,‘max_dump_file_size‘, unlimited)    

 

四.擷取追蹤檔案

以上產生的追蹤檔案位於user_dump_dest目錄中，位置及檔案名稱可以通過以下SQL查詢獲得:                        select d.value||‘/‘||lower(rtrim(i.instance, chr(0)))||‘_ora_‘||p.spid||‘.trc‘ trace_file_namefrom ( select p.spid from sys.v$mystat m,sys.v$session s,sys.v$process p where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,     ( select t.instance from sys.v$thread  t,sys.v$parameter  v where v.name = ‘thread‘ and (v.value = 0 or t.thread# = to_number(v.value))) i,     ( select value from sys.v$parameter where name = ‘user_dump_dest‘) d; /

 

整理自互連網

Oracle sql trace