Capturing 10053 trace files continued

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+

Comments:

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! :)

Posted by Kellyn Pot'Vin on December 02, 2011 at 09:34 AM PST #

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

Posted by Marcus Mönnig on December 03, 2011 at 12:41 AM PST #

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.

Thanks

Sandeep Redkar

Posted by guest on February 26, 2012 at 10:15 PM PST #

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.

Thanks.

Posted by guest on March 24, 2012 at 02:12 PM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

The Oracle Optimizer blog is written by members of the Optimizer development team. The goal of this blog is to provide an insight into the workings of the Optimizer and the statistics it relies on. The views expressed on this blog are our own and do not necessarily reflect the views of Oracle and its affiliates. The views and opinions expressed by visitors on this blog are theirs solely and may not reflect ours.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today