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 WorldTM 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!
Tracked: Jun 01, 11:26
That predicate move-around is a real bummer: have had a few nasty experiences with it when upgrading release level. I wonder if there is some weird "_" parameter to turn it off...
Of course: setting optimizer_features_enable can fix it, but it also turns off other potentially useful stuff.
Whilst *not* recommending this and still uncertain, one of the things I like about these trace files is you can learn possibilities. I just had a quick look through the example here ...
_pred_move_around = true
Looks a likely candidate
Thanks. Yes, looks likely.
Of course: never do this without first consulting with MOS, yaddayadda.
It might be a nice idea to open the trace file in a new window - it makes it easier to follow your comments while reading through the file
I conducted a brief survey on Twitter and received similar responses. It's now a seperate file that should open in a seperate browser tab or window. At least that's how it appears to me.
I suspect I still prefer to include sections of the file in the actual blog post, but will have ponder that.
Just a suggestion: line numbers.
Instead of saying things like "next we go into a long section...", something like "Line Numbers 445 - 598376 is a long section..."
It makes it easy to understand where you are, without you having to embed sections of the file in your blog post (which, with something this big, is probably not a great idea!).
Embedding discrete sections as I see you are thinking of doing would also take away the experience of navigating the file as-is. I think seeing it as a whole, and being guided to the good bits, without missing out the fact that there are swathes of dross, would be prefereable. But line numbers are then essential so we know where we're supposed to be looking!
Good suggestion, thanks.
I'll hopefully have a bit of time to faff around with the options and suggestions over the next few days and see what sticks.
Hope all's well!
I've had a first crack at the line number thing.
I think I personally prefer including bits of the trace file in the post itself as I find the flipping back and forwards slightly annoying, but others seem to prefer the full file. I'll probably use a combination of both ...
Makes a big difference, so thanks for that!
With two browser windows, opened side-by-side, snapped to half-screen each, the new approach makes it a lot easier to follow along. Much appreciated.
You're welcome. Now I just need to pull my finger out and actually come up with some better examples