 |
|
The Problem of Using
Choose as the Default Mode
Oracle Tips by
Burleson
|
One of the most confounding problems with
Oracle databases happens when they do not explicitly set their
optimizer_mode parameter in their init.ora file. As you may
know, when no optimizer_mode is specified, Oracle8i
defaults to optimizer_mode=choose.
The choose default mode is very
dangerous for several reasons. The choose mode evaluates whether to
use the RBO or the CBO according to the presence or absence of CBO
statistics. If statistics exist, then use the CBO; else use the RBO.
The problem comes in when table and indexes are “accidentally”
analyzed. For example, a DBA might want to know the average row length
for a table and issue the analyze table command to populate the
avg_row_len column in the DBA_TABLES view, not realizing that he
or she has just changed the execution plans for dozens of SQL
statements.
The real problem with the choose mode is that
whenever any table or index contains statistics, the choose
mode assumes that you want to use the CBO and uses internal statistics
(such as the number of data blocks allocated to the tables) to
estimate statistics for the other tables in the query. These estimates
are quite poor and usually result in a suboptimal execution plan for
the query. This statistics estimation is done during the parse phase
of SQL execution, and the time required to gather statistics for the
CBO can be very significant.
It is very common in shops that are new to
Oracle and use the default optimizer_mode=choose. One of the
first things that I look at when a shop complains of poor SQL response
time is the default optimizer mode and the existence of complete
statistics for the CBO. Most Oracle shops do not use choose as
their default, and conduct a test of performance using each optimizer
mode and settle upon a default of rule, first_rows, or
all_rows as their default optimizer mode.
Using a Rule-Based Default Optimizer Mode
Some Oracle8i databases continue to use
a rule-based default (optimizer_mode=rule), and the DBA tunes
selected queries by adding cost-based hints and selectively analyzing
tables and indexes. This allow the best of both worlds because the
reliable and predictable rule-based optimizer is used for the simple
SQL, while queries that required specialized access methods such as
bitmap indexes or hash joins can still use cost-based optimization.
The choice about using a rule-based default is
generally made by running the production database for one day in
rule mode and another day in first_rows mode, and then
choosing the appropriate default in accordance with performance
feedback from end users and internal performance measurements from
STATSPACK. The goal is to minimize the amount of manual SQL tuning by
choosing a default optimizer mode with the best overall performance.
This empirical approach ensures that a minimal number of SQL
statements will have a sub-optimal execution plan.
One problem with the first__rows or
all__rows optimizer_mode is that it forces all statements into CBO
whether they have statistics or not. In the preceding scenario, the
DBA should also make sure all indexes and tables are analyzed.
When using a rule-based default, you can still
use all of the robust features of Oracle8i, including optimizer
plan stability (stored outlines), cursor sharing, and the advanced
cost-based hints. However, you always need to keep in mind the
differences between cost-based and rule-based optimization. The
driving table for cost-based optimization (when using the ordered
hint) is the reverse of that for rule-based optimization, and
rule-based queries are generally tuned by resequencing table names in
the from clause and predicates in the where clause.
Conversely, cost-based tuning relies on hints, CBO statistics, and the
settings for numerous initialization parameters to determine the
optimal table join order and overall execution plan.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.
|
