Database Reference
In-Depth Information
If you want to generate a trace file for one SQL statement at a time and you're able to manually execute it, it's
common to embed it between the following two SQL statements, thus enabling and disabling the event 10053. Just be
aware that the trace file is generated only when a hard parse is performed:
ALTER SESSION SET events '10053 trace name context forever'
ALTER SESSION SET events '10053 trace name context off'
If manually executing a SQL statement isn't an option, as of version 11.1 you can instruct the query optimizer
to generate a trace file the next time a SQL statement identified by a specific sql_id is hard parsed. To enable and
disable that behavior, you use SQL statements like the following (it goes without saying that you have to change
the sql_id passed as a parameter). The advantage of this method is that you can let the application issue the SQL
statement. That in turn gives you a trace file for the real SQL statement as executed in the live execution environment.
Note that this method works at the session level as well. Simply replace the ALTER SYSTEM statement with an ALTER
SESSION statement:
ALTER SYSTEM SET events 'trace[rdbms.SQL_Optimizer.*][sql:9s5u1k3vshsw4]'
ALTER SYSTEM SET events 'trace[rdbms.SQL_Optimizer.*][sql:9s5u1k3vshsw4] off'
If you want to analyze the SQL statement associated with a cursor stored in the library cache, from version 11.2
onward you can take advantage of the dump_trace procedure in the dbms_sqldiag package. This method requires
neither the execution of the SQL statement nor knowledge of the actual environment in which the SQL statement was
parsed. You also don't need to know the values of the bind variables that are associated to the cursor. The procedure
takes everything it needs from the library cache and instructs the query optimizer to reoptimize the SQL statement
and dump a trace file. The following illustrates how to call it:
dbms_sqldiag.dump_trace(
p_sql_id => '30g1nn8wdymh3',
p_child_number => 0,
p_component => 'Optimizer',
p_file_id => 'test'
);
The procedure has the following input parameters:
p_sql_id specifies the parent cursor to be processed.
p_child_number specifies the child number that, along with p_sql_id , identifies the child
cursor to be processed. This parameter is optional and defaults to 0.
p_component specifies whether the procedure dumps the Optimizer or Compiler trace. Simply
put, while the former is analog to setting event 10053, the latter writes even more information
to the trace file.
p_file_id specifies a value for the tracefile_identifier initialization parameter. This
parameter is optional and defaults to NULL .
Search WWH ::




Custom Search