导航：首页 > 服务器 >

使用 oracle 10046 event

发表于：2026-11-02 作者：千家信息网编辑

千家信息网最后更新 2026年11月02日，描述遇到sql语句查询出错的问题.Yong Huang版提示做10046事件.对这个事件以前一直是模糊概念.想理清楚,所以写成这个文档.供以后使用.环境RHEL5.4 64 + 10.2.0.4说明1

千家信息网最后更新 2026年11月02日使用 oracle 10046 event

描述

遇到sql语句查询出错的问题.Yong Huang版提示做10046事件.对这个事件以前一直是模糊概念.想理清楚,所以写成这个文档.供以后使用.

环境

RHEL5.4 64 + 10.2.0.4

说明

10046 event 能干什么

目前知道这是一个系统性能分析事件,这个事件可以告诉oracle内核把相应session的详细时间信息输出到trace文件中.

10046 event level (不翻译了,水平有限,翻了半天还是觉得原文好)

You can think of the event 10046 "level" attribute associated with an Oracle session as a 4-bit flag whose bits have the following meanings:

Level		Function
Decimal	Binary	Function
1	0001	Emit statistics for parse, execute, fetch, commit, and rollback database calls (standard sql_trace)
2	0010	Unknown
3	0100	Emit values for SQL bind variables (also called "placeholders")
4	1000	Emit statistics for Oracle kernel internal function calls (also called "wait events") listed in v$event_name

For example, a level-12 trace combines the effects of level-4 and level-8 tracing. Strangely, activating any non-zero tracing level also activates level-1 tracing. Therefore, tracing at levels 4, 8, and 12 are exactly equivalent to tracing at levels 5, 9, and 13, respectively: all these levels include the standard sql_trace output.

trace 文件位置

11gR1 或 11gR1 以上版本

SQL> show parameter diagnostic_dest

11gR1以前版本

SQL> show parameter user_dump_dest

trace 文件命名规则

tracefile 命名规则 :_ora__.trc

其中pid为相应session所对应的OS PID,tracedid 跟session的TRACEFILE_IDENTIFIER参数相关,默认TRACEFILE_IDENTIFIER为null.

eg.

给当前session设置TRACEFILE_IDENTIFIER

ALTER SESSION SET TRACEFILE_IDENTIFIER='TOMS';
此处的"TOMS"即为trace file 命名规则中_ora__.trc的 tracedid.
可以设置TRACEFILE_IDENTIFIER参数的session里查询V$PROCESS.TRACEID查看tracefile_identifier的设置.

取消session标识将 tracefile_identifier置空即可.
alter session set tracefile_identifier='';

定位trace文件(10g版本测试通过)

1.自己当前session的trace file (需要有查询 v$mystat, v$session ,v$process 的权限)

Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONEselect d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||
p.spid || '.trc' 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;

2.以sys用户查找其他session的trace file (需要知道其他session sid)

Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONENormal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE

Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE

select d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||
p.spid || '.trc' trace_file_name
from (select p.spid
from v$session s, v$process p
where s.sid = &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;

会提示输入sid的数值.嘿嘿,就是把1给小小的改动了下.sid到v$session视图中查询.这里面的trace file名字是拼出来的.所以仅仅符合trace命名规则的_ora_.trc部分

步骤

session级的trace

1.10046 trace 自己的 session

  alter session set tracefile_identifier='10046'; 
  alter session set timed_statistics = true;
  alter session set statistics_level=all;
  alter session set max_dump_file_size = unlimited;
  --alter session set max_dump_file_size = 2147483647;
  alter session set events '10046 trace name context forever,level 12';

  -- Execute the queries or operations to be traced here --

  select * from dual;

  alter session set events '10046 trace name context off';
  exit;

2. dbms_system包 10046 trace 指定的session (Oracle release 8.1.6 and newer)

  conn / as sysdba
  exec sys.dbms_system.set_bool_param_in_session(sid,serial#,'timed_statistics',true);
  exec sys.dbms_system.set_int_param_in_session(sid,serial#,'max_dump_file_size',2147483647);
  exec sys.dbms_system.set_ev(sid,serial#,10046,12,'');

  -- Execute the queries or operations to be traced here --

  select * from dual;
  exec sys.dbms_system.set_ev(sid,serial#,10046,0,'');
  exit;

3.oradebug

查询出session的SID,SERIAL#,PID和SPID(OS PID)

  conn / as sysdba
  select p.PID,p.SPID,s.SID,s.SERIAL#
  from v$process p,v$session s
  where s.paddr = p.addr
  and s.sid = &SESSION_ID;

通过OS的PID生成10046 trace

  connect / as sysdba
  oradebug setospid 9834
  oradebug unlimit
  oradebug event 10046 trace name context forever,level 12
  oradebug event 10046 trace name context off

记得把9834换成自己查询出来的OS PID

通过数据库的PID生成10046 trace

  connect / as sysdba
  oradebug setorapid 9834
  oradebug unlimit
  oradebug event 10046 trace name context forever,level 12
  oradebug event 10046 trace name context off

记得把9834换成自己查询出来的OS PID

instance级的10046 trace

Note: Please be cautious when setting system wide, as this will impact performance due to every session being traced.

修改参数文件events参数实现instance级的10046
1.只对参数修改后新连接的session生效.

  alter system set events '10046 trace name context forever,level 12';
  alter system set events '10046 trace name context off';

2.重启数据库后生效

  alter system set events '10046 trace name context forever,level 12' scope=spfile;

关闭也用 alter system set events '10046 trace name context off';

通过 Logon Trigger 做10046 trace

有些情况我们需要trace一个登录用户.这时可以通过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 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;
  /

注意登录用户必须拥有alter session权限才能成功trace.