10053 Trace Files - Different Plan in Differen ...

Rather than just describing the contents of the trace file, I thought it might be a good idea to tie the various sections into how they might help you solve Real World^TM problems. Which might not be immediately obvious when the first example I use is the trace file for :- 

SELECT * FROM DUAL;

But here it is. The first thing to note is that it's a 66KB file of over 2000 lines, even for something so trivial, which is just a taste of just how massive these files can be. It will also be environment and version-specific, as you'll see. Such is the nature of low-level trace files.

Going through the initial sections at a very high level, we have ....

Lines 1-20 - The standard type of trace file pre-amble that you might have seen in other trace files including

- The trace file name
- Instance and version information
- Host and O/S information
- Session Instrumentation of the type set by calls to DBMS_APPLICATION_INFO

Then we get to the first 10053-specific information (lines 22-27) which registers the various Query Blocks in this query. Understandably there is only one entry here in the QUERY BLOCK SIGNATURE section, were Oracle automatically names the query block to SEL$1

Line 28 is a note from SQL Plan Management highlighting that this statement does not already exist in the SQL Management Base.

Lines 29-32 contain a note that 11g Auto-DOP is disabled and at this point hopefully you'll start to see that if you already have a reasonable understanding of the CBO and related features, the trace file is actually pretty descriptive and verbose. From memory, I'm not sure it was always as easy to read.

For more information on Predicate Move-Around (as mentioned in lines 33-35), this 1994 VLDB paper is worth a look. Of course, when your statement is SELECT * FROM DUAL, there aren't exactly a lot of predicates to move around!

Next we go into a long section describing OPTIMIZER INFORMATION gathered from a variety of sources.

Line 40 shows the SQL statement and dont underestimate how important this is as further confirmation that you're looking at the right tracefile.

Lines 42-94 are a very handy Legend that lists the abbreviations that are used in the trace file. Some of these might have been guessable but, with so many terms used, it's great that you don't have to guess any more.

Lines 95-419 are a section that I often find very handy in solving issues with bad plans in two different database environments. The classic case of a developer telling me that it runs fine in Test but not in Production. The developer might send along the two plans and, even with a couple of good SQL Monitoring reports or the output of DBMS_XPLAN, that doesn't really tell me why the two plans are different, just that they are different. Working in an environment with multiple Dev, Test and Prod environments, it's not unusual to find that there is some drift in the instance configurations or someone has different session parameters set. It's a quick job to just open up the two trace files in a visual diff tool and make absolutely sure that the parameters the optimizer references (and you'll see just how many there are these days!) are truly identical.

It's just a small tip, but you'd be surprised by the number of issues that's helped me identify!