oradebug 10046

一、对当前的session使用oradebug命令：
SQL> conn / as sysdba
Connected.

SQL> oradebug setmypid
Statement processed.

或者对其他session做跟踪

--连接到一个外部服务进程，且pid=v$process.pid
oradebug setorapid pid
--连接到一个外部服务进程，且spid=v$process.spid
oradebug setospid spid

二、激活10046事件
SQL> oradebug event 10046 trace name context forever,level 12;

Statement processed.

三、执行目标sql语句
SQL> select count(1) from v$session where Seconds_in_wait/60>=1 and STATE='WAITING' and sid in (select HOLDING_SESSION from DBA_WAITERS);

四、查看对应的trc文件及目录
SQL> oradebug tracefile_name;
/oracle/DB/11.2.0/admin/PROD/diag/rdbms/prod/PROD1/trace/PROD1_ora_109226.trc

五、关闭当前的10046跟踪事件
SQL> oradebug event 10046 trace name context off;
Statement processed.

六、使用tkprof来格式化
$ tkprof /oracle/DB/11.2.0/admin/PROD/diag/rdbms/prod/PROD1/trace/PROD1_ora_109226.trc /tmp/PROD1_ora_109226.txt

七、查看得到的/tmp/PROD1_ora_109226.txt文件