Before getting into the contents of a 10053 trace file and looking at any useful stuff, you need to know what the files are for and how and where they are created.
Essentially, setting event 10053 causes the Cost Based Optimizer to write information to a trace file describing the information it is using and the results of it’s calculations whilst walking through the decision-making process to determine the best execution plan. It includes the options that it has considered and discarded, those that it has accepted and options which are unavailable for various reasons. Because the decision making process is detailed and extensive, the files tend to be large for all but the most trivial statements and I’d challenge most people to read and understand an entire 10053 trace file! However, you are often looking for the reason for a particular bad decision, which helps to narrow the scope and, personally, I’ve found recent versions of 10053 trace files much more verbose and readable.
The best and most detailed reference I’ve seen is Wolfgang Breitling’s paper ‘A Look under the Hood of the CBO‘ although it was written many years ago and, as with all low-level undocumented Oracle information, things change frequently and without warning. At the start of the paper he describes how to set event 10053 to generate the related trace file in the instance’s user dump destination. However, since Oracle 11g, there is a more flexible way to generate the trace file that Maria Colgan describes in a couple of posts on the Optimizer Development Groups blog here and here. The latter post is particularly interesting because that approach automatically triggers a hard parse of the statement in order to generate the trace file.
Which solves what I suspect is one of the most confusing aspects of generating 10053 trace files when you’re first getting used to it. The statement needs to be hard-parsed to ensure that the trace file will be generated. It might help you to remember what it is that is being traced – the CBO making it’s decisions as it chooses an optimal execution plan. So, if the plan has already been generated then no trace file! One simple technique to get around this prior to 11g is to add a new comment to the statement to force a hard parse. Remember that if you find that the trace file is not being produced as expected, that might be the reason.
In the next post, I’ll start to look at the contents of a 10053 trace file.
I am not sure I asked Maria about that ( and if I did I forgot the answer ), but do you know if you need to have the diagnostic pack licensed in order to use the dbms_sqldiag.dump_trace procedure to generate the 10053 trace?
Mmm, very good point.
I suppose we’ll need to use Twitter for this 🙁
😉
License should not be necessary plus DUMP_TRACE is not even in the docs, so how Oracle can charge for using it 😉
Because, as you know, if they *can*, they *might* 😉
I suspect it is included in Enterprise Edition license, though, but will wait for someone who actually knows whether it is or not to confirm
I believe you are right. My suspicion was based on the “DBMS_DIAG” package name. But that package is not among the ones listed in the command line APIs for the diagnostic or tuning pack. Should have checked before commenting.