Oracle 10046是一個Oracle內(nèi)部事件，
啟用Oracle 10046調(diào)試事件
。最常用的是在Session級別設置sql_trace(alter session set sql_trace=true)即是開啟了級別為1的10046調(diào)試事件。當設置了10046事件之后，Oracle 將產(chǎn)生一個dump文件。通過得到的dump文件進行進一步分析，可以得到Oracle 內(nèi)部執(zhí)行系
統(tǒng)解析、調(diào)用、等待、綁定變量等詳細的trace信息，對于分析系統(tǒng)的性能有著舉足輕重的作用。
一、10046事件的相關參數(shù)
該事件需要設置一些參數(shù)以控制dump文件的輸出：
TIMED_STATISTICS
用于控制計時信息，可以設定為true和false。當設定為true時，計時信息將會被添加到trace文件中。
MAX_DUMP_FILE_SIZE
用于控制trace文件的最大尺寸。當使用10046事件時，建議將該參數(shù)設定為unlimited。
USER_DUMP_DEST
用于設定trace文件寫入到哪個文件目錄
STATISTICS_LEVEL
用于控制統(tǒng)計信息的收集度。此參數(shù)有3個選擇，baisc，typical，all。
basic:僅收集滿足trace所需的最基本的信息，象Timed statistics，Object level statistics，以及一些advisory會被忽略。
typical:此為缺省值。此設置將在basic的基礎上增加一些額外的統(tǒng)計信息，象操作系統(tǒng)耗用時間的統(tǒng)計信息，執(zhí)行計劃的統(tǒng)計信息都會被收集
all:當設置為all時，所有與該session相關的信息全部會被收集。
TRACEFILE_IDENTIFIER
用于設置識別Trace文件的字符串，便于更快捷的找到生成的Trace文件。
以上參數(shù)可以基于系統(tǒng)級別以及會話級別進行修改。
ALTER SESSION/SYSTEM SET timed_statistics=true
ALTER SESSION/SYSTEM SET max_dump_file_size=unlimited
ALTER SESSION SET tracefile_identifier='trace_sql_example' -->僅session級別
為特定的session動態(tài)設定trace相關參數(shù)，借助DBMS_SYSTEM包
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 );
二、10046調(diào)試事件的等級
10046調(diào)試事件可以分為多個不同的等級，不同的等級輸出不同的trace信息。
等級 作用
0 禁止調(diào)試事件
1 調(diào)試事件處于激活狀態(tài)。針對每個被處理的數(shù)據(jù)庫調(diào)用，輸出SQL語句,APPNAME（應用程序名）,PARSING IN CURSOR,PARSE ERROR（SQL解析）
,EXEC（執(zhí)行）,FETCH（獲取數(shù)據(jù)）,UNMAP,SORT UNMAP（排序，臨時段）,ERROR,STAT（執(zhí)行計劃）,XCTEND（事務）等行。
4 包括等級1的輸出，加上BIND行（綁定變量信息）
8 包括等級1的輸出，加上WAIT行（等待事件信息）。對于處理過程中的每個等待，提供如下信息：等待時間的名字，持續(xù)時間，以及一些額外
的參數(shù)，可表明所等待的資源。
12 輸出等級4以及等級8的所有信息
三、激活10046調(diào)試事件
[sql] view plaincopyprint?
1.針對當前會話啟用10046事件(常用set event)
-- 開啟當前會話級別為12的Trace，level后面的數(shù)字用于設定Trace的級別，取值為0,1,4,8,12
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'
-- 關閉當前會話任意級別的Trace
ALTER SESSION SET EVENTS '10046 trace name context off'
2. 針對非當前會話啟用10046事件
sys@MMBOTST> 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
-->使用包來實現(xiàn)其他session的10046事件
sys.DBMS_SYSTEM.set_ev( &input_sid
, &input_serial
, 10046
, &input_level
, NULL );
sys.DBMS_SYSTEM.set_ev( &input_sid
, &input_serial
, 10046
, 0
, NULL );
-->使用觸發(fā)器來實現(xiàn)其它用戶登錄之后即開啟10046事件
-->為避免針對所有用戶開啟跟蹤，建議創(chuàng)建一個角色，假定為sql_trace，然后將該角色授予需要進行trace的用戶(create role sql_trace)
CREATE OR REPLACE TRIGGER enable_sql_trace
AFTER LOGON
ON DATABASE
BEGIN
IF ( DBMS_SESSION.is_role_enabled( 'SQL_TRACE' ) ) 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;
/
3.根據(jù)client identifier來trace 多個不同的會話
sys.DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE ( client_id=>'&input_client_identifier', waits=>TRUE, binds=>FALSE );
sys.DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE ( client_id=>'&input_client_identifier' );
使用系統(tǒng)自帶的包來設置client identifier
BEGIN
dbms_session.set_identifier(client_id=>'robinson.cheng');
dbms_application_info.set_client_info(client_info=>'Linux x86_64');
dbms_application_info.set_module(module_name=>'test.module',
action_name=>'test session');
END;
/
scott@CNMMBO> SELECT client_identifier,
2 client_info,
3 module AS module_name,
4 action AS action_name
5 FROM v$session
6 WHERE sid = sys_context('userenv','sid');
CLIENT_IDENTIFIER CLIENT_INFO MODULE_NAME ACTION_NAME
------------------------- ------------- ----------------- -------------------------
robinson.cheng Linux x86_64 test.module test session
-->使用下面的procedure來清除當前session的client identifier
scott@CNMMBO> exec dbms_session.CLEAR_IDENTIFIER;
PL/SQL procedure successfully completed.
-->也可以使用下面的trigger來生成client identifier
-->下面針對sql_trace角色下的用戶登錄之后設置client identifier
CREATE OR REPLACE TRIGGER logon_set_identifier
AFTER LOGON
ON DATABASE
DECLARE
UID VARCHAR2( 64 );
BEGIN
IF ( DBMS_SESSION.is_role_enabled( 'SQL_TRACE' ) ) THEN
SELECT ora_login_user
|| ':'
|| SYS_CONTEXT( 'USERENV', 'OS_USER' )
INTO UID
FROM dual;
DBMS_SESSION.set_identifier( UID );
ELSE
NULL;
END IF;
END logon_set_identifier;
4.基于組件級別與數(shù)據(jù)庫級別的跟蹤(略) 參考：http://docs.oracle.com/cd/E11882_01/appdev.112/e10577/d_monitor.htm
四、演示10046調(diào)試事件
[sql] view plaincopyprint?
1.創(chuàng)建演示環(huán)境
CREATE TABLE sql_trace_test
(
id NUMBER
, description VARCHAR2( 50 )
);
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'SQL_TRACE_TEST');
CREATE OR REPLACE PROCEDURE populate_sql_trace_test( p_loops IN NUMBER ) AS
l_number NUMBER;
BEGIN
FOR i IN 1 .. p_loops
LOOP
INSERT INTO sql_trace_test( id, description )
VALUES ( i
, 'Description for '
|| i );
END LOOP;
SELECT COUNT( * ) INTO l_number FROM sql_trace_test;
COMMIT;
DBMS_OUTPUT.put_line( l_number
|| ' rows inserted.' );
END;
/
SHOW ERRORS
2.使用10046事件開啟8級跟蹤
scott@CNMMBO> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
Session altered.
scott@CNMMBO> EXEC populate_sql_trace_test(p_loops => 10);
10 rows inserted.
PL/SQL procedure successfully completed.
scott@CNMMBO> ALTER SESSION SET EVENTS '10046 trace name context off';
Session altered.
scott@CNMMBO> ho ls -hltr /u02/database/CNMMBO/udump/*example*
-rw-r----- 1 oracle oinstall 9.1K 2012-02-24 10:24 /u02/database/CNMMBO/udump/cnmmbo_ora_6667_trace_sql_example.trc
3.使用tkprof工具格式化trace file
scott@CNMMBO> host
oracle@SZDB:~/robinson/scripts/dba_scripts/custom/sql> cd /u02/database/CNMMBO/udump/
oracle@SZDB:/u02/database/CNMMBO/udump> tkprof cnmmbo_ora_6667_trace_sql_example.trc sql_example.txt \
> explain=scott/tiger sys=no sort=prsela,exeela,fchela
TKPROF: Release 10.2.0.3.0 - Production on Fri Feb 24 10:33:16 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
oracle@SZDB:/u02/database/CNMMBO/udump> more sql_example.txt
TKPROF: Release 10.2.0.3.0 - Production on Fri Feb 24 10:33:16 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Trace file: cnmmbo_ora_6667_trace_sql_example.trc
Sort options: prsela exeela fchela
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
BEGIN populate_sql_trace_test(p_loops => 10); END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.00 0 0 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 66 (SCOTT)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
log file sync 1 0.09 0.09
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
INSERT INTO SQL_TRACE_TEST( ID, DESCRIPTION )
VALUES
( :B1 , 'Description for ' || :B1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10 0.00 0.00 0 1 31 10
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.00 0.00 0 1 31 10
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 66 (SCOTT) (recursive depth: 1)
/**************************************************/
/* Author: Robinson Cheng */
/* Blog: http://blog.csdn.net/robinson_0612 */
/* MSN: robinson_0612@hotmail.com */
/* QQ: 645746311 */
/**************************************************/
4.演示應用程序上的跟蹤(使用client identifier)
-->根據(jù)前面的描述創(chuàng)建角色并授予需要跟蹤的用戶以及添加client identifier的觸發(fā)器，并啟動應用程序
oracle@2go-devDB01uv:/u02/database/SYBO2/udump> ls -hltr -->移出不必要的trace文件以清除判斷其產(chǎn)生的跟蹤文件
total 8.5K
drwxr-xr-x 2 oracle oinstall 8.5K 2012-02-24 11:52 old
oex_admin@SYBO2> create role sql_trace; -->創(chuàng)建角色
Role created.
goex_admin@SYBO2> grant sql_trace to goex_webuser; -->將角色授予用戶，
電腦資料
《啟用Oracle 10046調(diào)試事件》(http://www.stanzs.com)。注：觸發(fā)器logon_set_identifier已經(jīng)創(chuàng)建Grant succeeded.
-->啟動應用程序后，session的信息如下
goex_admin@SYBO2> SELECT sid , serial#, username , client_identifier, client_info , module AS module_name
2 FROM v$session WHERE username is not null;
SID SERIAL# USERNAME CLIENT_IDENTIFIER CLIENT_INFO MODULE_NAME
---------- ---------- -------------------- ------------------------------ ------------- -----------------
1068 6250 GOEX_WEBUSER GOEX_WEBUSER:Robinson.Cheng mBO.exe
1073 7201 GOEX_ADMIN SQL*Plus
1075 9115 GOEX_ADMIN SQL*Plus
1086 60120 GOEX_ADMIN TOAD 10.5.0.41
-->開啟基于client identifier的trace
goex_admin@SYBO2> exec DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE('GOEX_WEBUSER:Robinson.Cheng',waits=>TRUE, binds=>FALSE );
PL/SQL procedure successfully completed.
-->調(diào)用應用程序的report 模塊并生成report，此時可以看到應用程序多產(chǎn)生了一個session,其sid,serial分別為1078,34788
goex_admin@SYBO2> SELECT sid , serial#, username , client_identifier, client_info , module AS module_name
2 FROM v$session WHERE username is not null;
SID SERIAL# USERNAME CLIENT_IDENTIFIER CLIENT_INFO MODULE_NAME
---------- ---------- -------------------- ------------------------------ ------------- -----------------
1068 6250 GOEX_WEBUSER GOEX_WEBUSER:Robinson.Cheng mBO.exe
1073 7201 GOEX_ADMIN SQL*Plus
1075 9115 GOEX_ADMIN SQL*Plus
1078 34788 GOEX_WEBUSER GOEX_WEBUSER:Robinson.Cheng mBO.exe
1086 60120 GOEX_ADMIN TOAD 10.5.0.41
-->report產(chǎn)生后關閉基于client identifier的trace
goex_admin@SYBO2> exec DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE('GOEX_WEBUSER:Robinson.Cheng');
PL/SQL procedure successfully completed.
oracle@2go-devDB01uv:/u02/database/SYBO2/udump> ls -hltr -->此時的udump目錄下產(chǎn)生了兩個trace文件
total 409K
drwxr-xr-x 2 oracle oinstall 8.5K 2012-02-24 11:52 old
-rw-r----- 1 oracle oinstall 4.9K 2012-02-24 11:56 sybo2_ora_6419.trc
-rw-r----- 1 oracle oinstall 391K 2012-02-24 11:56 sybo2_ora_6422.trc
oracle@2go-devDB01uv:/u02/database/SYBO2/udump> cat sybo2_ora_6419.trc | grep SESSION
*** SESSION ID:(1068.6250) 2012-02-24 11:56:19.617
oracle@2go-devDB01uv:/u02/database/SYBO2/udump> cat sybo2_ora_6422.trc | grep SESSION
*** SESSION ID:(1078.34788) 2012-02-24 11:56:20.650
IF ( DBMS_SESSION.is_role_enabled( 'SQL_TRACE' ) ) THEN
DBMS_SESSION.set_identifier( UID );
ALTER SESSION SET NLS_CALENDAR = 'GREGORIAN' NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' NLS_NUMERIC_CHARACTERS = '.,'
-->可以看到對于由應用程序衍生出來的另外一個session 也被trace，而衍生的session正是由SQL語句產(chǎn)生的。
更多參考
啟用用戶進程跟蹤
父游標、子游標及共享游標
綁定變量及其優(yōu)缺點
dbms_xplan之display函數(shù)的使用
dbms_xplan之display_cursor函數(shù)的使用
使用EXPLAIN PLAN獲取SQL語句執(zhí)行計劃
執(zhí)行計劃中各字段各模塊描述
啟用AUTOTRACE功能
函數(shù)使得索引列失效
作者 一沙彌的世界