SQL Plan Management Cheat Sheet – Part 1

This is the first in a series of blog posts on SQL plan management. I aim to answer the most common questions and address areas that commonly cause some confusion. Questions related to the following topics are not covered here; they are covered elsewhere:

  • New developments in automatic SQL plan management (automatic SPM, including real-time SPM).
  • SQL statements with multiple accepted and enabled plans – see Part 2.
  • SPM evolution – see Part 2.

SQL Plan Management

SQL plan management (SPM) keeps SQL execution plans stable, preventing them from changing unless a different plan is proven to be better. Objects called SQL plan baselines are used to enforce the use of specific execution plans. If the optimizer finds a plan different from the one encoded in a SQL plan baseline, it will be captured in the SQL plan history. SPM provides an infrastructure that captures known plans, tests and executes new plans in the SQL plan history, and ultimately manages which plans are accepted for use.

  • To control a SQL statement plan, you must capture and accept at least one known good plan. SPM does not guarantee the best performance for a SQL statement that has not been seen before.
  • SPM works with ‘repeatable’ SQL statements that are executed more than once. This generally means you need to be using bind variables and not literals in predicates. It’s also possible to use SPM with cursor sharing.

How is SQL Plan Management Licensed?

Always refer to the Database Licensing Information User Manual for your database version for the latest information. At the time of writing SQL plan management is available in Enterprise Edition and Standard Edition 2 without additional licenses. It is also available in Oracle Database 23c Free and BaseDB.

Standard Edition 2 comes with some cut-down capabilities since it is intended to deliver an alternative to (deprecated) stored outlines. SE2 capabilities are documented in the Notes column in the Table 1-6/Manageability section under the SQL Plan Management entry.

SQL Plan Baselines

SQL plan baselines can be seen in the data dictionary view dba_sql_plan_baselines:

select signature, plan_name, accepted, enabled, fixed
from   dba_sql_plan_baselines
where  accepted = 'YES';

Internally, SQL plan baseline store fully-specified hints called outlines which enforce specific SQL execution plans. You can see the execution plan encoded in a particular SQL plan baseline as follows (using plan_name from dba_sql_plan_baselines):

SELECT *
FROM   table(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE
            (plan_name=>'SQL_PLAN_9nkq1mq5nss4wec1c7603'));

You can create a SQL plan baseline for a SQL statement’s plan in a number of ways, but a quick and easy method is to create one from a SQL statement in the cursor cache:

var n number
exec :n := dbms_spm.load_plans_from_cursor_cache(sql_id=> …, plan_hash_value => … )

You can pull a plan from multiple sources using (for example) a SQL ID and plan hash value. There’s an example in GitHub, here. There’s also a new ‘quick fix’ procedure to locate the best plan known and enable it for you.

SQL Plan History

The dba_sql_plan_baselines view presents two distinct entities:

  • SQL plan baselines define what SQL execution plans are allowed for an individual SQL statements. In dba_sql_plan_baselines they are signified by the value ‘YES’ in the ACCEPTED column.
  • The SQL plan history is used to record plans found by the optimizer. For example, if there is an existing SQL plan baseline, but the optimizer finds a new and different plan, then this plan is recorded. SQL plan history entries are recorded in dba_sql_plan_baselelines where ACCEPTED=‘NO’.

It is OK to refer to a plan in the SQL plan history entry as a non-accepted SQL plan baseline.

select signature, plan_name, accepted, enabled, fixed
from   dba_sql_plan_baselines
where  accepted = 'NO';

Some SQL statements require more than one SQL execution plan (depending on bind values and data skew in the database). It is, therefore, necessary and possible for some SQL statements to have multiple accepted and enabled plans.

What does ACCEPTED and ENABLED mean?

The ACCEPTED status in dba_sql_plan_baselines signifies that a plan is approved for use. When the first plan is captured for a given SQL statement it will be marked with ACCEPTED=YES – the assumption being that it is a good plan.

The ACCEPTED status is managed by the process of SPM evolution (covered in a later post in this series). You can also force accepted status like this:

exec :report := dbsm_spm.evolve_sql_plan_baseline(…verify=>'NO'…)

Once a plan is ACCEPTED=YES, it cannot be set back to ACCEPTED=NO. Nevertheless, you can still prevent an accepted plan from being used using the ENABLED status as follows:

exec :n := dbms_spm.alter_sql_plan_baseline('SQL_add0f20cd74a969f',null,'enabled','no')
exec :n := dbms_spm.alter_sql_plan_baseline('SQL_add0f20cd74a969f',null,'enabled','yes')

If you want to manually control whether a SQL plan baseline is used, use the ENABLED flag. If you want to discard a SQL plan baseline entirely, use dbms_spm.drop_sql_plan_baseline.

What is a SQL Plan Baseline SIGNATURE?

The database uses exact matching signatures to match SQL plan baselines with SQL statements. For example:

select exact_matching_signature
from   v$sqlarea
where  sql_text like 'select col1, col2%';

EXACT_MATCHING_SIGNATURE
------------------------
     9739979040767481433

The EXACT_MATCHING_SIGNATURE column in V$SQL/V$SQLAREA can be used to join with DBA_SQL_PLAN_BASELINES.

select signature
from   dba_sql_plan_baselines
where  signature = (select exact_matching_signature
                    from   v$sqlarea
                    where  sql_text like 'select col1, col2%');

               SIGNATURE
------------------------
     9739979040767481433

Signatures are platform-independent and generated from SQL text, ignoring whitespace and SQL statement case differences so that semantically identical SQL can share the same plan baseline. It is, therefore, possible for multiple statements to match a single plan baseline, and for this reason, there is a many-to-one relationship between SQL IDs and SIGNATUREs. For example:

SQL> select sql_id, exact_matching_signature, sql_text from v$sql;

SQL_ID          EXACT_MATCHING_SIGNATURE SQL_TEXT
------------ --------------------------  -----------------------------------------------
g903r4m0zma2q        2117080876756403865 select   SUM(num) from tab1 where code = :bind1
0tzpp5x6zd4cr        2117080876756403865 select sum(num) from tab1 where code = :bind1

Can a SQL ID be used to locate the relevant SQL plan baseline?

A common question is, ‘Can I use a SQL ID to identify which SQL plan baseline it will use?’ In general, the answer is ‘no.’ The SQL ID is not stored or encoded in DBA_SQL_PLAN_BASELINES entries.

Since the SIGNATURE is the matching mechanism, consider what happens if a SQL statement similar to the two shown above is executed:

SELECT sum(num) FROM tab1 WHERE code = :bind1

This statement will have a new SQL ID, never seen in the system before, but we want it to use a SQL plan baseline if possible. The signature is 2117080876756403865 – the same as the statements above, so it will work.

A signature can be generated from SQL text, and used as the basis for matching SQL statements with its SQL plan baseline:

var signature_v number
begin
   :signature_v :=
      dbms_sqltune.sqltext_to_signature(' SELECT sum(num) FROM tab1 WHERE code = :bind1');
end;
/
select *
from   dba_sql_plan_baselines
where  signature = :signature_v;

In summary, multiple SQL IDs can share the same SQL plan baseline, and no SQL ID information is encoded in SQL plan baseline entries.

How do I know if a SQL plan baseline is being used?

The SQL_PLAN_BASLELINE column in V$SQL will contain the name of a SQL plan baseline only if the plan matches an ACCEPTED SQL plan baseline.
Similarly, the Note section in DBMS_XPLAN will name a SQL plan baseline (and only if the plan is the intended plan):

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(format=>'typical'));
SQL_ID  b72a70pvwywu2, child number 0
-------------------------------------
select sum(num),count(*) from tab1 where code = :bind1

Plan hash value: 3405812071
-------------------------------------------------------------------
| Id  | Operation                            | Name       | Rows  |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |            |       |
|   1 |  SORT AGGREGATE                      |            |     1 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TAB1       |     1 |
|*  3 |    INDEX RANGE SCAN                  | TAB1_CODEI |     1 |
-------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("CODE"=:BIND1)

Note
-----
   - SQL plan baseline SQL_PLAN_769kr0bj4gfj8a077eb5c used for this statement

Suppose an accepted, enabled SQL plan baseline is associated with a SQL statement but it’s not being used. It may mean that the SQL plan baseline is not reproducible. More on this later.

The LAST_EXECUTED column in DBA_SQL_PLAN_BASELINES tells you when a SQL plan baseline was last used to reproduce a plan successfully. Be careful when interpreting this; the LAST_EXECUTED column is refreshed (at most) once a week, which is true of the other dynamic columns in this view. Be particularly careful in assuming that there is a problem if the query in question is executed very infrequently.

The DBA_SQL_PLAN_BASELINES.REPRODUCED column reports if a SQL plan baseline plan is not reproduced for whatever reason (more on this below). This is discussed below, but it’s essential to know that the REPRODUCED column value is not reliable in all releases: it may not flip to ‘NO’ if a SQL plan baseline becomes non-reproducible. This issue was corrected in bug 30220932 (fixed in 21.1).

What does FIXED do?

You can fix SQL plan baselines as follows:

n := dbms_spm.alter_sql_plan_baseline('<sql_handle>', '<plan_name>','FIXED','YES')

You can check the current value using the DBA_SQL_PLAN_BASELINES view using the FIXED column.

Fixed set to YES labels a SQL plan baseline as special: ‘this is the plan I want to use, and I don’t want to use or discover alternative plans for this SQL statement’. FIXED SQL plan baselines will be used in preference to those that are not fixed.

If a SQL statement has more than one FIXED SQL plan baseline, then:

  • If the optimizer picks a new plan that doesn’t match any of the fixed SQL plan baselines, each fixed plan will be parsed, and the one with the lowest cost will be chosen.

I often see FIXED=YES proposed in cases where the SQL plan baseline isn’t being used because the optimizer is not using the plan encoded in the plan baseline. It is commonly thought that FIXED=YES somehow makes plan enforcement ‘stronger,’ perhaps guaranteeing or forcing a particular plan. This is not accurate. FIXED=YES tells the optimizer to choose a fixed SQL plan baseline in preference to FIXED=NO. However, the internal mechanism to enforce a particular execution plan is the same whether the plan baseline is fixed or not. If a plan will no reproduce with FIXED=YES, it will not reproduce with FIXED=NO.

If the optimizer has a choice of SQL plan baselines, you can state a preference by fixing the one you want to use (although I would generally use ENABLED instead – see below).

Fixed has another effect. If the optimizer finds a new plan for a SQL statement that is not in the plan baseline or history, a new non-accepted SQL plan baseline will be created. However, if all SQL plan baselines for a SQL statement are fixed and enabled, new plans will not be captured in the SQL plan history.

Should you use FIXED=YES?

Fixed is useful for these scenarios:

  1. A SQL statement has multiple accepted plans. You want the optimizer to use a subset, but continue to allow SPM to capture new (potentially better) plans. Even if evolve accepts new plans, the preferred, fixed ones will continue to be used. In this way you can maintain tight control over what new plans are used.
  2. You are using the automatic evolve task to manage plan evolution, but for whatever reason, you want to prevent individual SQL statements from acquiring new plans by fixing all plan baselines for specific SQL statements.

Just remember that if you are using fixed for scenario 2, you are closing the door on the potential benefit of capturing new plans. If you don’t fix all SQL plan baselines for a SQL statement, alternative plans generated by the optimizer will be stored in the SQL plan history. They won’t be used until they are accepted (by plan evolution), but one of these plans might be better than those currently in use. Data usually changes over time, and the physical database design might change. A new join order may perform better, or perhaps you want to take advantage of a new index. SPM evolution takes care of that for you.

If you want to ensure that newly captured plans are not automatically accepted by SPM auto evolve, you can do this:

BEGIN
   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK' ,
      parameter => 'ACCEPT_PLANS',
      value     => 'FALSE');
END;
/

If you prevent auto SPM evolve from accepting new plans, fixing SQL plan baselines is arguably unnecessary. Should you want to, you can control which plans are used with enable and disable instead. You will capture new plans and be able to check SPM evolve reports to see what recommendations they make.

A SQL statement is using a SQL plan baseline, but why does the plan hash value look wrong?

If you use DBMS_XPLAN to look at a plan for a SQL plan baseline, a plan hash value (PHV) will be reported:

SELECT *
FROM   table(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE
            (plan_name=> 'SQL_PLAN_5dq34f4n87kc518669c3b'));
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_5dq34f4n87kc518669c3b         Plan id: 409377851
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD-FROM-CURSOR-CACHE
Plan rows: From dictionary
--------------------------------------------------------------------------------

Plan hash value: 1775246573

The PHV is a hash generated from the SQL execution plan, so it is often used to check that SQL execution plans are the same.  Nevertheless, you might sometimes see cases where V$SQL.PLAN_HASH_VALUE is different from the PHV reported by DBMS_XPLAN for the SQL plan baseline. For example:

select plan_hash_value, sql_plan_baseline
from   v$sql
where  sql_plan_baseline = 'SQL_PLAN_5dq34f4n87kc518669c3b';

PLAN_HASH_VALUE SQL_PLAN_BASELINE
--------------- --------------------
       97405998 SQL_PLAN_5dq34f4n87kc518669c3b

You might think it is the wrong plan, but it is not the case!

The plan hash value column in V$SQL and the output from DBMS_XPLAN is the regular plan hash value (PHV), while internally, SQL plan baselines match plans using plan hash value 2 (PHV2).

The database computes PHV and PHV2 for each SQL execution plan. The algorithm used to compute PHV is sensitive to the underlying system and other minor plan differences, while the algorithm used to compute PHV2 is platform-independent and insensitive to unimportant plan differences. SPM, therefore, uses PHV2 to perform plan matching.
PHV2 for a SQL statement in the cursor cache can be seen in the OTHER_XML column of V$SQL_PLAN, and the tag looks like this:

<info type="plan_hash_2">409377851</info>

You will see 409377851 next to the “Plan Id:” label reported by DBMS_XPLAN:

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_5dq34f4n87kc518669c3b            Plan id: 409377851     <------ Here
Enabled: YES         Fixed: NO       Accepted: YES   Origin: MANUAL-LOAD-FROM-CURSOR-CACHE 
Plan rows: From dictionary
--------------------------------------------------------------------------------

The PHV2 is encoded in the plan name of the SQL plan baseline, so it isn’t directly exposed in the DBA_SQL_PLAN_BASELINES dictionary view.

I use SQL Profiles to force SQL execution plans. Aren’t they better?

Some DBAs use SQL profiles to control execution plans by loading them with outline hints from one SQL statement to another. Internally, SQL plan baselines use hint outlines to force a particular execution plan too. There is no additional ‘magic’ in SQL profiles, and you should be aware that if you are loading hints into SQL profiles, you cannot assume that the plan you get is the one you intended even if you use a full outline. The SQL profile infrastructure was created to adjust cardinality estimates using the SQL Tuning Advisor, and not designed to enforce a specific plan. For this reason, SQL profiles do not indicate if a plan is not reproduced. Whatever plan you get, you will always see a non-NULL SQL_PROFILE column value in V$SQL and a SQL profile name in the Note section of the SQL execution plan.

A SQL profile will attempt to establish a single plan, whereas you can choose to have multiple plans for an individual SQL statement with SPM. This can be beneficial in cases where there’s data skew and different plans benefit different bind values. In addition, SQL profiles provide no mechanism to ‘evolve’ a plan over time or account for changes in data volumes, data distribution, or the database schema (such as new indexes). Over time, SQL profiles may silently fail to reproduce the intended plan.

If you have created a SQL profile for a SQL statement, I recommend creating a SQL plan baseline for the SQL statement, too. Then, you can disable the SQL profile. The SQL plan baselines will allow you to fix the plan or evolve it over time, and you will be able to tell if your intended plan is not reproducing properly.

The advantage of SQL profiles is that they allow you to force-match SQL statements, applying a single SQL profile for multiple SQL statements using literal values rather than bind variables. If your SQL uses bind values, SQL plan baselines are recommended.

If you’re used to copying execution plans from one SQL statement to another with SQL profiles, see this blog post, which explains how to do something similar with SQL plan baselines.

Why is my SQL plan baseline not reproducing?

In rare cases, you may notice that a SQL plan baseline is not used when you expect it to be. If a SQL statement has an accepted and enabled SQL plan baseline, the required plan should be used, and the corresponding plan name should appear in V$SQL.SQL_PLAN_BASELINE. If this does not happen, and the term we use is ‘the SQL plan baseline plan is ‘non-reproducible.’

Potential causes are:

  1. There has been a change in the physical schema. A classic example is if an index required by the SQL plan baseline plan is dropped, the plan cannot be used. This will not raise an error. Instead, a new plan will be generated by the optimizer and saved in the SQL plan history. Until the new plan is accepted (by SPM evolution), the column V$SQL.SQL_PLAN_BASELINE will be NULL.
  2. In rare cases, a plan may become non-reproducible after a database upgrade or critical optimizer patch. Critical optimizer patches can sometimes have side effects. For example, a critical patch might prevent a particular query transformation from being used under a particular set of circumstances. This may render a SQL plan baseline plan non-reproducible.
  3. A bug prevents the outline hints stored in the SQL plan baseline from properly constraining the SQL execution plan (SQL plan baselines use outline hints, as described above in the section, I use SQL Profiles to force SQL execution plans. Aren’t they better?).
  4. Some SQL statements are not elidible for SQL plan baseline control. See the following MOS note: SQL Plan Management (SPM) – SQL Plan Baseline Restrictions/Limitations – Cases Where Plan Baselines will Not be Used (Doc ID 2308153.1)

If an accepted SQL plan baseline plan becomes non-reproducible, a new reproducible plan will be captured in the SQL plan history (with ACCEPTED=NO). This plan will be used even though it’s not accepted! The optimizer will have no choice because we don’t throw an error and break the application. In most cases, the query performance will not be significantly affected, so a pragmatic way to deal with it is to accept the new plan captured in the SQL plan history and use it in future. You can let SPM evolution accept it automatically, or evolve it manually. Here’s an example of kicking off evolve manually (or you could leave it to the auto evolve task instead):

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
--------------------------------
select col1 from table1 where id = 100

…plan data…

Note
-----
   - Failed to use SQL plan baseline for this statement

SQL> -- We will see the
SQL> select sql_handle,plan_name,accepted
     from dba_sql_plan_baselines;

SQL_HANDLE               PLAN_NAME                         ACC SQL_TEXT
------------------------ --------------------------------- --- ---------
SQL_b0820672080b6cad     SQL_PLAN_b10h6f840qv5d05ce4c2e          YES select…
SQL_b0820672080b6cad     SQL_PLAN_b10h6f840qv5d1f191f3e          NO  select…

SQL> var report clob
SQL> exec :report := dbms_spm.evolve_sql_plan_baseline –
        ('SQL_b0820672080b6cad'-
         ,verify=>'no');

SQL> select sql_handle,plan_name,accepted
     from dba_sql_plan_baselines;

SQL_HANDLE               PLAN_NAME                         ACC SQL_TEXT
------------------------ --------------------------------- --- ---------
SQL_b0820672080b6cad     SQL_PLAN_b10h6f840qv5d05ce4c2e          YES select…
SQL_b0820672080b6cad     SQL_PLAN_b10h6f840qv5d1f191f3e          YES select…

If you experience a performance degradation, there is a blog post that covers how you can discover whether reason #1 is the likely cause. You may encounter #2 in rare cases after patches or upgrades, so I recommend tuning the problem SQL statement if performance is degraded (using SQL tuning advisor, for example). If you suspect it’s #3 or want to pursue #2, it will need to be dealt with by Oracle Suppor after checking patch recommendation in MOS. In particular, you should first check the note: Things to Consider to Avoid SQL Plan Management (SPM) Related Problems on 19c (Doc ID 2774029.1).

Links