SQL Plan Baselines and the Optimizer Environment

06 Jan 2019

It should be clear that a SQL Plan Baseline becomes non-reproducible if you change the underlying objects in a way that prevents the optimizer from using the given execution plan (e.g. by dropping an index used in the execution plan). In such a situation the plan baseline will be marked as non-reproducible.

A change in the optimizer environment can also be the reason for the optimizer not being able to reproduce a baseline. SQL Plan Baselines store a set of hints that should guide the optimizer to use the desired plan. But in some situations, not all the required hints to reproduce the exact same plan are stored in the baseline. This blog post explores these situations and gives some tips to avoid them. It also shows how such non-reproducible baselines can be made reproducible with the use of SQL Patch.

Demo

Let’s create a simple table with an index

SQL> create table numbers (num number(4));
SQL> create index num on numbers(num);
SQL> insert into numbers values(10);
SQL> commit;

We issue a simple query with some bind variables…

SQL> var fromNum number;
SQL> var toNum number;
SQL> exec :fromNum := 9; :toNum := 11;

SQL> select * from numbers where num between :fromNum and :toNum;

       NUM
----------
        10

… and display its execution plan

SQL> select * from table(dbms_xplan.display_cursor());

SQL_ID  3gurcdja09xfm, child number 0
-------------------------------------
select * from numbers where num between :fromNum and :toNum

Plan hash value: 3276288629

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     1 (100)|          |
|*  1 |  FILTER           |      |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| NUM  |     1 |     3 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(:TONUM>=:FROMNUM)
   2 - access("NUM">=:FROMNUM AND "NUM"<=:TONUM)

Especially note the FILTER operation on plan line id 1. We create a baseline for this statement.

SQL> var baselineCount number;
SQL> exec :baselineCount := dbms_spm.load_plans_from_cursor_cache(
                                 sql_id=>'3gurcdja09xfm',
                                 fixed => 'YES');

PL/SQL procedure successfully completed.

SQL> print :baselineCount

BASELINECOUNT
-------------
            1

When we execute the query again, we see a note at the end of the execution plan telling us that the created baseline was used.

SQL_ID  3gurcdja09xfm, child number 0
-------------------------------------
select * from numbers where num between :fromNum and :toNum

Plan hash value: 3276288629

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     1 (100)|          |
|*  1 |  FILTER           |      |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| NUM  |     1 |     3 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(:TONUM>=:FROMNUM)
   2 - access("NUM">=:FROMNUM AND "NUM"<=:TONUM)

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

Now let’s change an optimizer parameter and re-execute the query. We also trace the SQL_Compiler component to get more information about what’s happening behind the scenes:

SQL> alter session set "_optimizer_generate_transitive_pred" = false;

Session altered.

SQL> alter session set events 'trace [SQL_Compiler.*]';

Session altered.

SQL> select * from numbers where num between :fromNum and :toNum;

       NUM
----------
        10

SQL> select * from table(dbms_xplan.display_cursor());

SQL_ID  3gurcdja09xfm, child number 1
-------------------------------------
select * from numbers where num between :fromNum and :toNum

Plan hash value: 2824003243

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| NUM  |     1 |     3 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("NUM">=:FROMNUM AND "NUM"<=:TONUM)

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


22 rows selected.

Elapsed: 00:00:00.14

In the trace file the following lines are written:

------- START SPM Plan Dump -------
SPM: failed to reproduce the plan using the following info:
  parse_schema name        : THOMAS
  plan_baseline signature  : 5596800680717884339
  plan_baseline plan_id    : 1469248798
  plan_baseline hintset    :
    hint num  1 len 27 text: IGNORE_OPTIM_EMBEDDED_HINTS
    hint num  2 len 35 text: OPTIMIZER_FEATURES_ENABLE('18.1.0')
    hint num  3 len 20 text: DB_VERSION('18.1.0')
    hint num  4 len  8 text: ALL_ROWS
    hint num  5 len 22 text: OUTLINE_LEAF(@"SEL$1")
    hint num  6 len 51 text: INDEX(@"SEL$1" "NUMBERS"@"SEL$1" ("NUMBERS"."NUM"))
SPM: generated non-matching plan:
----- Explain Plan Dump -----
----- Plan Table -----

============
Plan Table
============
------------------------------------+-----------------------------------+
| Id  | Operation         | Name    | Rows  | Bytes | Cost  | Time      |
------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT  |         |       |       |     1 |           |
| 1   |  INDEX RANGE SCAN | NUM     |     1 |     3 |     1 |  00:00:01 |
------------------------------------+-----------------------------------+

The generated plan with help from the hints from the baseline produced a slightly different plan (no FILTER operation anymore). This shows that the hints stored in this particular baseline are not sufficient to generate the exact same execution plan.

Instead, Oracle will now use the generated plan with the lowest cost. In this example the used plan is not much worse than the one stored in the baseline, but the key point is that the baseline was not used. In real scenarios this could mean that a possibly much worse plan is used.

Types of hints not stored in SQL Plan Baselines

From my observations the following types of hints are not stored in SQL Plan Baselines:

Optimizer parameters (opt_param(...))
Parallel hints (e.g. PARALLEL or NO_PARALLEL) when influenced by session level settings, by executing alter session force parallel query for example.

So, if the optimizer environment of the session executing the statement differs from the environment where the execution plan of the baseline was generated, there’s a potential for non-reproducible plans. This is only true if there is no regular hint leading to the same execution plan. If, for example, an execution plan is doing an index join, the SQL Plan Baseline would store the hint INDEX_JOIN. Disabling index joins on session level (alter session set "_index_join_enabled" = false) has therefore no impact on the reproducibility of the baseline.

When loading an execution plan into a SQL Plan baseline always keep in mind what optimizer environment was used to generate the plan and what optimizer environment is used by the session supposed to benefit from the SQL Plan Baseline.

Add the missing hints to the SQL statement

After we found out that our plan could not have been reproduced with the set of stored hints, the trace output can give us some valuable information about the sessions optimizer environment:

***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
  *************************************
  PARAMETERS WITH ALTERED VALUES
  ******************************
Compilation Environment Dump
_optimizer_generate_transitive_pred = false
Bug Fix Control Environment


  *************************************
  PARAMETERS WITH DEFAULT VALUES
  ******************************
...

Note that in our scenario we used the default optimizer environment to generate an execution plan to be used in the SQL Plan Baseline and an altered optimizer environment to show that the baseline is not used. It could also be the other way around, whereas we would not find any information about altered parameters in the trace file.

There are 2 ways to add the necessary hint to our statement:

Packing the SQL Plan Baseline into a staging table (DBMS_SPM.PACK_STGTAB_BASELINE), modify the hints stored in the OTHER_XML column and unpack it again.
Create a SQL Patch.

The second approach is probably more supported, so we demonstrate that one. We just add the hint opt_param('_optimizer_generate_transitive_pred' 'true')

SQL> var patchName varchar2(128)
SQL> exec :patchName := dbms_sqldiag.create_sql_patch(
  sql_id => '3gurcdja09xfm',
  hint_text=>'opt_param(''_optimizer_generate_transitive_pred'' ''true'')');
PL/SQL procedure successfully completed.

SQL> print patchName

PATCHNAME
----------------------------
SYS_SQLPTCH_0168234fa3400005

Now let’s execute the query again:

SQL> select * from numbers where num between :fromNum and :toNum;

       NUM
----------
        10

SQL> select * from table(dbms_xplan.display_cursor());

SQL_ID  3gurcdja09xfm, child number 0
-------------------------------------
select * from numbers where num between :fromNum and :toNum

Plan hash value: 3276288629

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     1 (100)|          |
|*  1 |  FILTER           |      |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| NUM  |     1 |     3 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(:TONUM>=:FROMNUM)
   2 - access("NUM">=:FROMNUM AND "NUM"<=:TONUM)

Note
-----
   - SQL patch "SYS_SQLPTCH_0168234fa3400005" used for this statement
   - SQL plan baseline SQL_PLAN_4vaynk3kdp6xm5792f51e used for this statement


25 rows selected.

Elapsed: 00:00:00.09

We see, that the created patch and the baseline are now used again.

Final thoughts

I’m two-minded about the fact, that not all necessary hints are stored in the SQL Plan Baseline.

In case of the parallel hints it would have been easy for Oracle to add them to the baseline when necessary, but it was probably decided to intentionally not do that. If you force parallelism on the session for example, you probably don’t want to use a baseline with a serial plan.

Regarding the optimizer parameters I think a changed parameter should not be a reason for a baseline not being used.

blog. aregger.io

SQL Plan Baselines and the Optimizer Environment

Demo

Types of hints not stored in SQL Plan Baselines

Add the missing hints to the SQL statement

Final thoughts