Insights into Statistics, Query Optimization and the Oracle Optimizer

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

Maria Colgan
Distinguished Product Manager


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 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 11g to 12c). 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 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;

--------- ----------- -------------
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;

--------- ----------- -------------

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.


For more:


Join the discussion

Comments ( 16 )
  • Yoav Wednesday, March 2, 2011
    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;
    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 ?
  • Maria Colgan Monday, March 21, 2011
    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.
  • wardrobe storage Sunday, April 24, 2011
    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.
  • guest Tuesday, November 1, 2011

    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.


  • Steve Wednesday, November 9, 2011

    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.

  • Maria Colgan Wednesday, November 9, 2011

    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!

  • guest Friday, January 4, 2013


    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.


  • Maria Colgan Wednesday, January 9, 2013

    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



  • guest Wednesday, May 8, 2013

    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 ...

  • Maria Colgan Tuesday, May 21, 2013

    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.



  • sql server masters Wednesday, May 22, 2013

    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


  • guest Monday, April 27, 2015

    Nice to sharing information about oracle sql. how many modules are there in oracle?

  • Alexander Thursday, January 19, 2017

    I am neither SYS nor SYSDBA

    I do have ADMINISTER SQL MANAGEMENT OBJECT privilege and

    I do execute DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE procedure,

    but instead of evolving particula PLAN I am getting either:

    Plan already Accepted (when it is) or

    ORA-01031: insufficient privileges (when it is not).

    Any advises?

  • Antony Saturday, January 28, 2017


    "Statements that are only executed once will not benefit from SQL plan baselines since accepted plans are only used in subsequent hard parses". Does this mean that the optimizer may choose a wrong plan from SPM when cursor is aged out from shared pool and requires at least two executions to choose a right plan from SPM?



  • NigelBayliss Monday, January 30, 2017

    Hi Antony,

    SPM matches the text stored in a SQL plan baseline with the text of the SQL statement you're parsing (this is a slight simplification, but that's the general idea). If there's a match, then a plan stored in the SQL plan baseline is a candidate to be used. If a brand new and unique SQL statement is executed just once, then by definition there won't be a SQL plan baseline to match because the SQL has never been seen before. The idea behind SPM it to capture SQL plan baselines for SQL statements that are parsed and executed many times, then it makes sense to consistently enforce known "good" SQL exectution plans (that are stored in SQL plan baselines). It doesn't matter if the cursor has been aged out - when it's parsed again, the text of the SQL statement can still be matched with a stored SQL plan baseline if one exists for it.



  • NigelBayliss Tuesday, February 21, 2017

    Hi Alexander,

    Re: "ORA-01031: insufficient privileges"

    I have created bug 25541781 to track this. There are apparently some priv issues with SPM evolve in Oracle Database 12c. Firstly, the ADVISOR priv needs to be granted - and this is not mentioned in the docs (I have bugged this).

    In addition to ADVISOR, there's the following set of grants, but it's not a viable work-around for most:

    grant select on sys.sqlobj$plan to youruser;

    grant select on sys.sqlobj$data to youruser;

    grant select on sys.sqlobj$ to youruser;

    grant select on sys.sqlobj$auxdata to youruser;

    grant select on sys.sql$text to youruser;

    Also, V_$SQL if you want to search for SQL IDs and then SELECT on the tables you're evolving.

    Thanks for raising this,


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.