启用 Oracle 10046 调试事件

10046事件调试

启用 Oracle 10046 调试事件 .
分类： Oracle 性能优化 2012-02-24 14:26 1779人阅读 评论(2) 收藏 举报
oraclesessionstatisticssqlmoduleparsing Oracle 10046是一个Oracle内部事件。最常用的是在Session级别设置sql_trace(alter session set sql_trace=true)即是开启了级别为1
的10046调试事件。当设置了10046事件之后，Oracle 将产生一个dump文件。通过得到的dump文件进行进一步分析，可以得到Oracle 内部执行系
统解析、调用、等待、绑定变量等详细的trace信息，对于分析系统的性能有着举足轻重的作用。



一、10046事件的相关参数
该事件需要设置一些参数以控制dump文件的输出：
TIMED_STATISTICS
用于控制计时信息，可以设定为true和false。当设定为true时，计时信息将会被添加到trace文件中。

MAX_DUMP_FILE_SIZE
用于控制trace文件的最大尺寸。当使用10046事件时，建议将该参数设定为unlimited。

USER_DUMP_DEST
用于设定trace文件写入到哪个文件目录

STATISTICS_LEVEL
用于控制统计信息的收集度。此参数有3个选择，baisc，typical，all。
basic:仅收集满足trace所需的最基本的信息，象Timed statistics，Object level statistics，以及一些advisory会被忽略。
typical:此为缺省值。此设置将在basic的基础上增加一些额外的统计信息，象操作系统耗用时间的统计信息，执行计划的统计信息都会被收集
all:当设置为all时，所有与该session相关的信息全部会被收集。

TRACEFILE_IDENTIFIER
用于设置识别Trace文件的字符串，便于更快捷的找到生成的Trace文件。

以上参数可以基于系统级别以及会话级别进行修改。
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动态设定trace相关参数，借助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调试事件的等级
10046调试事件可以分为多个不同的等级，不同的等级输出不同的trace信息。
等级 作用
0 禁止调试事件
1 调试事件处于激活状态。针对每个被处理的数据库调用，输出SQL语句,APPNAME（应用程序名）,PARSING IN CURSOR,PARSE ERROR（SQL解析）
,EXEC（执行）,FETCH（获取数

10046事件调试

据）,UNMAP,SORT UNMAP（排序，临时段）,ERROR,STAT（执行计划）,XCTEND（事务）等行。
4 包括等级1的输出，加上BIND行（绑定变量信息）
8 包括等级1的输出，加上WAIT行（等待事件信息）。对于处理过程中的每个等待，提供如下信息：等待时间的名字，持续时间，以及一些额外
的参数，可表明所等待的资源。
12 输出等级4以及等级8的所有信息



三、激活10046调试事件


[sql] view plaincopyprint?
01.1.针对当前会话启用10046事件(常用set event)
02. -- 开启当前会话级别为12的Trace，level后面的数字用于设定Trace的级别，取值为0,1,4,8,12
03. ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'
04. -- 关闭当前会话任意级别的Trace
05. ALTER SESSION SET EVENTS '10046 trace name context off'
06.
07.2. 针对非当前会话启用10046事件
08. sys@MMBOTST> desc dbms_system
09. PROCEDURE SET_EV
10. Argument Name Type In/Out Default?
11. ------------------------------ ----------------------- ------ --------
12. SI BINARY_INTEGER IN
13. SE BINARY_INTEGER IN
14. EV BINARY_INTEGER IN
15. LE BINARY_INTEGER IN
16. NM VARCHAR2 IN
17.
18

10046事件调试

. -->使用包来实现其他session的10046事件
19. sys.DBMS_SYSTEM.set_ev( &input_sid
20. , &input_serial
21. , 10046
22. , &input_level
23. , NULL );
24. sys.DBMS_SYSTEM.set_ev( &input_sid
25. , &input_serial
26. , 10046
27. , 0
28. , NULL );
29.
30. -->使用触发器来实现其它用户登录之后即开启10046事件
31. -->为避免针对所有用户开启跟踪，建议创建一个角色，假定为sql_trace，然后将该角色授予需要进行trace的用户(create role sql_trace)
32. CREATE OR REPLACE TRIGGER enable_sql_trace
33. AFTER LOGON
34. ON DATABASE
35. BEGIN
36. IF ( DBMS_SESSION.is_role_enabled( 'SQL_TRACE' ) ) THEN
37. EXECUTE IMMEDIATE 'ALTER SESSION SET timed_statistics=true';
38.

10046事件调试

39. EXECUTE IMMEDIATE 'ALTER SESSION SET max_dump_file_size=unlimited';
40.
41. EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 8'' ';
42. END IF;
43. END;
44. /
45.
46.3.根据client identifier来trace 多个不同的会话
47. sys.DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE ( client_id=>'&input_client_identifier', waits=>TRUE, binds=>FALSE );
48. sys.DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE ( client_id=>'&input_client_identifier' );
49.
50. 使用系统自带的包来设置client identifier
51. BEGIN
52. dbms_session.set_identifier(client_id=>'robinson.cheng');
53. dbms_application_info.set_client_info(client_info=>'Linux x86_64');
54. dbms_application_info.set_module(module_name=>'test.module',
55. action_name=>'test session');
56. END;
57. /
58.
59. scott@CNMMBO> SELECT client_identifier,

10046事件调试

60. 2 client_info,
61. 3 module AS module_name,
62. 4 action AS action_name
63. 5 FROM v$session
64. 6 WHERE sid = sys_context('userenv','sid');
65.
66. CLIENT_IDENTIFIER CLIENT_INFO MODULE_NAME ACTION_NAME
67. ------------------------- ------------- ----------------- -------------------------
68. robinson.cheng Linux x86_64 test.module test session
69.
70. -->使用下面的procedure来清除当前session的client identifier
71. scott@CNMMBO> exec dbms_session.CLEAR_IDENTIFIER;
72.
73. PL/SQL procedure successfully completed.
74.
75. -->也可以使用下面的trigger来生成client identifier
76. -->下面针对sql_trace角色下的用户登录之后设置client identifier
77. CREATE OR REPLACE TRIGGER logon_set_identifier
78. AFTER LOGON
79. ON DATABASE
80. DECLARE
81. UID VARCHAR2( 64 );

10046事件调试

82. BEGIN
83. IF ( DBMS_SESSION.is_role_enabled( 'SQL_TRACE' ) ) THEN
84. SELECT ora_login_user
85. || ':'
86. || SYS_CONTEXT( 'USERENV', 'OS_USER' )
87. INTO UID
88. FROM dual;
89.
90. DBMS_SESSION.set_identifier( UID );
91. ELSE
92. NULL;
93. END IF;
94. END logon_set_identifier;
1.针对当前会话启用10046事件(常用set event)
-- 开启当前会话级别为12的Trace，level后面的数字用于设定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

10046事件调试

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

-->使用包来实现其他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 );

-->使用触发器来实现其它用户登录之后即开启10046事件
-->为避免针对所有用户开启跟踪，建议创建一个角色，假定为sql_tra

10046事件调试

ce，然后将该角色授予需要进行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.根据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' );

使用系统自带的包来设置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

10046事件调试

_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

10046事件调试

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.基于组件级别与数据库级别的跟踪(略) 参考：http://www.77cn.com.cn/cd/E11882_01/appdev.112/e10577/d_monitor.htm



四、演示10046调试事件

[sql] view plaincopyprint?
01.1.创建演示环境
02. CREATE TABLE sql_trace_test
03. (
04. id NUMBER

10046事件调试

05. , description VARCHAR2( 50 )
06. );
07.
08. EXEC DBMS_STATS.gather_table_stats('SCOTT', 'SQL_TRACE_TEST');
09.
10. CREATE OR REPLACE PROCEDURE populate_sql_trace_test( p_loops IN NUMBER ) AS
11. l_number NUMBER;
12. BEGIN
13. FOR i IN 1 .. p_loops
14. LOOP
15. INSERT INTO sql_trace_test( id, description )
16. VALUES ( i
17. , 'Description for '
18. || i );
19. END LOOP;
20.
21. SELECT COUNT( * ) INTO l_number FROM sql_trace_test;
22.
23. COMMIT;
24. DBMS_OUTPUT.put_line( l_number
25. || ' rows inserted.' );
26. END;

10046事件调试

27. /
28.
29. SHOW ERRORS
30.
31.2.使用10046事件开启8级跟踪
32. scott@CNMMBO> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
33.
34. Session altered.
35.
36. scott@CNMMBO> EXEC populate_sql_trace_test(p_loops => 10);
37. 10 rows inserted.
38.
39. PL/SQL procedure successfully completed.
40.
41. scott@CNMMBO> ALTER SESSION SET EVENTS '10046 trace name context off';
42.
43. Session altered.
44.
45. scott@CNMMBO> ho ls -hltr /u02/database/CNMMBO/udump/*example*
46. -rw-r----- 1 oracle oinstall 9.1K 2012-02-24 10:24 /u02/database/CNMMBO/udump/cnmmbo_ora_6667_trace_sql_example.trc
47.
48.3.使用tkprof工具格式化trace file
49. scott@CNMMBO> host

10046事件调试

50. oracle@SZDB:~/robinson/scripts/dba_scripts/custom/sql> cd /u02/database/CNMMBO/udump/
51. oracle@SZDB:/u02/database/CNMMBO/udump> tkprof cnmmbo_ora_6667_trace_sql_example.trc sql_example.txt \
52. > explain=scott/tiger sys=no sort=prsela,exeela,fchela
53.
54. TKPROF: Release 10.2.0.3.0 - Production on Fri Feb 24 10:33:16 2012
55.
56. Copyright (c) 1982, 2005, Oracle. All rights reserved.
57.
58. oracle@SZDB:/u02/database/CNMMBO/udump> more sql_example.txt
59.
60. TKPROF: Release 10.2.0.3.0 - Production on Fri Feb 24 10:33:16 2012
61.
62. Copyright (c) 1982, 2005, Oracle. All rights reserved.
63.
64. Trace file: cnmmbo_ora_6667_trace_sql_example.trc
65. Sort options: prsela exeela fchela
66. ********************************************************************************
67. count = number of times OCI procedure was executed
68. cpu = cpu time in seconds executing
69. elapsed = elapsed time in seconds executing
70. disk = number of physical reads of buffers from disk
71. query = number of buffers gotten for consistent read

10046事件调试

72. current = number of buffers gotten in current mode (usually for update)
73. rows = number of rows processed by the fetch or execute call
74. ********************************************************************************
75.
76. BEGIN populate_sql_trace_test(p_loops => 10); END;
77.
78. call count cpu elapsed disk query current rows
79. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
80. Parse 1 0.00 0.00 0 0 0 0
81. Execute 1 0.00 0.00 0 0 0 1
82. Fetch 0 0.00 0.00 0 0 0 0
83. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
84. total 2 0.01 0.00 0 0 0 1
85.
86. Misses in library cache during parse: 1
87. Optimizer mode: ALL_ROWS
88. Parsing user id: 66 (SCOTT)
89.
90. Elapsed times include waiting on following events:
91. Event waited on Times Max. Wait Total Waited
92. ---------------------------------------- Waited ---------- ------------
93. log file sync 1 0.09 0.09
94. SQL*Net message to client

10046事件调试

1 0.00 0.00
95. SQL*Net message from client 1 0.00 0.00
96. ********************************************************************************
97.
98. INSERT INTO SQL_TRACE_TEST( ID, DESCRIPTION )
99. VALUES
100. ( :B1 , 'Description for ' || :B1 )
101.
102. call count cpu elapsed disk query current rows
103. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
104. Parse 1 0.00 0.00 0 0 0 0
105. Execute 10 0.00 0.00 0 1 31 10
106. Fetch 0 0.00 0.00 0 0 0 0
107. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
108. total 11 0.00 0.00 0 1 31 10
109.
110. Misses in library cache during parse: 1
111. Misses in library cache during execute: 1
112. Optimizer mode: ALL_ROWS
113. Parsing user id: 66 (SCOTT) (recursive depth: 1)
114.
115. /**************************************************/
116. /* Author: Robinson Cheng */

10046事件调试

117. /* Blog: http://www.77cn.com.cn/robinson_0612 */
118. /* MSN: robinson_0612@http://www.77cn.com.cn */
119. /* QQ: 645746311 */
120. /**************************************************/
121.
122.4.演示应用程序上的跟踪(使用client identifier)
123. -->根据前面的描述创建角色并授予需要跟踪的用户以及添加client identifier的触发器，并启动应用程序
124. oracle@2go-devDB01uv:/u02/database/SYBO2/udump> ls -hltr -->移出不必要的trace文件以清除判断其产生的跟踪文件
125. total 8.5K
126. drwxr-xr-x 2 oracle oinstall 8.5K 2012-02-24 11:52 old
127.
128. oex_admin@SYBO2> create role sql_trace; -->创建角色
129.
130. Role created.
131.
132. goex_admin@SYBO2> grant sql_trace to goex_webuser; -->将角色授予用户。注：触发器logon_set_identifier已经创建
133.
134. Grant succeeded.
135.
136. -->启动应用程序后，session的信息如下
137. goex_admin@SYBO2> SELECT sid , serial#, username , client_identifier, client_info , module AS module_name
138. 2 FROM v$session WHERE username is not null;

10046事件调试

139.
140. SID SERIAL# USERNAME CLIENT_IDENTIFIER CLIENT_INFO MODULE_NAME
141. ---------- ---------- -------------------- ------------------------------ ------------- -----------------
142. 1068 6250 GOEX_WEBUSER GOEX_WEBUSER:Robinson.Cheng mBO.exe
143. 1073 7201 GOEX_ADMIN SQL*Plus
144. 1075 9115 GOEX_ADMIN SQL*Plus
145. 1086 60120 GOEX_ADMIN TOAD 10.5.0.41
146.
147. -->开启基于client identifier的trace
148. goex_admin@SYBO2> exec DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE('GOEX_WEBUSER:Robinson.Cheng',waits=>TRUE, binds=>FALSE );
149.
150. PL/SQL procedure successfully completed.
151.
152. -->调用应用程序的report 模块并生成report，此时可以看到应用程序多产生了一个session,其sid,serial分别为1078,34788
153. goex_admin@SYBO2> SELECT sid , serial#, username , client_identifier, client_info , module AS module_name
154. 2 FROM v$session WHERE username is not null;
155.
156. SID SERIAL# USERNAME CLIENT_IDENTIFIER CLIENT_INFO MODULE_NAME
157. ---------- ---------- -------------------- ------------------------------ ------------- -----------------
158. 1068 6250 GOEX_WEBUSER GOEX_WEBUSER:Robinson.Cheng mBO.exe
159. 1073 7201 GOEX_ADMIN SQL*Plus
160. 1075 9115 GOEX_ADMIN

10046事件调试

SQL*Plus
161. 1078 34788 GOEX_WEBUSER GOEX_WEBUSER:Robinson.Cheng mBO.exe
162. 1086 60120 GOEX_ADMIN TOAD 10.5.0.41
163.
164. -->report产生后关闭基于client identifier的trace
165. goex_admin@SYBO2> exec DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE('GOEX_WEBUSER:Robinson.Cheng');
166.
167. PL/SQL procedure successfully completed.
168.
169. oracle@2go-devDB01uv:/u02/database/SYBO2/udump> ls -hltr -->此时的udump目录下产生了两个trace文件
170. total 409K
171. drwxr-xr-x 2 oracle oinstall 8.5K 2012-02-24 11:52 old
172. -rw-r----- 1 oracle oinstall 4.9K 2012-02-24 11:56 sybo2_ora_6419.trc
173. -rw-r----- 1 oracle oinstall 391K 2012-02-24 11:56 sybo2_ora_6422.trc
174.
175. oracle@2go-devDB01uv:/u02/database/SYBO2/udump> cat sybo2_ora_6419.trc | grep SESSION
176. *** SESSION ID:(1068.6250) 2012-02-24 11:56:19.617
177. oracle@2go-devDB01uv:/u02/database/SYBO2/udump> cat sybo2_ora_6422.trc | grep SESSION
178. *** SESSION ID:(1078.34788) 2012-02-24 11:56:20.650
179. IF ( DBMS_SESSION.is_role_enabled( 'SQL_TRACE' ) ) THEN
180. DBMS_SESSION.set_identifier( UID );
181. ALTER SESSION SET NLS_CALENDAR = 'GREGORIAN' NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' NLS_NUMERIC_CHARACTERS = '.,'
182.

10046事件调试

183. -->可以看到对于由应用程序衍生出来的另外一个session 也被trace，而衍生的session正是由SQL语句产生的。

本文来源：https://www.bwwdw.com/article/euyj.html