Diagnostic output of the Oracle Query Optimizer

Question

There are many instances where we are not happy with the decisions that Oracle's cost-based-optimizer makes regarding the query execution plan. Using hints, less-than-straightforward query transformations, index reorganization and instance parameters we then try to coax it into doing what we think makes more sense. It is very much taking stabs in the dark, and the results can vary widely between the development, staging and production servers (no matter how hard we try to synchronize statistics and such).

Is there a way to get diagnostic output from the Oracle server to illustrate what alternatives the CBO has looked at, and why they have been discarded?

I am thinking of a feature like EXPLAIN PLAN, except that it enumerates all possible (or at least many) execution plans and their associated costs.

Answer 1

You can better understand how the Oracle optimizer is making its decisions by collecting 10053 trace event dumps.

I have found that the optimizer rarely makes "wrong" decisions (well, excluding distributed queries across db links), but is usually being misled by statistics that don't accurately represent the nature and distribution of your data. Check resources like http://optimizermagic.blogspot.com and http://www.jlcomp.demon.co.uk for a look into optimizer internals.

You can also use the Oracle SQL Analyzer utility (extra cost) to have the optimizer consider alternative paths and if a better one is found it will at least produce the old and new EXPLAIN PLANS for your comparison. Sometimes you can reverse-engineer the cause for the sub-optimal plans from this information (and more often or not you'll find it is a statistics problem)