 |
|
Determining the Default Optimizer_Mode
Oracle Tips by Burleson Consulting |
As we have noted, there are some shortcomings
in the CBO and the RBO, and you must make a decision about the
appropriate optimizer_mode default for your database. If your
database has many thousands of SQL statements to tune, choosing the
best overall optimizer mode can save you hundreds of hours of SQL
tuning effort. Every database is different, both in the types of
queries and the structures of the data. However, there are some
general rules that can be applied:
Prior to Oracle 8.1.6, queries that join three or more
large tables will generally benefit from the rule-based optimizer or
the first_rows hint.
-
Queries that access bitmapped or function-based
indexes will benefit from the cost-based optimizer.
-
Queries that will benefit from hash joins
-
Queries that use STAR query hints need the
cost-based optimizer.
-
Databases at Oracle8i and beyond may benefit from
the cost-based optimizer default.
The choice of default optimizers depends on the
version of Oracle. Oracle recommends that all Oracle7 databases use
rule-based optimization, and by Oracle8i, the cost-based optimizer
has improved to the point where it can be considered for a default
optimizer mode. Given that any Oracle environment would benefit from
both optimizers, there are several choices. The Remote DBA could make the
cost-based optimizer the default and use rule hints when required,
or they could make rule-based the default and use cost hints and
statistics when desired.
Many Remote DBAs
conduct a study where they bounce the init.ora optimizer_mode and
then run the application for a day in each mode and collect
statistics. From these overall comparisons, the proper default
optimizer_mode becomes readily apparent. Here is the procedure:
Notify your
end-users that you are changing important parameters and ask them to
call you if they notice any significant changes in performance. Also
tell the end-users that you can quickly put the system back into its
original state.
Again, the first step in any Oracle SQL
tuning endeavor is to set the appropriate default for the whole
database. This may save hundreds of hours of manual tuning.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
