Understanding SQL Plan Baselines in Oracle Database 11g

What is one
of the most challenging aspects of performance tuning in an Oracle database? There
are probably a fair number of Oracle DBAs that will tell you dealing with the
performance of SQL in their databases is one of the biggest challenges they encounter.
SQL statement tuning is often painstaking and can be a very time consuming
activity to say the least. Then, once properly tuned, making sure the most
resource intensive statements stay that way is yet another hurdle that is faced
by DBAs.

The cost based optimizer will generate new execution plans for statements that already
have a plan cached in the library cache because of a variety of changes in the
database. Sometimes, these new plans perform worse than the plan(s) cached in
memory. The list of actions or changes in the database that cause execution
plans to be regenerated include upgrades or patches to the optimizer, update
optimizer statistics, DDL changes that affect the objects being accessed in the
statement, system setting changes and the creation of SQL profiles. All of
these are actions commonly performed in regular database maintenance
operations.

Oracle has
introduced several major features over several releases that are, or have been,
designed to help the DBA manage and control the execution plans that the
optimizer generates for repetitive statements run in our databases. These
features include stored outlines, SQL Profiles and most recently with Oracle
11g, SQL Plan Baselines.

The drawback
to stored outlines and SQL profiles is that they are reactive in nature and may
require maintenance and updating. SQL Plan Baselines on the other hand are much
more automated, and more proactive in their behavior. New plans can only be
added to the SQL Plan Baseline if they will not result in a slower performance,
and only plans that are actually in the SQL Plan Baseline are used by optimizer
to generate the plans to process statements.

SQL Plan
Baselines are a new feature in Oracle 11g that helps to prevent repeatedly used
SQL statements from regressing because a newly generated execution plan is less
effective than what was originally in the library cache. It should be noted
however, that SQL Plan Baselines do not help if the action taken in the
database is so significant that the execution plan absolutely must change, for
example if a previously used index is dropped.

Defining SQL Plan Baselines

Defined, a
SQL Plan Baseline is a set of one or more "accepted" plans that
contain hints, the plan hash value and other plan related data. In addition to
the actual baseline, Oracle also maintains a SQL Plan History. This history is
a list of all execution plans generated for a statement, including those that
have and have not been moved into the SQL Plan Baseline. Acceptable execution
plans are moved from the SQL Plan History into the SQL Plan Baseline, which is
referred to as evolving the plan. The baseline and history are maintained in
the SQL Management Base (SMB), which is kept in tables in the SYSAUX
tablespace. Included in the SMB is also any related SQL profiles for the
statements.

The diagram
that follows shows the relationship between the SMB, Plan History and Plan
Baseline. A statement has had three different execution plans generated. Plan
1 and Plan 2 have performed well, and have been accepted into the baseline.
Plan 3 was also generated, however it did not perform as well as the other two,
and therefore, while it is part of the history, it is not a baseline plan.

The relationship between the SMB, Plan History and Plan Baseline

Before a plan
in the SQL Plan Baseline can be used or selected by the optimizer, the SQL
Baseline must be initialized with at least one accepted plan for the repeatable
statements being run. The two activities that populate the SQL Plan Baselines
are capturing and evolving. Capturing is the initial load of plans into the
baseline, evolving is the evaluation of new plans in the SQL History to ensure
they will not cause the statement to regress and then adding them to the SQL
Baseline.

Capturing SQL Plan Baselines

During the
capture phase, Oracle maintains a log of the SQL ID for statements executed
against the database. If a statement is parsed or executed after it was initially
logged, it is considered a repeatable statement. For each of these statements,
the SQL History is created and new plans (if generated) are added to it. There
are two ways to initiate the capture, automatically and manually.

To do an
automatic load, change the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to
TRUE rather than it’s default of FALSE. This is a dynamic parameter, so the
capture process can be quickly started and then stopped if necessary. During
automatic capture, the first plan generated for any statement is flagged as
accepted and placed into both the SQL History and the SQL Baseline. Any
subsequent execution plans are placed only in the history.

Manual
loading of the initial SQL Baseline is accomplished by using the DBMS_SPM
package supplied with Oracle. There are two functions that load SQL plans.
These are LOAD_PLANS_FROM_SQLSET and LOAD_PLANS_FROM CURSOR_CACHE. Any plans
that are manually loaded are placed into the SQL Plan Baseline.

DBMS_SPM also
has a procedure that will migrate stored outlines that may already exist for
any of the high-load statements into the SQL Plan Baseline. This function is
called MIGRATE_STORED_OUTLINE and was introduced as a part of Oracle Database
11g R2. This means, the work done previously to create stored outlines can now
be used to initially seed the SQL Plan Baselines, which is a nice feature.

For more
details on running these procedures, refer to DBMS_SPM
Documentation.

Evolving SQL Plan Baselines

If the
optimizer generates a new plan for a repeated SQL statement, it is
automatically added to the SQL Plan History. However, it is not automatically
added to the baseline. In order for a new plan to be added to the SQL Plan Baseline,
it must be "evolved" or verified first. Once again, there are several
methods for evolving a plan from the history into the baseline.

The Automatic
SQL Tuning job (also new in 11g) that is run as an automatic task during the
maintenance window will automatically verify plans that have been added into
the SQL Plan History. Verification simply means that the plans are checked to
ensure that the new plan will not result in a performance degradation or
regression. This is an out-of-the-box feature that focuses on high-load statements
because these are the statements that the Automatic SQL Tuning job selects to
analyze.

In addition,
manually running the SQL Tuning Advisor may result in plans being added to the
SQL Plan Baseline. If the SQL Tuning Advisor yields a recommendation to create
and use a SQL Profile, if that profile is accepted, the corresponding plan is
automatically added to the baseline.

The
ALTER_SQL_PLAN_BASELINE function of DBMS_SPM can be used to change the status
of plans in the SQL History to Accepted, which in turn moves them into the SQL
Baseline and the EVOLVE_SQL_PLAN_BASELINE function of the DBMS_SPM package can
be used to see which plans have been evolved.

Fixing SQL Plan Baselines

Another
option that can be used with SQL Plan Baselines is to mark one (or more)
specific plan as a fixed baseline. If a fixed plan exists in a baseline, the
optimizer will give priority to that fixed plan, even if a better, lower cost
plan is available.

This would
enable a DBA to essentially guarantee (or at least make most likely) a very
specific plan to be used by optimizer. However, there are some factors to be
aware of, if this approach is used. First, the optimizer will not add new
plans to the history if there is a fixed baseline and the
EVOLVE_SQL_PLAN_BASELINE function will not evolve plans either. New plans in
the SQL Plan Baseline would have to be added by manually loading them from the
SQL Cache or a SQL Tuning set. Second, there is a possibility that a better
execution plan will be ignored because it has not been marked as fixed.

A plan can be
marked as fixed by using the ALTER_SQL_PLAN_BASELINE function of DBMS_SPM and
changing the FIXED attribute to a value of YES.

DBA_SQL_PLAN_BASELINES Dictionary View

This is the
main dictionary view used to see information about the SQL Plan Baselines.
There are several columns that are of particular interest:

Selecting SQL Baseline Plans

Once SQL Plan
Baselines are in place, additional steps are taken to evaluate whether or not an
execution plan from the SQL Plan Baseline should be used for a repeatable
statement run in the database. In order for Oracle to replace a generated SQL
Plan with a plan from the SQL Plan Baseline, the
OPTIMIZER_USE_SQL_PLAN_BASELINES must be set to TRUE (which is the default
value).

For each
statement that is run, the following steps take place.

Is OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE?
 
	YES?  Is the plan part of the SQL History?
 
		YES?  Is the plan part of the SQL Baseline?
 
		YES?  Use the plan generated

		NO?    Replace with the best SQL Baseline Plan 
 
	NO?    Add the plan to the SQL History and use the best SQL Baseline Plan
 
NO?    Use the plan generated

Controlling SQL Baseline Space Usage

The SQL
Management Base (SMB) area allocated in the SYSAUX tablespace is controlled by
two settings, and an automatic purge activity takes place to remove any plans
that have not been used within a designated period of time.

By default,
the SMB is limited to no more than 10 percent of the SYSAUX tablespace. This
can be adjusted by using the DBMS_SPM.CONFIGURE(‘SPACE_BUDGET_PERCENT’,nn);
command. The value of nn can be from 1percent to 50percent. If the space is
exceeded, warnings are generated to the alert log on a weekly basis until the space
issue is resolved by adjusting the percentage, changing the size of SYSAUX, or
plans are purged.

Plans are
purged on a weekly basis. By default, any plan that has not been used in 53
weeks will be automatically purged. This is also an adjustable value and is
changed with the DBMS_SPM.CONFIGURE(‘PLAN_RETENTION_WEEKS’,nnn); procedure.
Valid values are from 5 to 523 weeks. The LAST_EXECUTED column of
DBA_SQL_PLAN_BASELINES will indicate which plans might be up for purging in the
near future.

In addition,
plans can be manually removed from the SMB using the
DBMS_SPM.DROP_SQL_PLAN_BASELINE function.

Wrapping Up

Additional
Information on SQL Plan Baselines is available through the website listed
above, including how to export/import plans from one database to another using
DataPump, and more details on migrating stored outlines into SQL Plan
Baselines.

Overall, SQL
Plan Baselines may prove to be a very useful tool for DBAs by providing a
benefit that will actually enhance the general stability of SQL performance by
avoiding the use of new execution plans that will result in a slower, regressed
query result. An additional benefit may be found in reducing the time and
effort that DBAs have to spend doing SQL Statement tuning.

»


See All Articles by Columnist
Karen Reliford