An easy way to obtain 10053 trace

I have just learnt a couple of new methods 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, you 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/trace directory.


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]