Migrating to the Cost-Based Optimizer
Oracle Tips by Burleson Consulting
As we know, the rule-based optimizer has
remained essentially unchanged since Oracle6, while the cost-based
optimizer is being constantly enhanced. Starting with Oracle8i, the
CBO has been enhanced to the point where it provides better overall
execution plans, and many shops are looking to migrate their SQL to
the CBO after installing Oracle8i. This migration is very
worthwhile, but there are pitfalls that must be considered.
Retraining the development staff
Seasoned Oracle developers who have been
using the RBO for many years will find the CBO to be very foreign.
Unlike the RBO, the CBO uses opposite set of rules for determining
the driving table, the wealth of CBO hints can be intimidating, and
the query transformation techniques employed by the CBO can be
aggravating to the novice. To ensure a successful migration to the
Oracle8i CBO, the Remote DBA and development staff should attend the
appropriate training to ensure that they fully understand the CBO
Choose your Cost-based Optimizer Philosophy
Once your company has migrated to Oracle8i
and made a decision to use the cost-based SQL optimizer, you must
make a very important decision. Especially important in undertaking
a migration to the CBO is documenting your companies’ philosophy
regarding the relationship between the CBO statistics and the use of
optimizer plan stability. Before undertaking a migration to the CBO,
the Oracle Remote DBA must develop a philosophy regarding CBO statistics
and their ability to dynamically change SQL execution plans. This
choice is heavily dependent upon the nature of the Oracle database,
and either choice may be optimal.
Remember, some Oracle professionals subscribe
to the theory that for any SQL query, there exists one, and only
one, optimal execution plan. Once this optimal execution plan is
located, it should be made persistent with optimizer plan stability.
In contrast, other shops want their SQL to change execution plans
whenever there has been a significant change to the CBO statistics.
Stable shops where the table statistics rare
change will want to employ optimizer plan stability to make
execution plans persistent, while shops where the CBO statistics
frequently change will tune their queries without optimizer plan
stability so that run-time optimizer is free to choose the most
appropriate execution plan, based on the CBO statistics.
The choice of SQL philosophy has a dramatic
impact on the approach to SQL tuning and maintenance.