SQL Plan Management (Part 1 of 4) Creating SQL plan baselines

Introduction

Do you ever experience performance regressions because an execution plan has changed for the worse? If you have, then we have an elegant solution for you in 11g called SQL Plan Management (SPM). The next four posts on our blog will cover SPM in detail. Let's begin by reviewing the primary causes for plan changes.

Execution plan changes occur due to various system changes. For example, you might have (manually or automatically) updated statistics for some objects, or changed a few optimizer-related parameters. A more dramatic change is a database upgrade (say from 10gR2 to 11g). All of these changes have the potential to cause new execution plans to be generated for many of your SQL statements. Most new plans are obviously improvements because they are tailored to the new system environment, but some might be worse leading to performance regressions. It is the latter that cause sleepless nights for many DBAs.

DBAs have several options for addressing these regressions. However, what most DBAs want is simple: plans should only change when they will result in performance gains. In other words, the optimizer should not pick bad plans, period.

This first post in our series, describes the concepts of SQL Plan Management and how to create SQL plan baselines. The second part will describe how and when these SQL plan baselines are used. The third part will discuss evolution, the process of adding new and improved plans to SQL plan baselines. Finally, the fourth part will describe user interfaces and interactions with other Oracle objects (like stored outlines).

SQL Plan Management

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. (Click on the image for a larger view.)

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 parameter optimizer_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. Set optimizer_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 parameter use_stored_outlines is TRUE. In this case, turn on incremental capture of plans into an STS using the function capture_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.

Comments:

Hi, I enjoyed very much to read this article. Yet when tried to run the example against the SH demo schema in 11201 instance , i did NOT get the same results as you show above. In your example you got to diffrent plans for different binds. e.g : SQL> var pid number SQL> exec :pid := 100; and SQL> exec :pid := 10000; In my case i got the same plan . Also please note that i got TOTALY different number of rows. In your case just 9 rows . In the demo about half a million. SQL> var pid number SQL> exec :pid := 100; SQL>select p.prod_name, s.amount_sold, t.calendar_year from sales s, products p, times t where s.prod_id = p.prod_id and s.time_id = t.time_id and p.prod_id < :pid; ... 479520 rows selected. Could you please suggest a reason why i am getting same plan a such a big diffrences in the amout of record compering to your case ? Thanks

Posted by Yoav on March 02, 2011 at 12:33 AM PST #

Hi Yoav, Unfortunately the examples used in this blog post came from one of our internal development environment where the SH schema is not identical to the one shipped with the database. Apologies for the confusion. You should see the same plan but the row counts will be very different.

Posted by Maria Colgan on March 21, 2011 at 06:14 AM PDT #

You actually make it seem so easy with your presentation but I find this topic to be actually something which I believe I'd never understand.

Posted by wardrobe storage on April 23, 2011 at 10:11 PM PDT #

Do you recommend to have SPM "always on" as a default? I mean setting "alter system set optimizer_capture_sql_plan_baselines = true;" + running a job "dbms_spm.evolve_sql_plan_baseline" once a night, for example?

In theory things could only getting better with these settings.

Thanks.

Posted by guest on November 01, 2011 at 02:06 AM PDT #

I as well question whether it is recommended to leave "optimizer_capture_sql_plan_baselines = true". It would seem that otherwise, new SQL statements have the potential to regress as there would be no baseline for the new statement(s).

PS - Thank you Maria for your continued blog updates. I've been completely immersed lately and it is very refreshing to find such detailed and relevent content.

Posted by Steve on November 09, 2011 at 02:45 AM PST #

Setting optimizer_capture_sql_plan_baselines = true permanently will result in a SQL plan baseline being created for every repeatable SQL statement on the system. That includes all of the recursive SQL Oracle executes on your behalf as well as every 'select sysdate from dual;' or 'select * from v$sql;' the DBA may do on the system. This could lead to a very large number of SQL plan baselines being captured and an increased foot print in the SYSAUX tablespace. It may be easier to manual load plans from the cursor cache for new SQL stmts in your application.
That said I am aware of some customers who do have optimizer_capture_sql_plan_baselines = true and they have not encountered any problem other than a large SYSAUX tablespace!

Posted by Maria Colgan on November 09, 2011 at 03:09 AM PST #

Hi,

Very good blog post. And everything has been explained in concise way that how do we need to implement it.
I am having a scenario scenario where in one schema we are getting the right Explain Plan but in the other schema our plan is simply not getting the right plan? Can I use the Baseline to force it to use this plan.

Thanks..

Posted by guest on January 04, 2013 at 03:28 AM PST #

Hi sudhir,

Yes, you can use a SQL plan baseline to force the plan for an identical SQL statement in two different schemas. I discussed how this works in a recent blog post here: https://blogs.oracle.com/optimizer/entry/how_does_sql_plan_management

Thanks,
Maria

Posted by Maria Colgan on January 09, 2013 at 08:57 AM PST #

This is a great article, very informative.

The only thing that is missing are the privileges/grants you need to use SQL Plan Management if you don't have access to the SYS or SYSTEM accounts.

Kind of like giving someone instructions on how to bake a cake, but not telling them how much flour, milk and eggs to use. The instructions are there, but missing one critical piece of information ...

Posted by guest on May 08, 2013 at 05:18 AM PDT #

Information on what privileges are required to execute any supplied PL/SQL package can be found in the security section for that package in the Oracle® Database PL/SQL Packages and Types Reference.

In order to use SQL Plan Management you will need the EXECUTE privilege on the DBMS_SPM package.

To evolve a plan in an existing SQL plan baseline you will need the ADMINISTER SQL MANAGEMENT OBJECT privilege.

NOTE: any user granted the ADMINISTER SQL MANAGEMENT OBJECT privilege is able to execute any subprogram in the DBMS_SPM package.

Thanks,
Maria

Posted by Maria Colgan on May 21, 2013 at 03:00 PM PDT #

Took me time to read all the comments, but I really enjoyed the article. It proved to be Very helpful to me and I am sure to all the commenters here! Its always nice when you can not only be informed, but also entertained! Im sure you had fun writing this article.
Valuable information and excellent design you got here! I would like to thank you for sharing your thoughts and time into the stuff you post!! Thumbs up
http://www.sqlservermasters.com/

Posted by sql server masters on May 21, 2013 at 10:11 PM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

The Oracle Optimizer blog is written by members of the Optimizer development team. The goal of this blog is to provide an insight into the workings of the Optimizer and the statistics it relies on. The views expressed on this blog are our own and do not necessarily reflect the views of Oracle and its affiliates. The views and opinions expressed by visitors on this blog are theirs solely and may not reflect ours.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today