Friday, February 24, 2012

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_file_id=>' test1053');

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

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

No comments: