Insights into Statistics, Query Optimization and the Oracle Optimizer

Capturing 10053 trace files continued

Maria Colgan
Distinguished Product Manager

In our previous blog post I described how you can use the new diagnostic event infrastructure in Oracle Database 11g to capture an Optimizer trace (10053) for any SQL statement once you have its SQL_ID. The approach I showed using the traditional ‘Alter session set event’ approach and thus required you to execute the SQL statement. What I forgot to mention (and have been scolded for) was that you can also use the new infrastructure to generate an Optimizer trace for any SQL statement in the cursor cache without having to execute it. The DBMS_SQLDIAG package has been extend to include a procedure called DUMP_TRACE. This procedure, just like the event requires only the SQL_ID of the statement you wish to generate an Optimizer trace for but it doesn’t require you to re-execute the statement. The procedure will automatically trigger a hard parse of the statement to generate the trace.

So if we continue with the example we had in the previous blog post. The command to generate the trace with the new DUMP_TRACE procedure would be as follows.

After running the procedure you can easily find the trace file by looking in USER_DUMP_DEST for the trace file whose name includes ‘MY_SPECIFIC_STMT_TRC’.

Unfortunately the DUMP_TRACE procedure didn’t make it into the DBMS_SQLDIAG documentation but Greg Rahn has blogged about this procedure in a lot more detail if you are interested.

Maria Colgan+

Join the discussion

Comments ( 4 )
  • Kellyn Pot'Vin Friday, December 2, 2011

    I tried out this the other day and was glad to have one more way to run traces! Thank you for putting this out there, Maria! :)

  • Marcus Mönnig Saturday, December 3, 2011

    Will the optimizer consider of bind variables (values and types)? If so, which bind variables will it use?

  • guest Monday, February 27, 2012

    Thanks for such informative blog. When I searched on net, i found there are two possibilities for p_component parameter.

    1. Compiler

    2. Optimizer

    For tracing SQLs with 10053 event, we can use Compiler. But what is the use for Optimizer parameter value. I would appreciate if you can answer this.


    Sandeep Redkar

  • guest Saturday, March 24, 2012

    Is there a way to get the OWNER of each object to show up in trace files?

    Sometimes the use of synonyms and database links makes it difficult to determine the exact data source for some parts of a query.

    I haven't been able to find any discussion of whether it is possible to have the owner displayed with the data sources for the plans shown in a trace.


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.