February 19, 2007

Parallelism and CBO

In the 9i Database Performance Tuning Guide and Reference part no: A96533-01, there is a convenient list of features that “require the CBO” on page 1-15, with the note

“Use of any of these features enables the CBO, even if the parameter OPTIMIZER_MODE is set to RULE.”

The list is as follows:

Partitioned tables and indexes

Index-organized tables

Reverse key indexes

Function-based indexes

SAMPLE clauses in a SELECT statement

Parallel query and parallel DML

Star transformations and star joins

Extensible optimizer

Query rewrite with materialized view

Enterprise Manager progress meter

Hash joins

Bitmap indexes and bitmap join indexes

Index skip scans

If you work through the list carefully, you will discover that the warning is a little ambiguous. For example, you can create bitmap indexes and the rule-based optimizer will ignore them; you can create function-based indexes and, if they start with simple columns, the rule-based optimizer may use them – but only the leading simple columns; you can parallel-enable a table and the rule-based optimizer will simply give up and hand over to the cost-based optimizer. So your interpreration of the phrase “use of any of these features” has to be a little flexible, and vary with feature.

The traps that this can lead can be quite funny. Here’s one I was asked about recently.

The system is running rule-based; it’s just gone through a big export/import cycle to upgrade to 9.2.0.7. To speed things up, the indexes, primary key constraints, and statistics were created in parallel after the data had been loaded. Suddenly a lot of queries were running cost-based, even though the optimizer_mode was set to rule.

“Clearly” the problem was in the parallel indexing, so I ran up a little demonstration:

So my first thought seemed to be wrong. I had been expecting the query to run parallel because there was a parallel-enabled index on the table (which you can check by looking at user_indexes.degree).

Then I had another look at the original email describing the problem – and changed the way I built the primary key. Rather than creating the index then creating the constraint, I changed the script to create the constraint and build the index at the same time, with the following impact on the execution plan:

This time the cost-based optimizer kicked in, and produced an execution plan that did a fast full scan on the index – but not a parallel fast full scan.

Checking the degree column from both user_tables and user_indexes, I found that the parallel degree I had used to enable the constraint had been applied to the table (hence the cost-based optimization), but not to the index (hence the serial execution path).

Does anyone want to bet on this being flagged as a bug one day, and being changed ?