1.10046事件会话级别
适用场景:这种方法适用于SQL语句可以在新的session创建后再运行
1.1.设置生成的trace文件标志
alter session set tracefile_identifier='10046';
1.2.开启跟踪
alter session set events '10046 trace name context forever,level 12';
1.3.执行要跟踪的SQL语句
select * from scott.dept;
1.4.关闭跟踪
alter session set events '10046 trace name context off';
1.5.查看产生的TRACE路径
select tracefile from v$process where addr=(select paddr from v$session where sid=(select distinct sid from v$mystat));
1.6.格式化trace文件
tkprof /oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_18297_10046.trc /tmp/10046.trc
2.10046事件进程级别跟踪
适用场景:如果需要跟踪一个已经存在session,可以用oradebug连接到session上,并发起10046 trace
2.1.查找要跟踪的session信息
select *from v$session where username='HR';
SID,SERIAL#,USERNAME
38 323 HR
2.2.根据session信息找到对应的进程号SPID
SELECT p.PID, p.SPID, s.SID
FROM v$process p, v$session s
WHERE s.paddr = p.addr AND s.sid = 38;
PID SPID SID
28 19057 38
2.3.开启进程追踪
SQL> oradebug setospid 19057
Oracle pid: 28, Unix process pid: 19057, image: oracle@lxf01
SQL> oradebug unlimit
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.
SQL> oradebug event 10046 trace name context off
Statement processed.
2.4.执行相应的SQL查询
2.5.关闭oradebug跟踪
SQL>oradebug event 10046 trace name context off
2.6.查看追踪文件并分析
SQL> select tracefile from v$process where addr=(select paddr from v$session where sid=38);