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:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm 
 

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.

---