10046事件及其用法介绍
10046事件在诊断数据库性能问题时，是一个诊断利器。
本文就10046事件功能及在不同场景的诊断做相应说明。
1:10046事件说明
10046事件是一个特定的事件号。通过该事件设置，可以追踪sql语句的执行情况。10046事件与SQL_TRACE=TRUE的功能一致。10046事件可以根据指定不同的事件级别，输出对应的信息到trace文件中。
2:10046事件级别说明
level 1：标准的SQL_TRACE功能（默认级别）；
level 4：level 1级别功能加上对绑定值的追踪；
level 8：level 1级别功能加上等待事件的追踪；对LATCH的等待追踪较有用。此外，还可以识别全表扫描及索引扫描等；
注意：10046的事件等级可以通过给出的级别进行数值累加，以实现多种功能。譬如，既需要追踪绑定值，又需要追踪等待事件，那么可以将level设置为12（level 4+level 8）。
此外，自11g开始，增加了以下几个level：
level 16：每次执行生成行转储统计信息
level 32：不转储执行统计信息
11.2.0.2以上，又增加了以下level：
level 64：行自适应转储。譬如执行超过1分钟sql，需要获取执行这类高成本的sql相关信息。
最常用的是LEVEL 12。
3:10046事件设置语法
1. 会话级别：
alter session set events '10046 trace name context forever';（默认）
alter session set events '10046 trace name context forever, level 12';
alter session set events '10046 trace name context off';
2. 11g语法
alter session set events 'sql_trace';
alter session set events 'sql_trace wait=true';
alter session set events 'sql_trace off'';
3. 初始化参数设置
event="10046 trace name context forever,level 4"
注意：初始化参数设置event时，数据库所有会话都会进行追踪。
4:Trace文件的位置
1. 11g R1之后：
从11gR1开始，Oracle引入了新的诊断结构，以参数DIAGNOSTIC_DEST控制存放trace文件与core文件的路径。
可以用以下命令，获取DIAGNOSTIC_DEST的位置：
SQL> show parameter diagnostic_dest
2. 11gR1之前
如果是用户进程，10046 trace文件会被生成在user_dump_dest下；如果是后台进程，trace文件会被生成在background_dump_dest下。
下面的命令可以显示user_dump_dest:
SQL> show parameter user_dump_dest
5:会话级别设置10046的示例
注意：会话级别10046跟踪，适用于sql语句可以在新的session创建后再运行。具体步骤如下：
1.创建新的session；
SQL> conn scott/******;
Connected.
2.在session级别中，收集10046trace
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
3.执行需要跟踪的SQL
SQL> select empno,ename from emp;
4.退出
SQL> alter session set events '10046 trace name context off';
Session altered.
注意：也可以用exit退出当前会话来结束sql的trace。
6:跟踪其它会话
通常，在对数据库进行性能优化诊断时，都是需要对其它会话上的sql进行跟踪。而通过alter session的方式，是无法对其它会话执行的sql进行trace的。这里介绍另外一种10046trace方法。
1. 首先，用找到需要被跟踪的session
例如，在SQL*Plus里，找出目标session的OS的进程ID(spid):
select p.PID,p.SPID,s.SID
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = &SESSION_ID
/
SPID 是操作系统的进程标识符（os pid）
PID 是Oracle的进程标识符(ora pid)
如果不知道session的ID, 那么可以使用类似下面的SQL语句来帮助你找到它：
column line format a79
set heading off
select 'ospid: ' || p.spid || ' # ''' ||s.sid||','||s.serial#||''' '||
s.osuser || ' ' ||s.machine ||' '||s.username ||' '||s.program line
from v$session s , v$process p
where p.addr = s.paddr
and s.username <> ' ';
2.一旦找到OS PID，就可以用以下命令初始化跟踪
假设需要被跟踪的OSPID是9834。
以sysdba的身份登录到SQL*Plus并执行下面的命令：
connect / as sysdba
oradebug setospid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
注意：需要把例子中的'9834' 替换成真实的os pid。此外，也可以通过oradebug使用 'setorapid'命令连接到一个session。
下面的例中， 使用PID（Oracle进程标识符）(而不是SPID), oradebug命令将被改为：
connect / as sysdba
oradebug setorapid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
记得把例子中的9834替换成真实的ora pid。
2.跟踪过程完成以后，关闭oradebug跟踪：
oradebug event 10046 trace name context off
7:实例层的追踪
注意：在实例层设置跟踪需要非常小心，这是因为整体性能会由于所有session都被跟踪而受到影响。
1.实例级别的参数设置
这个设置将会跟踪在这个参数设置“以后”创建的每个session。已经存在的session不会被跟踪。系统层的10046跟踪适用于当我们知道问题session会出现，但是不能预先识别它的时候。在这种情况下，可以打开系统层跟踪一小段时间，当问题被重现以后立即将其关闭，然后从已经生成的trace中查找需要的信息。
用以下命令打开系统层的跟踪：
alter system set events '10046 trace name context forever,level 12';
用以下命令关闭在所有session中的10046跟踪：
alter system set events '10046 trace name context off';
2.初始化参数的设置
设置以下参数并重新启动实例后，实例上所有的session都会打开跟踪。
event="10046 trace name context forever,level 12"
移除这个参数并且重启实例， 或者使用下面的alter system命令可以关闭跟踪。
alter system set events '10046 trace name context off';
3.通过logon trigger设置跟踪
有的时候当需要跟踪某个特定用户的操作时，可以使用logon trigger来打开跟踪。下面是一个例子：
CREATE OR REPLACE TRIGGER SYS.set_trace
AFTER LOGON ON DATABASE
WHEN (USER like '&USERNAME')
DECLARE
lcommand varchar(200);
BEGIN
EXECUTE IMMEDIATE 'alter session set tracefile_identifier=''From_Trigger''';
EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
END set_trace;
/
为了能打开跟踪session, 执行trigger的用户需要被显式地授予'alter session' 权限. 例如：
grant alter session to <USERNAME> ;
