X

Insights into Statistics, Query Optimization and the Oracle Optimizer

  • July 21, 2017

Are my SQL plan baselines being re-enabled?

Nigel Bayliss
Product Manager

A number of times people have said to me something along the lines of, “Our SQL plan baselines keep getting re-enabled. Why is that?”. For some time I’ve been puzzled by this question because the database doesn't automatically re-enable SQL plan baselines once they've been disabled. Nevertheless, it gradually dawned on me that there is probably a misunderstanding behind it. That's what this post is about.

Consider the following SQL execution plan; it has an associated SQL plan baseline:

Execution Plan #1

select /* SPMTEST */ sum(n) from sales where region_id = 10

------------------------------------
| Id  | Operation          | Name  |
------------------------------------
|   0 | SELECT STATEMENT   |       |
|   1 |  SORT AGGREGATE    |       |
|   2 |   TABLE ACCESS FULL| SALES |
------------------------------------

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

The "Note" section above shows that we are using a SQL plan baselines. Let’s disable it:

ret := dbms_spm.alter_sql_plan_baseline
       (plan_name=>'SQL_PLAN_b8dhx84x2wh9n35032dee', 
        attribute_name=>'ENABLED',  
        attribute_value=>'NO') ;

You can see that our SQL plan baseline has ENABLED=NO:

SQL> SELECT plan_name, sql_text, enabled, accepted
  2  FROM   dba_sql_plan_baselines;

PLAN_NAME                      SQL_TEXT                          ENABLED  ACCEPTED
------------------------------ --------------------------------- -------- --------
SQL_PLAN_b8dhx84x2wh9n35032dee select /* SPMTEST */ sum(n)       NO       YES
                               from sales where region_id = 10

The SQL execution plan for this statement is no longer constrained. The data in the table will change over time and at some point we might get a new plan. For the purposes of this example, imagine that this business sells to more and more regions and eventually the optimizer establishes that the index on region_id is a favorable access path:

Execution Plan #2

select /* SPMTEST */ sum(n) from sales where region_id = 10

---------------------------------------------------------
| Id  | Operation                            | Name     |
---------------------------------------------------------
|   0 | SELECT STATEMENT                     |          |
|   1 |  SORT AGGREGATE                      |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SALES    |
|   3 |    INDEX RANGE SCAN                  | SALES_IX |
---------------------------------------------------------

Our SQL plan baseline is disabled, so the optimizer is not constrained; it is free to use this new plan. There is no “Note” section either so we can be sure that we are not using a SQL plan baseline. At this point, everything is as you might expect: the SQL plan baseline is disabled so the execution plan is not constrained in any way. Nevertheless, a few days later the DBA notices this:

Execution Plan #3

---------------------------------------------------------
| Id  | Operation                            | Name     |
---------------------------------------------------------
|   0 | SELECT STATEMENT                     |          |
|   1 |  SORT AGGREGATE                      |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SALES    |
|   3 |    INDEX RANGE SCAN                  | SALES_IX |
---------------------------------------------------------

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

At first glance you can be forgiven for thinking that the SQL plan baseline has been “switched back on”. Not quite – take a look at the SQL plan baseline name – it is different.

So what happened? Firstly, bear in mind that when you disable a SQL plan baseline you disable an individual SQL plan baseline and not the ability of a particular SQL statement to acquire new SQL plan baselines. When the optimizer sees a new SQL execution plan for a SQL statement it will create a new SQL plan baseline if a SQL plan baseline for the statement already exists. It does this even if auto capture is disabled (optimizer_capture_sql_plan_baselines=FALSE) and even if existing SQL plan baselines associated with the statement are disabled. If we had taken a look at SQL plan baselines the moment after the new “index plan” was chosen by the optimizer (execution plan #2), this is what you would have seen:

SQL> SELECT plan_name, sql_text, enabled, accepted
  2  FROM   dba_sql_plan_baselines;

PLAN_NAME                      SQL_TEXT                          ENABLED  ACCEPTED
------------------------------ --------------------------------- -------- --------
SQL_PLAN_b8dhx84x2wh9n35032dee select /* SPMTEST */ sum(n)       NO       YES
                               from sales where region_id = 10   
SQL_PLAN_b8dhx84x2wh9n4f85717c select /* SPMTEST */ sum(n)       YES      NO
                               from sales where region_id = 10   

When the optimizer generated the new SQL execution plan it created a new SQL plan baseline for it (SQL_PLAN_b8dhx84x2wh9n4f85717c). It does this even though our first SQL plan baseline (SQL_PLAN_b8dhx84x2wh9n35032dee) was disabled (ENABLED=NO). The new SQL plan baseline is enabled but it has not yet been verified and accepted by SQL plan management evolution, so we see that ACCEPTED=NO. At this point, our SQL statement will not use either of our SQL plan baselines and there is no “Note” section shown in the plan (that’s what we saw above with execution plan number 2). If the auto evolution task is enabled in your database (which is the default in Oracle Database 12c) or if you run evolution manually, then the new SQL plan baseline is subject to verification and evolution because it is not disabled. When a SQL plan baseline is verified, evolution might accept it. This is what you will see after evolution:

PLAN_NAME                      SQL_TEXT                          ENABLED  ACCEPTED
------------------------------ --------------------------------- -------- --------
SQL_PLAN_b8dhx84x2wh9n35032dee select /* SPMTEST */ sum(n)       NO       YES
                               from sales where region_id = 10  
SQL_PLAN_b8dhx84x2wh9n4f85717c select /* SPMTEST */ sum(n)       YES      YES
                               from sales where region_id = 10  

We now have a SQL plan baseline that is enabled and accepted. The first one remains disabled but the optimizer will be able to use the new SQL plan baseline. That’s what we see in execution plan number 3, above.

Summary

When you disable a SQL plan baseline you disable an individual SQL plan baseline but not the mechanics of plan capture and evolution. This behavior is benign and correct because, after all, the new plan was verified before it was accepted. However, I get the impression DBAs sometimes disable a SQL plan baseline with the expectation that they have somehow switched off SQL plan management for an individual SQL statement. As you’ve seen, that isn’t the case. If your intention is to do this, then I recommend that you drop the SQL plan baseline using DBMS_SPM.DROP_SQL_PLAN_BASELINE. If you want to keep a “backup copy”, then use DBMS_SPM.PACK_STGTAB_BASELINE to pack the relevant baseline into a staging table before you drop it. You can always put it back using DBMS_SPM.UNPACK_STGTAB_BASELINE.

There's more on this too in My Oracle Support, Doc ID 1520337.1.

Join the discussion

Comments ( 3 )
  • Houri Mohamed Thursday, July 27, 2017
    Hi Nigel,

    What you've explained here is clear and quite obvious since a long time (see point 17 in this blog :https://jonathanlewis.wordpress.com/2013/12/23/plan-changes/)

    Best regards
    Mohamed
  • Chuck Friday, September 28, 2018
    Thanks for the details. This was useful when a 12.1 to 12.2 upgrade went downhill with existing baselines. I'm still struggling to see how new handles are captured via AUTO-CAPTURE when optimizer_capture_sql_plan_baselines has been set to FALSE for months. It only captures 2-3 new ones per day on average with it set to FALSE. Pre-existing SQL captures aside, how can this query have new SQL Handles captured even with FALSE?

    SELECT sql_handle, MIN (created)
    FROM dba_sql_plan_baselines
    GROUP BY sql_handle
    ORDER BY 2 DESC;

    We are seeing slow SYSAUX growth and have to routinely apply the DBMS_SPM.DROP_SQL_PLAN_BASELINE weed wacker. All I want is for it to stop identifying new queries.
  • Nigel Bayliss Friday, October 5, 2018
    Hi Chuck - thanks for the clarifications via email too. Please feel free to get back in touch if this isn't fully resolved.
    Thanks,
    Nigel
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services