Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




When the Rule-Based Optimizer Fails
Oracle Tips by Burleson

There are cases in which the rule-based optimizer fails to choose an optimal execution plan. In Oracle8i, this is most often due to the inability of the RBO to use advanced index structures and parallel query. New features aside, we also see that the RBO will often choose a suboptimal index to service a query. This happens because the rule-based optimizer is not aware of the number of distinct values in tables and indexes but relies on simple heuristics to find an acceptable access path to the data.

Remember, in rule-based optimization, all indexes have an equal ranking. When items have an equal order, the row cache order is used to select the first index. When the RBO detects equally ranked objects, it chooses the first object that it comes to in the row cache. Be aware that row cache order cannot be externally controlled, as it is determined by a internal unpublished algorithm. It is possible that row cache order can be changed by modifying shared pool parameters, or by dropping and recreating the objects, but this option is seldom as feasible as simply adding a hint and running the query again as a cost-based query.

When the Rule-Based Optimizer Is Best

As I have repeatedly noted, there are many times when the RBO will achieve a faster execution plan than cost-based optimization, especially in the earlier releases of Oracle8. To illustrate these differences, letís use the Oracle demo database for our example. For the following query, assume that we have built a nonunique B-tree index on both the deptno and mgr columns.

select /*+ rule */
   mgr = 7902
   deptno = 10

Now, letís take a look at the distribution of distinct values within the deptno and mgr columns.

SQL> select distinct deptno from emp;


SQL> select distinct mgr from emp;


Here you see that the mgr column has more than double the selectivity of the deptno column because it has far more unique values. We would expect that the most efficient way to service this query would be to use the AND_EQUAL access method, starting with the most selective index. Here is the rule-based execution plan for this query:

OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
AGGREGATE                                                            1
RANGE SCAN                     EMP_MGR                               1
RANGE SCAN                     EMP_DEPTNO                

The rule-based optimization uses the AND-EQUAL access method to combine the ROWID lists from each index range scan to get only those ROWIDs that meet the selection criteria.

To see the difference between this method and cost-based optimization, here is the same query using the first_rows hint.

OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
AGGREGATE                                                            1
BY INDEX ROWID                 EMP                                   1
RANGE SCAN                     EMP_DEPTNO                            1

Unlike under rule-based optimization, the cost-based optimizer wants to perform an index range scan on the deptno index and then filter for the rows belonging to the specified mgr column. This could take a long time, especially if the tables are large.

NOTE: Remember that the rule-based optimizer does not recognize bitmap or function-based indexes. Adding a rule hint will cause full-table scans in cases where cost-based indexes are used to access the target table.

In sum, you need to pay careful attention to the indexes that are chosen by the rule-based optimizer, and either disable the indexes that you do not want to be used in the query or force the use of the index that you want. To review, indexes can be explicitly specified with the index hint, or unwanted indexes can be disabled by mixing data type on the index (i.e., where numeric_column_value = 123||í Ď).

Any perceived index change (whether it really affects the value or not) will defeat the index, so simply adding zero to a number or a null to a character should always disable index access.


The rule-based optimizer remains quite popular in Oracle8i despite the attempts by Oracle to improve cost-based optimization to the point where it always outperforms the RBO. The major points in this chapter include these:

  • The RBO is very stable and predictable, while the CBO is more intelligent but often unpredictable.

  • The number of databases using the RBO falls with each new release of the CBO, but there are still a significant number of databases using a rule-based optimizer default.

  • Some shops use a cost-based optimizer default and override selected queries with the rule hint.

  • Some shops use a rule-based default and tune SQL queries by adding cost-based hints and analyzing selected tables and indexes

  • In the RBO, the order of tables in the from clause determines the table join order. The last table in the from clause is the driving table, which should be the smallest table.

  • The order of the Boolean predicates in the where clause can also affect the behavior of the RBO.

  • Using optimizer_mode=choose can be very dangerous to performance if selected tables or indexes are analyzed.

  • The most common error with the RBO is its inability to know the selectivity of each index on a table. Hence, the RBO sometimes uses a nonselective index to access a table.

Next, letís take a look at the single most complex of all SQL tuning operations, the optimization of table joins. If you can master the table join, you are well on your way to being a SQL tuning guru.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation. 

Hit Counter