10053 event used for CBO Tracing

What is the CBO doing, how it comes up with an execution plan? This is where the 10053 trace event comes into play. The 10053 trace shows us HOW the CBO came to its decision (the final execution plan).


 10053 trace for "GOOD" and "BAD" execution:  

alter session set timed_statistics = true; 
alter session set statistics_level=all; 
alter session set max_dump_file_size = unlimited; 
alter session set events '10053 trace name context forever, level 1'; 
-- /*Run SQL here*/ ------ 
alter session set events '10053 trace name context off'; 


There is a new method to obtain an Optimizer (10053) trace for any sql statement that is already in the cache, without actually executing it and also enabling 'events infrastructure'. With 11gR2 onwards, we can either use the DBMS_SQLDIAG.DUMP_TRACE package procedure or use new diagnostic events infrastructure to obtain the 10053 trace for a given sql_id


Demonstration 1:

1. Find out the sql_id for the given statement:

SELECT sql_id,child_number FROM v$sql WHERE sql_text LIKE '%SQL TEXT%';

2. Input the sql_id to the DBMS_SQLDIAG.DUMP_TRACE package procedure:

execute DBMS_SQLDIAG.DUMP_TRACE(p_sql_id=>'sql_id',  p_child_number=>0, 
p_component=>'Compiler',
p_file_id=>'ABCD');

Trace file with ABCD post fix can be found under $ORACLE_HOME/diag/rdbms/dbname/instance_name/tracedirectory.

Demonstration 2: (excerpt from MOS Note)
SQL> alter session set max_dump_file_size = unlimited;
SQL> ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*][sql:sql_id]'; 
SQL> --Execute the query --
To disable the trace
SQL> ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*] off';


Refer to MOS ML: 

How to Obtain Tracing of Optimizer Computations (EVENT 10053) [ID 225598.1]
alter session set tracefile_identifier='10053_&your_name'; 

.