Question: What is an Oracle SQL plan baseline,
and how do I create a SQL plan baseline?
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.
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:
|
|
 |
|
Guarantee your Success!
Oracle is the
world's most complex, robust and flexible database, considered
impossible to master without a mentor.
That's why all BC
Oracle trainers are working professionals, experts in Oracle who
share their tips and secrets. |
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright ? 1996 - 2012
All rights reserved.
Oracle ?
is the registered trademark of Oracle Corporation.
|
|
