DBMS_SQLDIAG.DUMP_TRACE Procedure

This procedure dumps 10053 (optimizer or compiler) trace for a specific SQL statement.

The procedure does not execute the SQL statement to generate the trace.

Compiler trace is a superset of optimizer trace. It includes:

DUMP_TRACE procedure parameters are:

Argument Name Type In/Out Default?
P_SQL_ID VARCHAR2 IN  
P_CHILD_NUMBER NUMBER IN DEFAULT
P_COMPONENT VARCHAR2 IN DEFAULT
P_FILE_ID VARCHAR2 IN DEFAULT

P_SQL_ID specifies the child cursor SQL_ID

P_CHILD_NUMBER specifies the child cursor number

P_COMPONENT can be 'Optimizer' or 'Compiler'

P_FILE_ID specifies a tracefile identifier

For example:

DBMS_SQLDIAG.DUMP_TRACE
(
  p_sql_id => '2y220pbrk573n',
  p_child_number => 0,
  p_component = 'Optimizer',
  p_file_id => 'OPTIMIZER'
);

Note that P_FILE_ID sets the TRACEFILE_IDENTIFIER, but does not reset it at the end of the call, so subsequent trace will be written to the new file. Alternatively NULL can be specified (the default).