本文详细介绍了Oracle 10046事件的使用方法及其在不同层级的设置方式，包括如何通过trace文件分析Oracle内部执行过程，提高数据库性能。
Oracle 10046是一个Oracle内部事件。最常用的是在Session级别设置sql_trace(alter session set sql_trace=true)即是开启了级别为1的10046调试事件。当设置了10046事件之后，Oracle 将产生一个dump文件。通过得到的dump文件进行进一步分析，可以得到Oracle 内部执行系统解析、调用、等待、绑定变量等详细的trace信息，对于分析系统的性能有着举足轻重的作用。
10046事件可以设置以下四个级别:
1 - 启用标准的SQL_TRACE功能,等价于sql_trace,针对每个被处理的数据库调用，输出SQL语句,APPNAME(应用程序名),PARSING IN CURSOR,PARSE ERROR(SQL解析),EXEC(执行),FETCH(获取数据),UNMAP,SORT UNMAP(排序，临时段),ERROR,STAT(执行计划),XCTEND(事务)等行
4 - Level 1 加上绑定值(bind values)
8 - Level 1 + 等待事件跟踪,对于处理过程中的每个等待，提供如下信息：等待时间的名字，持续时间，以及一些额外的参数，可表明所等待的资源
12 - Level 1 + Level 4 + Level 8
类似sql_trace，10046事件可以在全局设置，也可以在session级设置(一般情况，只做SESSION级别的设置)。
在开始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文件。
为特定的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事件
1、开启10046事件前，我们先确认一下TRACE文件路径及文件名:
路径: show parameter user_dump_dest
文件名：
SELECT d.VALUE || '/' || lower(rtrim(i.INSTANCE, CHR (0))) ||'_ora_'|| p.spid ||'.trc' as "trace_file_name"
FROM (SELECT p.spid
FROM v$mystat m, v$session s, v$process p
WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
(SELECT t.INSTANCE
FROM v$thread t, v$parameter v
WHERE v.NAME = 'thread' AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
(SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d;
也可以通过设置会话的
查看dbms_system包下过程的参数tracefile_identifier来确认TRACE文件名
ALTER SESSION SET tracefile_identifier='trace_sql_example'; -->仅session级别
还可以对会话设置一些参数
ALTER SESSION/SYSTEM SET timed_statistics=true;
ALTER SESSION/SYSTEM SET max_dump_file_size=unlimited;
2、开启10046事件
-- 开启当前会话级别为12的Trace，level后面的数字用于设定Trace的级别，取值为1,4,8,12，当LEVEL=1时，等同于SQL TRACE
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
-- 关闭当前会话任意级别的Trace
ALTER SESSION SET EVENTS '10046 trace name context off';
二、针对其它会话启用10046事件
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
--使用包来实现其他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事件
-->使用触发器来实现其它用户登录之后即开启10046事件
-->为避免针对所有用户开启跟踪，建议创建一个角色，假定为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;
/
四、根据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,先看下面一组测试:
SQL> column client_identifier format A20
SQL> column client_info format A20
SQL> column module_name format A30
SQL> column action_name format A20
SQL> SELECT client_identifier, client_info, module AS module_name, action AS action_name
2 FROM v$session
3 WHERE sid = sys_context('userenv','sid');
CLIENT_IDENTIFIER CLIENT_INFO MODULE_NAME ACTION_NAME
-------------------- -------------------- ------------------------------ --------------------
sqlplus@repsrv (TNS V1-V3)
SQL> BEGIN
2 dbms_session.set_identifier(client_id=>'client_id.test');
3 dbms_application_info.set_client_info(client_info=>'hpux');
4 dbms_application_info.set_module(module_name=>'test.module',
5 action_name=>'test.session');
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> SELECT client_identifier, client_info, module AS module_name, action AS action_name
2 FROM v$session
3 WHERE sid = sys_context('userenv','sid');
CLIENT_IDENTIFIER CLIENT_INFO MODULE_NAME ACTION_NAME
-------------------- -------------------- ------------------------------ --------------------
client_id.test hpux test.module test.session
-->使用下面的procedure来清除当前session的client identifier
SQL> exec dbms_session.CLEAR_IDENTIFIER;
PL/SQL procedure successfully completed.
SQL> SELECT client_identifier, client_info, module AS module_name, action AS action_name
2 FROM v$session
3 WHERE sid = sys_context('userenv','sid');
CLIENT_IDENTIFIER CLIENT_INFO MODULE_NAME ACTION_NAME
-------------------- -------------------- ------------------------------ --------------------
hpux test.module test.session
讲到这里，我觉得对于TRACE方法五花八门，但可根据具体情况选择更合适的TRACE方式。我这里也只是把自己比较清楚的作了一下说明，说到比较难跟踪的，其实就可以通过设置client_identifier来解决应用程序上的跟踪。
TKPROF工具
关于如何跟踪及查找产生的TRACE文件已经清楚，这时，当我们拿到TRACE文件时，我们会发现trace文件的可读性比较差，通常我们使用tkprof 工具来处理trace文件，Tkprof 工具是Oracle 自带的一个工具，用于处理原始的trace文件，它的作用主要是合并汇总trace文件中的一些项，规范化文件的格式，使文件更具有可读性。
注意：tkprof 工具只能用在处理SQL_TRACE和10046事件产生的trace，其他事件如10053不能处理。
Tkprof 是系统级别的，直接在系统下执行即可。
$ tkprof repdb_ora_9999.trc repdb_ora_9999.txt
TKPROF: Release 10.2.0.5.0 - Production on Fri Dec 14 16:44:40 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
$ ls repdb_ora_9999.txt对于具体用法，我就不一一介绍了，可以直接输入命令查看其帮助文档。另外，对于参数说明及TRACE文件的分析，我将作另外分析。
该文章是我结合参考资料，COPY了大部分资料，只是整理出来自己的一些想法，并整理脚本，方便以后工作，感谢提相关资料的博主。
(如果本人对以上材料有什么新进展，会及时更新，也希望大家能多提意见。)
参考资料：
http://www.cnblogs.com/zlja/archive/2012/02/24/2449269.html
http://blog.youkuaiyun.com/tianlesoftware/article/details/5857023
扫一扫
3865
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
