Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Oracle 10g de-supports rule-based SQL Optimizer

Oracle Tips by Burleson Consulting
Mike Ault

With a large number of Oracle shops using the rule-based optimizer (RBO), migration to cost-based optimization (CBO) will become a important task when migrating to Oracle10i, where the RBO will disappear.

As you may know, Oracle provides several parameters that can adjust the behavior of the CBO to make it more like rule-based optimization, especially optimizer_index_caching and optimizer_index_cost_adj.

Even in Oracle, the CBO sometimes falsely determines that the cost of full-table scan is less than the cost of an index access. The optimizer_index_cost_adj parameter is a great approach to whole-system SQL tuning, but you will need to evaluate the overall effect by slowly resetting the value down from 100 and observing the percentage of full-tale scans. You can also slowly bump down the value of optimizer_index_cost_adj when you bounce the database and then either use the access.sql or plan.sql scripts or reexamine SQL from the STATSPACK stats$sql_summary table to see the net effect of index scans on the whole database.

The plan.sql script (see code depot from book below) uses the v$sql_plan view and a quickly the reduction in sub-optimal, large-table full-table scans: 

                         Full table scans and counts
          Note that "K" indicates in the table is in the KEEP pool.

OWNER          NAME                      NUM_ROWS  C K   BLOCKS  NBR_FTS
-------------- ------------------------  --------- - - -------- --------
SYS            DUAL                                N          2   97,237
SYSTEM         SQLPLUS_PRODUCT_PROFILE             N K        2   16,178
DONALD         PAGE                      3,450,209 N    932,120    9,999
DONALD         RWU_PAGE                        434 N          8    7,355
DONALD         PAGE_IMAGE                   18,067 N      1,104    5,368
DONALD         SUBSCRIPTION                    476 N K      192    2,087
DONALD         PRINT_PAGE_RANGE                 10 N K       32      874
ARM            JANET_BOOKS                      20 N          8       64


We also note that the RBO still exists inside Oracle10g, but you may no longer specify optimizer_mode=rule.

SQL> alter session set optimizer_goal=rule;
alter session set optimizer_goal=3rule
ERROR at line 1:
ORA-01986: OPTIMIZER_GOAL is obsolete

However, you can still use the optimizer_mode at the session level:

SQL>  alter session set optimizer_mode=rule;

Session altered.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.