11g new feature 之 SPM

 

What is SPM?

SQL Plan Management (SPM) allows database users to maintain stable yet optimal performance for a set of SQL statements. SPM incorporates the positive attributes of plan adaptability and plan stability, while simultaneously avoiding their shortcomings.
It has two main objectives:
1.prevent performance regressions in the face of database system changes
2.offer performance improvements by gracefully adapting to database system changes

A managed SQL statement is one for which SPM has been enabled. SPM can be configured to work automatically or it can be manually controlled either wholly or partially (described later). SPM helps prevent performance regressions by enabling the detection of plan changes for managed SQL statements. For this purpose, SPM maintains, on disk, a plan history consisting of different execution plans generated for each managed SQL statement. An enhanced version of the Oracle optimizer, called SPM aware optimizer, accesses, uses, and manages this information which is stored in a repository called the SQL Management Base (SMB).
The plan history enables the SPM aware optimizer to determine whether the best-cost plan it has produced using the cost-based method is a brand new plan or not. A brand new plan represents a plan change that has potential to cause performance regression. For this reason, the SPM aware optimizer does not choose a brand new best-cost plan. Instead, it chooses from a set of accepted plans. An accepted plan is one that has been either verified to not cause performance regression or designated to have good performance. A set of accepted plans is called a SQL plan baseline, which represents a subset of the plan history.
A brand new plan is added to the plan history as a non-accepted plan. Later, an SPM utility verifies its performance, and keeps it as a non-accepted plan if it will cause a performance regression, or changes it to an accepted plan if it will provide a performance improvement. The plan performance verification process ensures both plan stability and plan adaptability.
The figure below shows the SMB containing the plan history for three SQL statements. Each plan history contains some accepted plans (the SQL plan baseline) and some non-accepted plans.

You can create a SQL plan baseline in several ways: using a SQL Tuning Set (STS); from the cursor cache; exporting from one database and importing into another; and automatically for every statement. Let's look at each in turn. The examples in this blog entry use the Oracle Database Sample Schemas so you can try them yourself.

Creating SQL plan baselines from STS

If you are upgrading from 10gR2 or have an 11g test system, you might already have an STS containing some or all of your SQL statements.
This STS might contain plans that perform satisfactorily. Let's call this STS MY_STS. You can create a SQL plan baseline from this STS as follows:

SQL> variable pls number;
SQL> exec :pls := dbms_spm.load_plans_from_sqlset(sqlset_name => 'MY_STS', -> basic_filter => 'sql_text like ''select%p.prod_name%''');

This will create SQL plan baselines for all statements that match the specified filter.

 

Creating SQL plan baselines from cursor cache

You can automatically create SQL plan baselines for any cursor that is currently in the cache as follows: 

SQL> exec :pls := dbms_spm.load_plans_from_cursor_cache( ->   attribute_name => 'SQL_TEXT',
->   attribute_value => 'select%p.prod_name%'); 

This will create SQL plan baselines for all statements whose text matches the specified string. Several overloaded variations of this function allow you to filter on other cursor attributes.

 

Creating SQL plan baselines using a staging table

If you already have SQL plan baselines (say on an 11g test system), you can export them to another system (a production system for instance). First, on the test system, create a staging table and pack the SQL plan baselines you want to export:

SQL> exec dbms_spm.create_stgtab_baseline(table_name => 'MY_STGTAB', -> table_owner => 'SH');

PL/SQL procedure successfully completed. 

SQL> exec :pls := dbms_spm.pack_stgtab_baseline( -> table_name => 'MY_STGTAB', -> table_owner => 'SH', -> sql_text => 'select%p.prod_name%');

This will pack all SQL plan baselines for statements that match the specified filter. The staging table, MY_STGTAB, is a regular table that you should export to the production system using Datapump Export. 

On the production system, you can now unpack the staging table to create the SQL plan baselines: 

SQL> exec :pls := dbms_spm.unpack_stgtab_baseline( -> table_name => 'MY_STGTAB', -> table_owner => 'SH', ->  sql_text => 'select%p.prod_name%'); 

This will unpack the staging table and create SQL plan baselines. Note that the filter for unpacking the staging table is optional and may be different than the one used during packing. This means that you can pack several SQL plan baselines into a staging table and selectively unpack only a subset of them on the target system.

 

Creating SQL plan baselines automatically

You can create SQL plan baselines for all repeatable statements automatically by setting the parameteroptimizer_capture_sql_plan_baselines to TRUE (default is FALSE). The first plan captured for any statement is automatically accepted and becomes part of the SQL plan baseline, so enable this parameter only when you are sure that the default plans are performing well. 

You can use the automatic plan capture mode when you have upgraded from a previous database version. Setoptimizer_features_enable to the earlier version and execute your workload. Every repeatable statement will have its plan captured thus creating SQL plan baselines. You can reset optimizer_features_enable to its default value after you are sure that all statements in your workload have had a chance to execute. 

Note that this automatic plan capture occurs only for repeatable statements, that is, statements that are executed at least twice. Statements that are only executed once will not benefit from SQL plan baselines since accepted plans are only used in subsequent hard parses. 
The following example shows a plan being captured automatically when the same statement is executed twice: 

SQL> alter session set optimizer_capture_sql_plan_baselines = true;

Session altered.
SQL> var pid number
SQL> exec :pid := 100;
PL/SQL procedure successfully completed.
SQL> select p.prod_name, s.amount_sold, t.calendar_year
2    from sales s, products p, times t
3    where s.prod_id = p.prod_id
4      and s.time_id = t.time_id
5      and p.prod_id < :pid;

PROD_NAME AMOUNT_SOLD CALENDAR_YEAR
--------- ----------- -------------
...
9 rows selected.

SQL> select p.prod_name, s.amount_sold, t.calendar_year
2    from sales s, products p, times t
3    where s.prod_id = p.prod_id
4      and s.time_id = t.time_id
5      and p.prod_id < :pid;

PROD_NAME AMOUNT_SOLD CALENDAR_YEAR
--------- ----------- -------------
...
9 rows selected.

SQL> alter session set optimizer_capture_sql_plan_baselines = false;
Session altered

Automatic plan capture will not occur for a statement if a stored outline exists for it and is enabled and the parameteruse_stored_outlines is TRUE. 

In this case, turn on incremental capture of plans into an STS using the functioncapture_cursor_cache_sqlset() in the DBMS_SQLTUNE package. 

After you have collected the plans for your workload into the STS, manually create SQL plan baselines using the method described earlier. 

Then, disable the stored outlines or set use_stored_outlines to FALSE. 

From now on, SPM will manage your workload and stored outlines will not be used for those statements. In this article, we have seen how to create SQL plan baselines. 

In the next, we will describe the SPM aware optimizer and how it uses SQL plan baselines.