 |
|
The Philosophy Behind
Optimizer Plan Stability
Oracle Tips by
Burleson
|
The use of stored outlines depends heavily upon
the nature of the SQL that you have in your shop. If your system is
characterized by dynamic tables where the number of rows may change
daily from 1,000,000 to 100, then you would probably not want to use
optimizer plan stability, because you would want the CBO to reevaluate
the execution plan for each SQL statement based on the current CBO
statistics.
On the other hand, if your tables are
relatively stable, using stored outlines will greatly improve the
performance of you SQL because the SQL parse phase is bypassed. You
also have the peace of mind of knowing that a SQL statement will
always use the same execution plan regardless of changes to Oracle
initialization parameters and CBO statistics.
Remember, there are many DBAs who feel that for
any SQL statement there exists only one optimal execution plan, and
once located, it should never change. If you are one of these DBAs,
then stored outlines can greatly aid your SQL tuning effort.
If you choose to implement stored outlines for
all of your SQL, you must also remember that CBO statistics will only
be used by new queries. Hence, you may want to reduce the frequency of
your reanalysis of tables and indexes.
The use of stored outlines also aids you when
you are migrating from rule-based to cost-based optimization. While
you begin to tune SQL statements to use the CBO, you can use optimizer
plan stability to ensure that your other SQL statements continue to
use their rule-based execution plans. Let’s take a look at how this
works.
Using Optimizer Plan Stability When Migrating to the CBO
Because Oracle8i is the first release of
Oracle where the CBO has comparable execution speeds to the RBO, many
DBAs are looking at methods for safely migrating their SQL into a
database with a cost-based optimizer default. If your application was
developed using the rule-based optimizer, then you may have already
invested a considerable amount of effort into manually tuning the SQL
statements to optimize performance.
Most RBO applications use a rule-based default
optimizer mode (optimizer_mode=rule) and then tune specific SQL
queries with cost-based hints. While the tuned SQL has hints to ensure
fast execution speeds, the majority of the SQL using the rule-based
default may change execution plans when migrated into a database with
a cost-based optimizer default (optimizer_mode=first_rows). You
can use optimizer plan stability to preserve your execution plans when
upgrading from rule-based to cost-based optimization.
By creating stored outlines for every SQL
statement before switching to cost-based optimization, you can use the
plans generated by the rule-based optimizer, while statements
generated by newly written applications developed after the switch use
cost-based plans. To create and store outlines for your system before
migrating to the CBO, use the alter system set
use_stored_outlines=true command. This will create an execution
plan for every SQL statement.
Later, when you are ready to see if your
non-hinted rule-based SQL statements will run faster with the Oracle8i
CBO, you can run a query against the DBA_OUTLINES to identify those
SQL statements that do not contain hints and tune them individually
using the procedures described later in this chapter.
Next, let’s get started and see how to prepare
your database to allow optimizer plan stability.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.
|
