10046的多种使用方式

10046跟踪

select userenv(‘sid’) from dual;

select paddr from v$session where sid=16

这个paddr的值就是下面addr的值，通过这个值查询会话的pid和spid。

select pid,spid from v$process where addr in (select paddr from v$session where sid in (select userenv('sid') from dual));

开启10046 跟踪

oradebug setorapid 64;

oradebug unlimit;

oradebug event 10046 trace name context forever,level 12;

测试SQL  

delete from emp where employee_id=205;

select * from emp  where employee_id=205;

oradebug event 10046 trace name context off;

oradebug tracefile_name;

格式化：

tkprof /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_18136.trc test.txt sys=no;

SQL> select pid,spid from v$process where addr='000000006DABB858';

       PID SPID

---------- ------------------------

28 18136

这里使用pid 跟踪，启动会话跟踪

SQL> oradebug setorapid 28

Oracle pid: 28, Unix process pid: 18136, image: oracle@ora12c (TNS V1-V3)

SQL> oradebug unlimit;

Statement processed.

SQL> oradebug event 10046 trace name context forever,level 12

Statement processed.

SQL> oradebug tracefile_name;

/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_18136.trc

执行那条导致中断的SQL语句。 发生故障后（也就是异常退出），执行下面语句结束10046跟踪。

获得跟踪文件位置

SQL> oradebug event 10046 trace name context off;

Statement processed.

SQL> host

使用tkprof工具格式化后，分析

[oracle@ora12c ~]$ tkprof /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_18136.trc test.txt sys=no;

下面是通过会话spid跟踪，步骤一样

通过操作系统进程跟踪某个会话。根据会话的sid

select p.pid,p.spid from v$process p,v$session s where p.addr=s.paddr and s.sid=31;

       PID SPID

---------- ------------------------

28 28837

SQL> oradebug setospid 28837

Oracle pid: 28, Unix process pid: 28837, image: oracle@ora12c (TNS V1-V3)

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.

SQL> oradebug tracefile_name;

/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_28837.trc

10046 debug跟踪

SQL> oradebug setmypid

Statement processed.

// 激活 10046 事件

SQL> oradebug event 10046 trace name context forever,level 12;

Statement processed.

SQL> your sql

// 在当前 session 关闭 10046 事件

SQL> oradebug event 10046 trace name context off;

Statement processed.

// 使用 oradebug tracefile_name 可以直接看到生成的 trace 文件的位置

SQL> oradebug tracefile_name;

g:\app\davidd\diag\rdbms\david\david\trace\david_ora_2176.trc