??? Oracle 10046是一個(gè)Oracle內(nèi)部事件。最常用的是在Session級(jí)別設(shè)置sql_trace(alter session set sql_trace=true)即是開啟了級(jí)別為1
的10046調(diào)試事件。當(dāng)設(shè)置了10046事件之后，Oracle 將產(chǎn)生一個(gè)dump文件。通過得到的dump文件進(jìn)行進(jìn)一步分析，可以得到Oracle 內(nèi)部執(zhí)行系
統(tǒng)解析、調(diào)用、等待、綁定變量等詳細(xì)的trace信息，對(duì)于分析系統(tǒng)的性能有著舉足輕重的作用。

?

一、10046事件的相關(guān)參數(shù)
??該事件需要設(shè)置一些參數(shù)以控制dump文件的輸出：??
??TIMED_STATISTICS
????用于控制計(jì)時(shí)信息，可以設(shè)定為true和false。當(dāng)設(shè)定為true時(shí)，計(jì)時(shí)信息將會(huì)被添加到trace文件中。
??
??MAX_DUMP_FILE_SIZE
????用于控制trace文件的最大尺寸。當(dāng)使用10046事件時(shí)，建議將該參數(shù)設(shè)定為unlimited。
??
??USER_DUMP_DEST
????用于設(shè)定trace文件寫入到哪個(gè)文件目錄
??
??STATISTICS_LEVEL
????用于控制統(tǒng)計(jì)信息的收集度。此參數(shù)有3個(gè)選擇，baisc，typical，all。
????basic:僅收集滿足trace所需的最基本的信息，象Timed statistics，Object level statistics，以及一些advisory會(huì)被忽略。
????typical:此為缺省值。此設(shè)置將在basic的基礎(chǔ)上增加一些額外的統(tǒng)計(jì)信息，象操作系統(tǒng)耗用時(shí)間的統(tǒng)計(jì)信息，執(zhí)行計(jì)劃的統(tǒng)計(jì)信息都會(huì)被收集
????all:當(dāng)設(shè)置為all時(shí)，所有與該session相關(guān)的信息全部會(huì)被收集。
??
??TRACEFILE_IDENTIFIER
????用于設(shè)置識(shí)別Trace文件的字符串，便于更快捷的找到生成的Trace文件。

??以上參數(shù)可以基于系統(tǒng)級(jí)別以及會(huì)話級(jí)別進(jìn)行修改。
????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級(jí)別
????
??為特定的session動(dòng)態(tài)設(shè)定trace相關(guān)參數(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)試事件的等級(jí)
??10046調(diào)試事件可以分為多個(gè)不同的等級(jí)，不同的等級(jí)輸出不同的trace信息。
??等級(jí)?? 作用
??0????? 禁止調(diào)試事件
??1????? 調(diào)試事件處于激活狀態(tài)。針對(duì)每個(gè)被處理的數(shù)據(jù)庫(kù)調(diào)用，輸出SQL語句,APPNAME（應(yīng)用程序名）,PARSING IN CURSOR,PARSE ERROR（SQL解析）
??????,EXEC（執(zhí)行）,FETCH（獲取數(shù)據(jù)）,UNMAP,SORT UNMAP（排序，臨時(shí)段）,ERROR,STAT（執(zhí)行計(jì)劃）,XCTEND（事務(wù)）等行。
??4????? 包括等級(jí)1的輸出，加上BIND行（綁定變量信息）
??8????? 包括等級(jí)1的輸出，加上WAIT行（等待事件信息）。對(duì)于處理過程中的每個(gè)等待，提供如下信息：等待時(shí)間的名字，持續(xù)時(shí)間，以及一些額外
???????? 的參數(shù)，可表明所等待的資源。
??12???? 輸出等級(jí)4以及等級(jí)8的所有信息

?

三、激活10046調(diào)試事件

1.針對(duì)當(dāng)前會(huì)話啟用10046事件(常用set event) -- 開啟當(dāng)前會(huì)話級(jí)別為12的Trace，level后面的數(shù)字用于設(shè)定Trace的級(jí)別，取值為0,1,4,8,12 ALTER SESSION SET EVENTS '10046 trace name context forever, level 12' -- 關(guān)閉當(dāng)前會(huì)話任意級(jí)別的Trace ALTER SESSION SET EVENTS '10046 trace name context off' 2. 針對(duì)非當(dāng)前會(huì)話啟用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 -->使用包來實(shí)現(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ā)器來實(shí)現(xiàn)其它用戶登錄之后即開啟10046事件 -->為避免針對(duì)所有用戶開啟跟蹤，建議創(chuàng)建一個(gè)角色，假定為sql_trace，然后將該角色授予需要進(jìn)行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 多個(gè)不同的會(huì)話 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)自帶的包來設(shè)置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來清除當(dāng)前session的client identifier scott@CNMMBO> exec dbms_session.CLEAR_IDENTIFIER; PL/SQL procedure successfully completed. -->也可以使用下面的trigger來生成client identifier -->下面針對(duì)sql_trace角色下的用戶登錄之后設(shè)置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.基于組件級(jí)別與數(shù)據(jù)庫(kù)級(jí)別的跟蹤(略) 參考： http://docs.oracle.com/cd/E11882_01/appdev.112/e10577/d_monitor.htm

?

四、演示10046調(diào)試事件?

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級(jí)跟蹤 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.演示應(yīng)用程序上的跟蹤(使用client identifier) -->根據(jù)前面的描述創(chuàng)建角色并授予需要跟蹤的用戶以及添加client identifier的觸發(fā)器，并啟動(dòng)應(yīng)用程序 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; -->將角色授予用戶。注：觸發(fā)器logon_set_identifier已經(jīng)創(chuàng)建 Grant succeeded. -->啟動(dòng)應(yīng)用程序后，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)用應(yīng)用程序的report 模塊并生成report，此時(shí)可以看到應(yīng)用程序多產(chǎn)生了一個(gè)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)生后關(guā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 -->此時(shí)的udump目錄下產(chǎn)生了兩個(gè)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 = '.,' -->可以看到對(duì)于由應(yīng)用程序衍生出來的另外一個(gè)session 也被trace，而衍生的session正是由SQL語句產(chǎn)生的。

五、更多參考 ??????

啟用用戶進(jìn)程跟蹤

父游標(biāo)、子游標(biāo)及共享游標(biāo)

綁定變量及其優(yōu)缺點(diǎn)

dbms_xplan之display_cursor函數(shù)的使用

dbms_xplan之display函數(shù)的使用

執(zhí)行計(jì)劃中各字段各模塊描述

使用 EXPLAIN PLAN 獲取SQL語句執(zhí)行計(jì)劃

啟用 AUTOTRACE 功能

函數(shù)使得索引列失效

Oracle 綁定變量窺探

?

啟用 Oracle 10046 調(diào)試事件