怎么捕获自己session执行的sql，进行深入跟踪

 
alter session set timed_statistics = true; --可选

 

alter session set max_dump_file_size = unlimited; --可选，防止dump目录放不下

 

-- To enable the trace event 10046
alter session set events '10046 trace name context forever, level X';

/* where X = (1,4,8,12) */

1 = Statistics

4 = Statistics , Bind Variable Values

8 = Statistics, wait Event Informantion

12 = Statistics , Bind Variable Values, Wait Event Information

 

--设置TRACEFILE_IDENTIFIER参数值，让trace文件包括MyTrace字符，这样找起来方便（这个方法不错！），从8.1.7开始就已经有了
alter session set tracefile_identifier = 'MyTrace';  -- 可选

 

-- Run your SQL script or program to trace wait event information--

...你的sql...

 

--To turn off the tracing
alter session set events '10046 trace name context off';

 

OK, 找到该转储文件localdb_ora_4456_mytrace.trc

 

 

再使用tkprof：
C:/> tkprof D:/oracle/product/10.2.0/admin/localdb/udump/localdb_ora_4456_mytrace.trc D:/output.txt

使结果更加可读。如图：

 

 

读Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning 有感~

另外还有10053事件，比10046要详细，参考下eygle的文章，

DBA手记:DBA诊断利器 - Event 10046和 10053

http://www.eygle.com/archives/2011/02/dba_event_10046_10053.html