Understanding CBO with SQL_COMPILER Tracing

What is SQL Compiler Tracing?

SQL compiler tracing is a method to generate trace files that indicate what steps and actions the cost-base optimizer (CBO) used to determine a SQL statement’s execution plan.  It is an extension of the 10053 trace that’s been around for years, enhanced in 11g to provide arguably easier syntax and additional features.

SQL Compiler tracing also generates information about any resource manager plans in effect and show system resource utilization for the SQL statements or statements that are executed.

Enabling SQL_COMPILER Tracing

SQL compiler tracing can be enabled for any statements not already existing with a parsed execution plan in the library cache by doing:

If the execution does not require a hard parse, a trace file will still be generated but the optimization environment and path detail will not exist in the trace file.

Using SQL_COMPILER Tracing

Let’s do a SQL_COMPILER trace for a specific SQL statement:

After running the SQL statement, look in the diagnostic definition for a trace file matching the specified trace file identifier:

I won’t go into a significant amount of detail here about how to interpret the contents of the SQL Compiler trace file.  There is a tremendous amount of information in these trace files and an entire book may be required to provide a comprehensive description of the contents.  But I will cover some of the highlights below.

The first section of the trace file shows the SQL statement being traced:

After this, it displays a legend that can be used for reference later in the trace file:

Next, the trace file lists all the parameters used by the optimizer for the traced session.  Note how many parameters influence CBO’s behavior in 11gR2 – in our environment, I counted 310 different parameters.

After this comes a section showing which bug fixes are enabled or disabled for this session.  This is a reflection of the database version and patch set level:

Once these environment-related settings are displayed, the trace file starts to show information concerning the actual SQL statement we traced.  It starts by showing the cursor information from the query block:

After this, it shows operations that the optimizer elected to attempt to rewrite or transform.  There are a number of such activities that the optimizer will attempt, and I’ll list the first several of these for our test query below:

When the transform operations are complete, the trace file shows a final transformed query and will use this to begin costing:

CBO will then cost various operations in the query, but before showing the costing calculations it will display the current system statistics in place in the database:

After this section, the trace file shows object (table and index) statistics for the tables involved in the query.  An excerpt is provided below:

Once the object statistics are reported and the optimizer environment is known, the CBO will then cost the query.  It begins by calculating the single-table access paths:

The single-table access path for the first table in the predicate is listed first, and after this all the index join costs are established.  Further down in the trace file, the single table access path for the second table in the query predicate is listed:

After the single-table access path for our first table is provided, as mentioned above, all the available index join costs are calculated based on the query predicate(s) and column conditions:

Now that CBO has the statistics, single-table access costs, and index join costs, it will begin evaluating and costing join operations:

Next in the trace file, we see join operation alternatives and join costs:

The trace file will contain an exhaustive list of all possible join costing based on the conditions in the query predicate, available indexes, and available join methods.  If you spend time examining the details of the trace file, you’ll get an appreciation for how much work CBO does to arrive at the “best plan”.  When complete, the final plan and join order is decided upon and displayed in the trace file:

There is a great deal of other information in the trace file not listed above, including information about the SQL statement’s run-time statistics.  Again, I won’t go into much detail here, but below is an excerpt:

Using SQL_COMPILER tracing with a given SQL_ID

You can also enable SQL compiler tracing for a specific SQL_ID, if you know it.  The syntax looks like this:

Why and when do I use SQL_COMPILER tracing?

After spending time understanding the contents of the trace file, you may ask yourself – why would I use this debugging feature, and when would I use it?  The answer is simple – when the CBO is determining an execution plan that you “don’t agree with”, or if you’re having performance issues that you can’t explain, SQL_COMPILER traces provide a great deal of data that reveals what the optimizer is and was thinking when determining the execution plan.  There are a number of things to look for in the trace file:

• Look for non-standard optimizer variables near the top of the trace file and ensure that you’ve got these set “right”.  Oracle’s CBO works best as-delivered, with standard optimizer parameters.
• Look at single-table access paths and ensure the number of rows and blocks is a true picture of reality.  Stale statistics can cause the wrong single-table access path cost, which will lead to a misreporting of cardinality and a potentially suboptimal plan.
• Look for index and index join costs – if they don’t reveal what you expect, ensure that you don’t have data type issues.
• Ensure that cost calculations for columns containing histograms are properly costed.  Histograms could prevent issues with bind variables, bind variable peeking, and edge conditions.
• Understand join costs and how they’re calculated.  Some things to look for are:
  • Sub-optimal costs due to multiple related predication conditions – perhaps research extended statistics.
  • Mis-costed join cardinality.  The CBO will cost based on a number of calculations, but there are some conditions in which it can’t have as much information about predicate independence as a human would.
• Ensure segment statistics are up-to-date.