Answer: The
optimizer_capture_sql_plan_baselines is part of the
Oracle 11g SQL Plan Management (SPM) feature. The
default is false but if
optimizer_capture_sql_plan_baselines is set to "true"
then Oracle will create baselines for repeatable SQL (v$sql
where executions > 1).
Also see
dba_sql_plan_baselines tips.
This is a somewhat frightening new features of 11g
because it says that for any given SQL statement, multiple
execution plans may exist. As we know, it is a rare
occurrence that the values of host variables would have such
a skew as to allow for multiple execution plans for a single
SQL statement. As a general rule, most SQL has
one, and only one, optimal execution plan, and it's the job
of the optimizer to find that plan and lock it down!
The whole point of adaptive SQL plan baselines for for
repeatable SQL statements. This approach does not make
sense for one-off SQL's.
Let's take a closer look at how this works. When
the Oracle optimizer created a cost-based decision tree, he
chooses the branch of the tree with the lowest estimated
cost. However, when SQL plan baselines are enabled,
Oracle will compare the SQL baselines to the newly-optimized
SQL:
- If the SQL plan matches the newly optimized SQL, the
plan is marked as "accepted"
- If the SQL plan does not match the newly-optimized
plan, Oracle will use the SQL plan and mark is as
"accepted", leaving the optimizers new plan in the library
cache un-used. If the "new" plan has a lower cost, it
will be used, but not until it has been verified
emprarcally.
In a sense, grabbing a SQL baseline, "freezes" the execution
plan and the SQL baselines is used unless the optimizer
generates a "new" execution plan for a query. At that
point, the optimizer evaluates if the new plan is cheaper
than the existing plan. If so, Oracle will use this
new plan. This is an attempt to ensure that only the
"best" execution plan is used by these "evolving" baselines.
However, it is important to remember that for the vast
majority of databases, there exists one, and only one
optimal execution plan for any given query.
See here for my research into
Oracle SQL Plan Management.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
