 |
|
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.
|
