使用 oracle 10046 event

最新推荐文章于 2024-08-06 18:34:57 发布

cnk45946

最新推荐文章于 2024-08-06 18:34:57 发布

阅读量88

点赞数

文章标签：数据库

描述

遇到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

_ora__.trctracedid.

定位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-NONE Normal 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.