Oracle Database 11g: SQL Plan Management, Part 1

Synopsis.
Oracle Database 11gR1 introduces SQL Plan Management, a new set of tools that
allows an Oracle DBA to capture and preserve the most efficient execution plans
for any SQL statement, thus limiting the impact of refreshed optimizer
statistics, changes to existing applications, and even upgraded database
versions. This article – the first in this series – provides a primer to SQL
Plan Management principles and offers a simple demonstration of its performance
tuning capabilities.

The previous
article series
explored the capabilities of Oracle Database 11g’s new SQL Performance Analyzer (SPA) tool set to
capture and analyze performance statistics for SQL statements and thus more
accurately predict the impact of environmental changes upon those statements’
performance before the
environment has been changed. However, while this is a valuable addition to any
Oracle DBA’s “tool belt,” any experienced DBA will tell you that this is only
half the battle.

A
much more insidious problem crops up when a valid (and excellent!) execution
plan for a SQL statement gets invalidated and the statement is reparsed, but
the resulting new execution plan is actually much worse than the original plan.
This can happen for several reasons:

  • It’s not uncommon for a cursor to get aged out of the Library Cache eventually.
    Because this necessitates a hard parse of its SQL statement, there is always a
    chance that the cursor may end up using a less efficient execution plan.
  • A cursor may also be invalidated when optimizer
    statistics are recalculated on that cursor’s dependent objects, or when even
    one of that cursor’s dependent object(s) is modified.
  • There are other, less common causes of changes to a
    SQL statement’s plan stability: a change in optimizer versions, initialization
    parameter modifications, and even (after Oracle 10g) the creation of a new SQL
    Profile for the statement.

SQL Plan Management: A Brief History Lesson

One
of the biggest challenges any DBA faces is the ability to identify not only
which SQL statements are performing poorly, but also capturing the best
execution plans for SQL statements and making those plans available to all statements
that could benefit from them. The evolution of these solutions makes for an
interesting history lesson.

Phase 1: Stored Outlines. Oracle 8i
provided a mechanism to capture a SQL statement’s execution plan and save it
into a stored outline. A stored outline is essentially a SQL statement that a
DBA or developer has optimized – sometimes using tuning tools, and many times
using just simple trial and error – and then stored within the OUTLN schema.
When a SQL statement is executed and that statement’s execution plan matches
that of the stored outline, Oracle uses the SQL statement within the stored
outline instead. Stored outlines can also be staged for eventual execution
within categories, or limited to only those sessions that have a matching
category.

This
provides what Oracle 8i terms plan stability,
and while it can be useful, it does have some drawbacks, the most serious one
being that stored outline plans over time become outdated because of changes to
data distributions or optimizer statistics. Then the stored outline has the
tendency to force a statement to actually use a plan that’s considerably less
efficient than if a new plan was derived by simply reparsing the statement and
obtaining a better execution plan.

Phase 2: SQL Profiles. Oracle Database 10g
addressed the shortcomings of stored outlines with a new method called SQL Profiles. The DBA uses either the SQL
Tuning Advisor or SQL Access Advisor to identify SQL statements that can
benefit from better performance. These statements can be stored within a SQL
Tuning Set, an AWR snapshot, or even within the current library cache. Once
identified as tuning candidates, these Advisors analyze the captured statements
for better performance opportunities, and then generate special extensions to the
statements’ execution plans called SQL Profiles that rewrite the SQL statements
to obtain the best possible performance during execution.

Like
a stored outline, a SQL profile provides the ability to use a better execution
plan if one is available. SQL profiles can also be staged for execution just
like stored outlines, or limit execution of a SQL Profile to only particular
sessions. But the most significant improvement over stored outlines is twofold:
(a) the ability for limited self-tuning to insure a SQL Profile was providing
the best possible execution plan; and (b) the ability to detect when a SQL
Profile was no longer efficient (and thus have ADDM suggest that a new SQL
Profile needs to be generated).

Phase 3: SQL Plan Management. Oracle 11g
implements a new feature set called SQL Plan
Management
(SPM) that overcomes unexpected SQL execution plan
regression via a simple yet elegant methodology. Once a user session has been
enabled for automatic SQL Plan Baseline capture, the cost-based optimizer (CBO) logs any SQL statements executed
within that session into the SQL Management
Base
(SMB). This stores the statement’s SQL text, its outline,
its bind variables, and its compilation environment as a SQL Plan Baseline.

Since
this is the first time the statement has been executed, Oracle 11g treats the
stored execution plan as optimal.
It’s during the next execution of
the same SQL statement that the
elegance of SQL Plan Management becomes evident. During the statement’s second
execution, the CBO compares the statement’s execution plan against the one that’s
currently stored in the SMB. If the CBO detects a new plan, the new plan is first added to plan history, and
then the new plan is evaluated to see if it’s more effective than the current execution plan in the SMB.

If
the new execution plan would cause the statement’s performance to improve, then
SPM marks the new plan as the
best choice for the execution of the statement. As long as the DBA hasn’t
overridden the default value of TRUE for the OPTIMIZER_USE_SQL_PLAN_BASELINES
initialization parameter, the CBO uses the new plan during
the statement’s current execution. Otherwise, if the statement’s performance
would actually regress because
the new plan was used, then the CBO chooses the one plan from all accepted
plans in the SMB that will result in the lowest execution cost. Either way,
however, SPM will store the new execution plan within the SMB, because it’s
possible that the new plan may actually become a better choice in the near
future.

Capturing SQL Plan Baselines

The
good news is that Oracle 11g makes it extremely easy to capture SQL Plan
Baselines into the SMB. First, the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter controls whether automatic capture of SQL Plan Baselines is
activated. This parameter’s default setting, FALSE, means that SQL Plan Baselines aren’t automatically captured. However,
as soon as the DBA sets it to TRUE at either the session or system level, SPM begins to log the execution
of SQL statements; when a SQL statement is executed more than once, that
statement will be considered as a candidate for SQL Plan Baseline capture.

Second, a new Oracle 11g package, DBMS_SPM,
provides the ability to capture and introduce high-volume SQL statements in advance by “seeding” plans manually from several sources:

  • One or more SQL statements currently found in the database’s
    Library Cache can be used to
    create SQL Plan Baselines. Procedure LOAD_PLANS_FROM_CURSOR_CACHE can
    be used to capture any subset of statements in the Library Cache as potential
    SMB candidates.
  • SQL statements stored in either a SQL Tuning Set or an AWR Snapshot can be captured and translated
    into SQL Plan Baselines via the LOAD_PLANS_FROM_SQLSET procedure.
  • Finally, SQL Plan Baselines can be populated from SQL
    statements in an imported staging table.
    This means it’s possible to capture statements from a totally different
    database – say, a QA environment – and “pre-seed” them into the current
    production environment. (I’ll spend a lot more time on how this can be an
    excellent technique to prevent plan regression during database or application
    upgrades in future articles in this series.)

Viewing SQL Plan Baseline Information

The
SQL Plan Baseline metadata captured in the SMB contains attributes that SPM and
the CBO uses for plan control.
When a brand-new plan first arrives in the SMB, it’s marked as ENABLED,
but it won’t be marked as an ACCEPTED plan until either (a) the CBO has
evaluated the plan and judged it the best plan, or (b) the plan has been
evolved into ACCEPTED
mode. Before the CBO considers using a plan for execution, it must be marked as
both ENABLED
and ACCEPTED.

The
simplest way to view this metadata is to query the DBA_SQL_PLAN_BASELINES
data dictionary view. Here’s a summary of the most valuable plan
control information found there:

Table 1.1. SQL Plan Baseline Plan Control Metadata

Attribute

Description

SQL_HANDLE

A
unique SQL identifier in string form;
it can be used as a search key

PLAN_NAME

A
unique SQL plan identifier in string form;
it can be used as a search key

SQL_TEXT

The
SQL statement’s unnormalized, actual text

ORIGIN

Tells
if the SQL Plan was either:

  • AUTO-CAPTURE:
    Automatically captured
  • MANUAL-LOAD:
    Manually evolved
  • MANUAL-SQLTUNE:
    Automatically evolved by SQL Tuning Advisor
  • AUTO-SQLTUNE:
    Automatically evolved by Automatic SQL Tuning

ENABLED

Indicates
that the SQL Plan is enabled (YES) for CBO utilization or not (NO). Disabled
plans are ignored by the CBO

ACCEPTED

Indicates
that the SQL Plan is validated as a good
plan
, either because Oracle 11g has:

  • Automatically
    accepted it, or
  • The
    DBA has forced its manual acceptance by changing its status to ACCEPTED via
    procedure DBMS_SPM.ALTER_SQL_PLAN_BASELINE()

FIXED

SQL
Plans whose FIXED attribute is set to YES will be considered by the CBO. If
multiple plans are marked as FIXED, the CBO will only select the best execution
plan from those so marked

OPTIMIZER_COST

The
total cost estimated by the CBO to execute the SQL statement using this
execution plan

Another way to view the potential
impact of existing SQL Plan Baselines on a SQL statement’s execution is via the
new DISPLAY_SQL_PLAN_BASELINE procedure of package DBMS_XPLAN.
For example, I can use this procedure to view all SQL Plan Baselines that match
a SQL statement’s handle in the SMB; if I supply the SQL statement’s plan name
as well, then its execution plan will also be shown.

Automatic SQL Plan Capture: A Simple Example

To
demonstrate exactly how SQL Plan Management selects the most effective
execution plan for a SQL statement, I’ll start with an extremely simple
example: a SQL statement (see Listing
1.1
) that retrieves a relatively small subset of data from
several tables in the Sales History (SH) schema. Note that I’ve tagged
this statement with an appropriate comment (SPM_1.1) so that it’s easier to
track its state as it progresses towards an accepted plan baseline.

Now I’m
ready for experimenting with automatic capture of SQL Plan Baselines. As shown
in Listing
1.2
, I’ll set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
initialization parameter to TRUE for just this session to activate automatic
capture of SQL Plan Baselines, and I’ll set OPTIMIZER_MODE to ALL_ROWS
so that the CBO optimizes the SQL statement’s corresponding execution plan to
return the entire result set as efficiently as possible. To insure that this
session will utilize any SQL Plan Baselines that are captured, I’ve also set
initialization parameter OPTIMIZER_USE_SQL_PLAN_BASELINES to TRUE
(its default value). This parameter controls whether the CBO should check if
the next repeatable execution of the SQL statement should be evaluated for a possible
better execution plan.

Then I’ll
execute statement SPM_1.1 twice.
Its first execution insures that
the statement’s execution is logged into the SMB, but it’s the statement’s second execution that causes its execution
plan to be automatically accepted as a SQL Plan Baseline for the statement. To
show the current state of the SMB in this point of my experiments, I’ll execute
the query shown in Listing
1.3
against the DBA_SQL_PLAN_BASELINES view. As the
resulting output shows, the current statement has indeed been captured into the
SMB via automatic capture means. (Note that I’ve abbreviated the SQL
statement’s hash value and its plan baseline to their last eight characters for
easier “handling.”

Finally,
I’ll change the OPTIMIZER_MODE to FIRST_ROWS so that the CBO
optimizes the SQL statement’s corresponding execution plan to return the first
few rows of the query’s result set as soon as possible – the obvious antithesis
of the ALL_ROWS
setting, which strives for maximum throughput. Listing
1.4
shows the commands I issued to achieve this, as well as the
results of querying the DBA_SQL_PLAN_BASELINES view once again. This
query output clearly shows that both execution plans are stored within the SMB,
but plan 07e0351f
is indeed more efficient – its optimizer cost is only 757, as compared to plan ddc1fcd0’s
optimizer cost of 2388. Accordingly, SPM marks only the first plan as both ENABLED and ACCEPTED.

Finally,
Listing
1.5
shows the details of both execution plans. I’ve utilized DBMS_XPLAN’s
new DISPLAY_SQL_BASELINE
procedure to display their exact execution methods; note that
the SPM has captured the corresponding SQL Plan Baseline and all of its
optimizer statistics.

Next Steps

I’ve
introduced SQL Plan Management theory and some rudimentary examples of how to
use it effectively, but there’s still a plethora of performance tuning
scenarios to cover. In the next article in this series, I’ll delve into how to:

  • Capture SQL Plan Baselines via manual methods
  • Use SQL Plan Baselines to prepare for application
    upgrades
  • “Evolve” a SQL Plan Baseline manually

References and Additional Reading

While
I’m hopeful that I’ve given you a thorough grounding in the technical aspects
of the features I’ve discussed in this article, I’m also sure that there may be
better documentation available since it’s been published. I therefore strongly
suggest that you take a close look at the corresponding Oracle documentation on
these features to obtain crystal-clear understanding before attempting to
implement them in a production environment. Please note that I’ve drawn upon
the following Oracle Database 11gR1 documentation for the deeper technical
details of this article:

B28274-01 Oracle Database 11gR1 Performance Tuning Guide

B28279-02 Oracle Database 11gR1 New Features Guide

B28419-02 Oracle Database 11gR1 PL/SQL Packages and Types
Reference

The
following notes in Oracle MetaLink also provide valuable background information
on stored outlines in Oracle 8i and beyond:

67536.1: Using Stored
Outlines

102311.1: How to Move Stored
Outlines for One Application from One Database to Another

132547.1: Stored
Outlines Quick Reference

»


See All Articles by Columnist
Jim Czuprynski

Jim Czuprynski
Jim Czuprynski
Jim Czuprynski has accumulated over 30 years of experience during his information technology career. He has filled diverse roles at several Fortune 1000 companies in those three decades - mainframe programmer, applications developer, business analyst, and project manager - before becoming an Oracle database administrator in 2001. He currently holds OCP certification for Oracle 9i, 10g and 11g. Jim teaches the core Oracle University database administration courses on behalf of Oracle and its Education Partners throughout the United States and Canada, instructing several hundred Oracle DBAs since 2005. He was selected as Oracle Education Partner Instructor of the Year in 2009. Jim resides in Bartlett, Illinois, USA with his wife Ruth, whose career as a project manager and software quality assurance manager for a multinational insurance company makes for interesting marital discussions. He enjoys cross-country skiing, biking, bird watching, and writing about his life experiences in the field of information technology.

Latest Articles