Oracle Database 11g: SQL Plan Management, Part 3

Oracle Database 11gR1 offers a new tool set, SQL Plan Management (SPM), that
lets any Oracle DBA capture and preserve the most efficient execution plans for
any SQL statement. The final article in this series illustrates how to use SPM
to limit unexpected regression of existing SQL statement performance before
those statements first encounter Oracle 11g’s cost-based optimizer. It also
reveals several SQL Plan Management features that allow extremely granular
management of the SQL Management Base (SMB).

The previous
in this series explored how Oracle Database 11g’s new SQL Plan Management (SPM) features can be
used to capture and load SQL Plan Baselines for Oracle 10g databases about to
be upgraded to Oracle 11g so that SQL statement regression is eliminated during
the database upgrade process. That article also illustrated methods to ensure
that SQL statements for new application code would choose the most efficient
execution plans even before the
application is deployed in production.

article – the final one in this series – will show how to use SPM to:

  • Limit
    unnecessary regression
    of SQL statements when they attempt to take
    advantage of new Oracle 11g optimizer features
  • Capture SQL
    Plan Baselines for specific SQL
    statements via manual methods
  • Control the
    of existing SQL Plan Baselines
  • Manage the
    of the SQL Plan
    Management Baseline
    (SMB), including how to purge obsolete or undesired SQL Plan

SPM Scenario #3: Using SQL Performance Analyzer (SPA) Against Prior Optimizer Versions

The first
  in the prior article
illustrated how SPM could be used to capture SQL Plan Baselines for SQL
statements whose performance would regress during an impending upgrade from
Oracle 10g to Oracle 11g. That scenario involved actually executing the
statements that made up the SQL Workload on an existing Oracle 10g database. An
alternate method to solve this issue is to simulate an Oracle 10g environment
in an existing Oracle 11g database environment by manipulating the value for
initialization parameter.

Preparing the Simulation. To illustrate
this scenario, I’ll utilize the same SQL statements that I captured earlier
into SQL Tuning Set STS_SPM_200 as part of SPM Scenario
in the previous article. Before I perform any new analyses, however,
I’ll first remove any SQL Plan Baselines from the SQL Management Base (SMB)
that were created during prior analyses. I’ll use function DBMS_SPM.DROP_SQL_PLAN_BASELINE
to remove just those statements that are “tagged” with a comment string of SPM_2
(see Listing
). Then I’ll prepare a new SQL
Performance Analyzer
(SPA) task named SPA_SPM_300 that will analyze the
performance of the SQL workload in the STS_SPM_200 SQL Tuning Set (see Listing

Analyzing SQL Workloads. Next, I’ll aim
SPA task SPA_SPM_300
at my database to evaluate simulated workload performance between a 10gR2 and
an 11gR1 Oracle database environment. I’ll first clear my Oracle 11g’s library
cache and database buffer cache to insure a clean starting point for
performance evaluation. I’ll then set initialization parameter OPTIMIZER_FEATURES_ENABLE
to a value of
to “fool” the optimizer into believing it’s an Oracle 10g database. Finally,
I’ll analyze the workload in that mode by performing a test execution of the SPA_SPM_300
analysis task. Once this sequence is completed, I’ll then repeat the same
analysis after setting OPTIMIZER_FEATURES_ENABLE to a value of
so that SPA will evaluate the workload in an Oracle 11g database environment.
The code I used to do this is shown in Listing

Comparing Relative Workload Performance.
Once the two workload test executions are complete, my next task is to
determine if there are any SQL statements whose performance would have
regressed because the optimizer setting has changed. I used the code in Listing
to perform a comparison between the two workload simulations
and then generate a report of any SQL statements which had poorer performance.
To show the flexibility of SQL Performance Analyzer here, I’ve eschewed using a
relative change in optimizer cost
as my metric; instead, I’ve chosen to compare statements based on estimated execution time.

Capturing SQL Plan Baselines for Regressing Statements.
As the resulting analysis report shows, two statements would be negatively
impacted by the simulated upgrade of optimizer features from
I’ll capture the execution plans for these statements into SQL Plan Baselines.
This will prevent the CBO from running these SQL statements using the 11g
optimizer settings, which would cause deleterious performance for those
statements. The code in Listing
illustrates how to accomplish this.

Controlling SQL Plan Evolution

11g has coined the term SQL plan evolution
to describe the progressive search for the best execution plan during a SQL
statement’s execution. As I outlined in the first
in this series, whenever SPM intercepts a new execution plan
for a SQL statement that already exists in the SMB, then SPM saves that plan
within in the SMB as part of that SQL statement’s history; however, SPM doesn’t permit the plan to be used until it’s been evaluated for
possible improved performance. If SPM determines that the new plan does improve
performance, SPM will then change the plan’s status to ACCEPTED.
This concept, also known as plan
is at the heart of SPM’s automatic evolution of SQL
statements within the SMB.

demonstrate these concepts, I’ve created a simple SQL statement (SPM_3_1.sql) that queries several
tables in the Sales History schema. To create a clean slate against which SPM
will evaluate this query, however, I’ll first clear any SQL Plan Baselines from
the SMB that are tagged with a comment string of SPM_ and flush both the Library
Cache and Database Buffer Cache, as shown in Listing
. Once that’s done, I’ll use the code shown in Listing
to deactivate automatic SQL Plan Baseline capture by setting
at a session level, execute the
query, and then capture the resulting SQL Plan Baseline manually from the
Library Cache with function DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE.

Bind Value Peeking and Adaptive Cursor Sharing. I’ve
deliberately used bind variables in SQL statement SPM_3_1.sql to illustrate how SQL
Plan Management interacts with bind variable
The cost-based optimizer has been able to take advantage
of this feature since Oracle Release 9.0.1, and it lets the CBO determine a
more accurate execution plan based on the first set of values that are supplied
for the bind variables.

11g also introduces a new feature called Adaptive
Cursor Sharing
that interacts extremely effectively with bind
variable peeking and shared cursors. The bottom line here is that SPM will
resolve any conflicts between these two features by marking the very first
execution plan that’s captured as the corresponding SQL Plan Baseline. If I
re-execute the same query but use different values for the bind variables, it’s
possible that the resulting execution plan could be much less effective than
the original plan. To prevent this, SPM still stores the execution plan as part
of the SQL statement’s plan history
in the SMB, but the new plan won’t be used until SPM verifies it as a better plan. (I’ll expand this discussion
in a later article that will focus on how Adaptive Cursor Sharing works.)

Requesting SQL Plan Baseline Evolution. On
the other hand, what will happen when SPM detects a better plan due to
increased performance? I’ll simulate this scenario by adding an index on the CUST_LAST_NAME
column of the SH.CUSTOMERS
table. This index will have a dramatically positive effect on the performance
of SQL statement SPM_3_1.sql because a full-table scan of the SH.CUSTOMERS
table is no longer required to provide the result set. Once I’ve reactivated
automatic SQL Plan Baseline capture by setting initialization parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE
back to its default value (TRUE) at a session
level, I’ll execute the statement again and verify the resulting SQL Plan
Baseline that is captured. The CBO will compile a new execution plan that will
utilize the new index to dramatically improve
the statement’s performance, and the new execution plan will also be logged in
the SMB for this statement (see Listing

SPM evolves this new plan,
however, the plan will not be utilized by the statement’s SQL Plan Baseline.
Oracle 11g will immediately evaluate
all available SQL Plan Baselines and evolve any that result in improved
performance. This function returns a CLOB that lists any SQL Plan Baselines that
have been changed to an accepted status as a result of plan evolution. I’ve
shown the results of this plan’s evolution in Listing
And to
illustrate that the improved and evolved SQL Plan Baseline will now be utilized
whenever this SQL statement is executed, I’ve also run an EXPLAIN PLAN
for the statement and formatted the resulting output with the +NOTES directive.

Automatic Plan Evolution Via SQL Tuning Advisor.
Oracle 11g also provides for automatic evolution of SQL Plan Baselines via the
execution of the SQL Tuning Advisor
for any selected SQL statements. And
since the Automatic SQL Tuning (AST) batch process
also invokes the SQL Tuning Advisor during its regularly scheduled nightly run,
AST can also recommend the acceptance of a SQL Profile whenever that Profile
offers better performance than the current SQL Plan Baseline. (For more
information on Automatic SQL Tuning, see this

Modifying SQL Plan Baseline Attributes

Oracle 11g permits extremely granular
control of the status and availability of captured SQL Plan Baselines,
regardless of their origin. To illustrate, I’ve prepared three SQL statements
(see and tagged them all with a similar comment for easy identification (see SPM_3_2.sql).
Once executed, I captured their execution plans directly from the Library
Cache, loading them into the SMB as SQL Plan Baselines using DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE.

to change various attributes of each SQL Plan Baseline. These modifiable
attributes include:

    Setting this attribute to its non-default value of NO tells Oracle 11g to
    temporarily disable a plan. A SQL
    Plan must be marked as both ENABLED and ACCEPTED, or the CBO will ignore
  • FIXED.
    It’s possible that a SQL Plan Baseline could actually reference more than one
    execution plan. When this attribute is set to its non-default value of YES
    for at least one plan of a SQL Plan Baseline, that one plan will be the only
    one chosen by the optimizer, even if a plan
    with a potentially lower cost exists.
    This give the DBA the ability
    to override the default behavior of the SMB, and it’s especially useful for
    transforming a stored outline into a stable SQL Plan Baseline. Note that when a
    new plan is added to a SQL plan baseline that is currently FIXED,
    the new plan can’t be utilized until it’s declared as FIXED.
    Setting this attribute to its non-default value of NO tells Oracle 11g to retain it indefinitely, thereby exempting from the SMB’s automatic purging mechanism.

The code shown in Listing
shows how I set up these SQL statements for capture and how
I later modified these SQL Plan Baseline attributes. Oracle 11g actually
supplies four overloaded methods to execute this function. The method I’ve just
demonstrated is probably the most flexible, since it accepts one of several SPM
attributes whose values can be filtered. The other three methods accept a
combination of:

  • A SQL ID,
    a SQL Plan Baseline hash value,
    and a simple filter against the
    statement’s SQL text
  • A SQL ID,
    a SQL handle, and a SQL Plan Baseline hash value
  • A SQL ID
    and a SQL Plan Baseline hash value

Controlling Automatic SMB Management Features

11g also offers two automatic management features that help keep the relative
size and content of the SMB under tight control.

SMB Space Management. Once per week, Oracle
checks if the size of the SMB has exceeded its space budget limit (default
value: 10% of the SYSAUX tablespace), and if that limit is exceeded, a warning
is recorded in the database’s alert log. Space warnings will continue until

  • More space is allocated to SYSAUX; or
  • The space limit is increased from its default
    value; or
  • SQL plan baselines or SQL profiles are purged from
    the SMB.

SMB Automatic Purging. Once per week, an
automated task runs against the SMB and purges all SQL Plans that have not been
used within the unused plan retention period. This period defaults to 53 weeks
but can be adjusted to a value between 5
and 523 weeks (i.e. about 10

These limits can be set using
procedure DBMS_SPM.CONFIGURE, and their current values can
be queried in the DBA_SQL_MANAGEMENT_CONFIG data dictionary view.
I’ve demonstrated how to modify the default settings for SMB space management
and automatic purging in Listing

Managing SQL Plan Baselines with Enterprise Manager

articles have concentrated on demonstrating script-based methods for managing
SQL Plan Baselines. However, I’d be remiss if I didn’t mention that Oracle 11g
Enterprise Manager Database Control also offers an excellent interface for
reviewing and managing the contents of the SMB. Figure 3.1 below shows the results of selecting the SQL Plan
link on the Server

Figure 3-1. SQL Plan Baseline Management Home Panel.

This interface makes it possible to
easily perform any of the following SPM functions without any complex PL/SQL

  • Enable
    or disable a SQL Plan Baseline
  • Evolve
    a better plan for a selected SQL Plan Baseline
  • Drop
    an existing SQL Plan Baseline
  • Pack
    a SQL Plan Baseline’s contents into a staging
  • Unpack
    a staging table into a SQL Plan Baseline

the example in Figure 3.1 illustrates, it’s also possible to limit the list of
SQL Plan Baselines displayed on this screen.


this article series has demonstrated, Oracle 11g’s new SQL Plan Management
(SPM) features offer an Oracle DBA the power to guarantee that a SQL statement
will have access to the absolutely best possible execution plan(s), and this
offers a chance to stabilize the performance of the SQL statement even before it’s ever executed in a production
environment. SQL Plan Baselines can be captured from multiple sources – even
from existing Oracle 10g databases – and can be pre-loaded for new applications
to ease the potential disruption to smooth deployment. Finally, SPM offers
several methods to influence which SQL Plan Baselines the cost-based optimizer
will choose for any individual statement, maintain the current list of SQL Plan
Baselines, and either automatically or manually purge any SQL Plan Baselines
that the DBA may deem are no longer desirable for future optimal SQL statement

References and Additional Reading

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


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