ORACLE 10046 Trace

10046 trace的跟蹤等級

10046是一個Oracle的內部事件(event)，通過設定這個事件可以得到Oracle內部執行系統解析、調用、等待、綁定變數等詳細的trace資訊，對於分析系統的效能有著非常重要的作用。

設定10046事件的不同層級能得到不同詳細程度的trace資訊，下面就列出各個不同層級的對應作用：

等級	二進位	作用
0	0000	無輸出
1	0001	輸出 ****,APPNAME（應用程式名稱）,PARSING IN CURSOR,PARSE ERROR（SQL解析）,EXEC（執行）,FETCH（擷取資料）,UNMAP,SORT UNMAP（排序，臨時段）,ERROR,STAT（執行計畫）,XCTEND（事務）等行
2	0011	與等級1完全一樣
4	0101	包括等級1的輸出，加上BIND行（綁定變數資訊）
8	1001	包括等級1的輸出，加上WAIT行（等待事件資訊）
12	1101	輸出等級1、等級4以及等級8的所有資訊

等級1的10046 trace被視為是普通的SQL Trace，而等級4、等級8以及等級12則被稱為Extended SQL Trace，Extended SQL Trace裡麵包括了最有用的WAIT資訊，因此在實際中也是用的最多的。

與SQL Trace相關的參數

在開啟10046時間的SQL Trace之前，要先設定好下面幾個參數。 timed_statistics 這個參數決定了是否收集與時間相關的統計資訊，如果這個參數為FALSE的話，那麼SQL Trace的結果基本沒有多大的用處，預設情況下這個參數設定為TRUE。 max_dump_file_size dump檔案的大小，也就是決定是否限制SQL Trace檔案的大小，在一個很忙的系統上面做SQL Trace的話可能會產生很多的資訊，因此最好在會話層級將這個參數設定成unlimited。 tracefile_identifier 給Trace檔案設定識別字串，這是個非常有用的參數，設定一個易讀的字串能更快的找到Trace檔案。

要在當前會話修改上述參數很簡單，只要使用下面的命令即可：

1 2 3

ALTER SESSION SET timed_statistics= true ALTER SESSION SET max_dump_file_size=unlimited ALTER SESSION SET tracefile_identifier='my_trace_session

當然，這些參數可以在系統層級修改的，也可以載入init檔案中或是spfile中，讓系統啟動時自動做全域設定。

要是在系統運行時動態修改別的會話的這些參數就需要藉助DBMS_SYSTEM這個包了，設定方法如下：

1 2 3 4 5 6 7 8 9

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' , 2147483647 )

注意，Oracle並沒有提供一個set_string_param_in_session的函數在dbms_system包中，因此tracefile_identifier是無法在別的會話中修改的（至少我到現在沒有找到一個可以設定的方法）。 10046 Trace啟動方法 開啟當前會話的10046 Trace 使用sql_trace參數

sql_trace應該是簡單快捷的開啟Trace的方法了，不過通過sql_trace只能開啟層級為1的Trace，而無法開啟其他更進階的Trace。

1 2 3 4 5

-- 開啟Trace ALTER SESSION SET sql_trace= true ;   -- 關閉Trace ALTER SESSION SET sql_trace= false ;

使用set event開啟Trace

使用set event開啟10046事件Trace是最常用的了。

1 2 3 4 5

-- 開啟層級為12的Trace，level後面的數字設定了Trace的層級 ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'   -- 關閉Trace，任何層級 ALTER SESSION SET EVENTS '10046 trace name context off'

開啟其他會話的10046 Trace 使用登陸觸發器開啟Trace

我們可以通過編寫登陸觸發器來開啟10046 Trace，使用這種方法開啟Trace的代碼和開啟當前會話的是一樣的，不同的就是這些開啟代碼是包含在一個after logon觸發器裡面的。

1 2 3 4 5 6 7 8 9 10

-- 代碼來自《Optimazing Oracle Performance》 P116 CREATE OR REPLACE TRIGGER trace_test_user AFTER LOGON ON DATABASE BEGIN    IF USER LIKE '%\_test' ESCAPE '\' THEN      EXECUTE IMMEDIATE ' ALTER SESSION SET timed_statistics= true ';      EXECUTE IMMEDIATE ' ALTER SESSION SET max_dump_file_size=unlimited ';      EXECUTE IMMEDIATE ' ALTER SESSION SET EVENTS '' 10046 trace name context forever, level 8 '' ';    END IF; END ; /

使用oradebug工具

使用oradebug工具必須要知道所要處理的進程的OS進程PID，OS PID可以使用下面的語句得到：

1 2 3 4 5 6

SELECT S.USERNAME,      P.SPID OS_PROCESS_ID,      P.PID ORACLE_PROCESS_ID FROM V$SESSION S, V$PROCESS P WHERE S.PADDR = P.ADDR      AND S.USERNAME = UPPER ( '&USER_NAME' );

得到PID之後就可以使用oradebug工具了，注意需要使用sysdba登陸到資料庫：

1 2 3 4 5 6 7 8

-- 假設9999為會話的OS PID oradebug setospid 9999; -- 設定Trace檔案大小 oradebug unlimit; -- 開啟層級為12的Trace oradebug event 10046 trace name context forever , level 12; --關閉trace Oradebug event 10046 trace name context off ;

使用DBMS_SYSTEM包

DBMS_SYSTEM包提供了兩個開啟10046 Trace的方法，一個是使用SET_SQL_TRACE_IN_SESSION過程，不過使用這個過程的效果和sql_trace是一樣的：

1 2 3 4 5

-- 開啟Trace EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(:sid, :serial#, true );   -- 關閉Trace EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(:sid, :serial#, false );

另一個方法是使用SET_EV過程，當然這個過程不僅僅用來設定10046事件，還能設定所有的其他的事件，使用方法為：

1 2 3 4 5 6 7 8

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

使用例子：

1 2 3 4 5

-- 開啟level 12的Trace EXEC SYS.DBMS_SYSTEM.SET_EV(:sid, :serial, 10046, 12, '' );   -- 關閉Trace EXEC SYS.DBMS_SYSTEM.SET_EV(:sid, :serial, 10046, 0, '' );

使用DBMS_SUPPORT包

DBMS_SUPPORT包預設情況下並沒有包含在資料庫中，需要通過運行$ORACLE_HOME/rdbms/admin/dbmssupp.sql安裝之後才能使用。

可以DBMS_SUPPORT包來開啟自身進程或者是別的進程的Trace。

開啟自身進程：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

-- 使用方法 DESC DBMS_SUPPORT   PROCEDURE START_TRACE   Argument Name                  Type                    In / Out Default ?   ------------------------------ ----------------------- ------ --------   WAITS                          BOOLEAN                 IN     DEFAULT   BINDS                          BOOLEAN                 IN     DEFAULT PROCEDURE STOP_TRACE   -- 執行個體 -- 開啟層級為12的Trace EXEC SYS.DBMS_SUPPORT.START_TRACE( true , true );   -- 關閉Tr