10053事件可以将CBO解析SQL，做出执行计划的过程一步一步演示给我们看，让我们可以直接窥视到这里究竟发生了什么事情。

SQL> create table t3 as select rownum x from dba_objects;

Table created.

SQL> create index ind_t3 on t3(x);

Index created.

SQL> exec dbms_stats.gather_table_stats(user, 'T3',cascade=>true);

PL/SQL procedure successfully completed.

SQL> create table t4 as select x, 'T1' name from t3 where x < 10000;

Table created.

SQL> alter session set events '10053 trace name context forever, level 1';

Session altered.

SQL> explain plan for select t4.* from t3, t4 where t3.x < 100 and t3.x = t4.x;

Explained.

SQL> alter session set events '10053 trace name context off';

Session altered.

SQL>

10053时间会在生成一个trace文件。但是这个文件不能被tkprof工具处理，tkprof只能处理sql trace或者10046事件产生的trace文件。可以用以下SQL来查看当前session的trace文件的位置：

SELECT 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;

其他的session可以用:

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 = ''

AND s. serial# = ''

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, SERIAL#号填充到SQL条件里。

因为生成的trace文件很冗长，这里先不列出内容。

ITPub博客

Oracle 10053事件