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

 

 

   
 

Tuning with the Rule-Based Optimizer
Oracle Tips by Burleson
 

Ever since Oracle first introduced the cost-based optimizer in Oracle7, tuning professionals have been struggling with coping with dual optimizer modes. While Oracle Corporation continued to recommend the cost-based optimizer, many SQL tuning professionals found that the rule-based optimizer often created the most efficient execution plans. In this author’s experience, over 80 percent of Oracle7 databases used rule-based optimization. In Oracle8, this number dropped to about 60 percent, and in Oracle8i we see that about 40 percent of databases still find faster overall execution plans for their systems using rule-based execution (Figure 15-1).

Figure 1: Relative use of default optimizer modes

Today we must make a decision between a predictable and stable rule-based optimizer or the intelligent and often unpredictable cost-based optimization. Even in Oracle8i, there are still conditions (especially n-way table joins) where the CBO fails to use all of the available indexes and performs an unnecessary full-table scan on a table. This is a well-known shortcoming of cost-based optimization, and many SQL tuning professionals will add a first_rows hint and lower the value of optimizer_index_cost_adj, thereby telling the CBO to always favor index scans over full-table scans. Others will simply code a rule hint ensure that the proper indexes are being used to join the tables.

Regardless of the approach, tuning SQL for the rule-based optimizer is very different from tuning SQL with cost-based optimization. This chapter will cover the following topics:

  • Invoking rule-based optimization

  • The problem of using choose as the default optimizer mode

  • Using a rule-based optimizer mode as the database default

  • Tuning with the rule-based optimizer

Invoking Rule-Based Optimization

The RBO is very easy to invoke, and there are three ways to invoke the rule-based optimizer at the session or database level:

  • Setting the init.ora parameter optimizer_mode=rule

  • At the session level using alter session set optimizer_goal=rule;

  • Adding rule hints to cost-based SQL: /*+ rule */ or --+ rule

Of course, setting rule-based optimization is the easy part. The challenge is determining when to invoke rule-based optimization to improve the speed of you SQL query. Let’s begin with a discussion of the choose optimizer mode and see how it flips between cost-based and rule-based optimization methods.


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