10053 trace with DBMS_SQLDIAG.DUMP_TRACE -11gR2

1.    Find sql_id for the query that you want to trace.

SELECT sql_id,child_number FROM v$sql WHERE sql_text LIKE '%your sql text here%';

2.    Execute DBMS_SQLDIAG.DUMP_TRACE with your sql_id

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

3.    Tracefile with the postfix “test1053” that I used here will be generated under diag trace directory:

$ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace

-rw-r----- 1 oracle oinstall   28906 Feb 24 14:35 testdb_ora_32607_test1053.trm

-rw-r----- 1 oracle oinstall   91040 Feb 24 14:35 testdb_ora_32607_ test1053.trc

Another way is to:

SQL> alter session set max_dump_file_size = unlimited;
SQL> ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*][sql:sql_id]';
SQL> --Run the query here --

To disable the trace:

SQL> ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*] off';