Question: What is an Oracle SQL plan baseline,
and how do I create a SQL plan baseline? Is a SQL baseline the same
as swapping execution plans with SQL profiles and optimizer plan
stability?
Answer: An Oracle SQL plan baseline is a
series of stored execution plans designed to make the database
resilient against degraded performance caused by changes in
execution plans. This is achieved by only using execution plans
which are accepted.
There are three techniques for changing SQL statements when is
cannot touch the SQL, as is the case with vendor software:
1 - Oracle 11g onwards: Create
a SQL baseline for the correct plan
2 - Oracle10g onwards:
Swap execution plans for SQL Profiles
See
here.
3 - Oracle9i and earlier: Swap
plans using stored outlines.
See
here
When an SQL statement is parsed, the cost-based optimizer
produces a decision tree and chooses the execution plan with the
lowest estimated cost. If no matching plan is found within the SQL
plan baseline, the optimizer evaluates the accepted plans in the SQL
plan baseline and uses the one with the lowest actual cost in terms
of execution time. If automatic capture is enabled, at this point
the plan generated by the CBO will be added to the SQL plan baseline
and flagged as non-accepted. A non-accepted plan is a plan which is
currently a part of the SQL plan baseline but is not used until it
can be proven not to cause degraded performance.
There are two methods to create SQL plan baselines: automatic
plan capture and manually loading existing execution plans. When
automatic plan capture is enabled, the plan history for SQL
statements is created and maintained using information provided by
the optimizer. This plan history includes relevant information used
by the optimizer to reproduce the execution plan, such as bind
variables, compilation environment, outline and SQL text. When an
SQL statement is issued, Oracle will check for an SQL plan baseline.
If an SQL plan baseline does not exist, Oracle will create an SQL
plan baseline and then execute the associated plan. If an SQL plan
baseline does exist, then it just executes a plan from the SQL plan
baseline. It is recommended that you perform a considerable amount
of testing before implementing automatic plans in a production
environment to ensure no negative impact on performance exists.
You can manually load plans in conjunction with, or as an
alternative to, automatic plan capture. The manually loaded plans
are not verified for performance, but are added as accepted plans to
existing or newly created SQL plan baselines. Plans can be loaded
from SQL tuning sets using load_plans_from_sqlset as
follows:
declare
l_plans_loaded pls_integer
begin
l_plans_loaded :=
dbms_spm.load_plans_from_sqlset(
sqlset_name => '');
end;
Plans may also be manually loaded from specific SQL statements in
the cursor cache using load_plans_from_cursor_cache. This
function has four overloaded methods, allowing statements to be
identified by sql_id, sql_text,
parsing_schema_name, module and action. The
following identifies the statement using a sql_id:
declare
l_plans_loaded pls_integer
begin
l_plans_loaded :=
dbms_spm.load_plans_from_cursor_cache(
sql_id => '');
end;
The return value of each of these functions will indicate the
number of the plan loaded by the function call. For more information
on SQL plan baselines check out the following helpful links:
 |
|
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.
|
