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).