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 220.127.116.11. 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:
drop table t1; create table t1 as select rownum id, rpad('x',10) small_vc, rpad('x',400) padding from all_objects where rownum <= 20000 ; create unique index t1_pk on t1(id) parallel (degree 2); alter table t1 add constraint t1_pk primary key(id); execute dbms_stats.gather_table_stats(user,'t1',cascade=>true) set autotrace traceonly explain alter session set optimizer_mode = rule; select count(*) from t1; set autotrace off Execution Plan (18.104.22.168) ----------------------------------------- 0 SELECT STATEMENT Optimizer=RULE 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'T1'
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:
-- create unique index t1_pk on t1(id) parallel (degree 2); alter table t1 add constraint t1_pk primary key(id) parallel 2; Execution Plan (22.214.171.124) ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=RULE (Cost=8 Card=1) 1 0 SORT (AGGREGATE) 2 1 INDEX (FAST FULL SCAN) OF 'T1_PK' (UNIQUE) (Cost=8 Card=20000)
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 ?