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


 

HTML Text

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 */
   count(*)
from
   emp
where
   mgr = 7902
and
   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;

    DEPTNO
----------
        10
        20
        30

SQL> select distinct mgr from emp;

       MGR
----------
      7566
      7698
      7782
      7788
      7839
      7902

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:

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
 SELECT STATEMENT
  SORT
AGGREGATE                                                            1
    AND-EQUAL
                                                                     1
      INDEX
RANGE SCAN                     EMP_MGR                               1
      INDEX
RANGE SCAN                     EMP_DEPTNO                
             2

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.

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
 SELECT STATEMENT
                                                                     1
  SORT
AGGREGATE                                                            1
    TABLE ACCESS
BY INDEX ROWID                 EMP                                   1
      INDEX
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.

Conclusion

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