Enabled, Accepted, Fixed SQL Plan Baselines

By Franck Pachot

.
When the documentation is not always clear, I prefer to build a test case to be sure about the behavior in different context and different versions. Here is a test on SQL Plan Management to show which plan is chosen among the different states of SQL Plan Baselines: Enabled, Accepted, Fixed. Thanks to Oracle ACE program, I have some Oracle Cloud credits to quickly provision a database, so I tested that on Oracle 18c.

For this test, I’ve created a table:

create table DEMO as select rownum n from xmltable('1 to 10000');

with 8 indexes:

exec for i in 1..8 loop execute immediate 'create index DEMO'||i||' on DEMO(n,'||i||')'; end loop;

and a procedure to query it several times, setting random costs for the indexes, with only one cheapest:

create or replace procedure runplans(n number) as
 dummy number;
begin
-- run all this 30 times
for k in 1..30 loop
 run from index DEMO1 to DEMOt with one of them cheaper each time
 for t in 1..n loop
  -- set random cost for all indexes
  for i in 1..8 loop dbms_stats.set_index_stats(user,'DEMO'||i,indlevel=>round(dbms_random.value(10,100)),no_invalidate=>true); end loop;
  -- set cheap cost for index DEMOt
  dbms_stats.set_index_stats(user,'DEMO'||t,indlevel=>1,no_invalidate=>true);
  -- change some parameters to parse new child
  execute immediate 'alter session set optimizer_index_caching='||(t*8+k);
  -- query with an index hint but not specifying the index so the cheaper is chosen
  select /*+ index(DEMO) */ n into dummy from DEMO where n=1;
 end loop;
end loop;
end;
/

So you understand the idea: have 8 possible execution plans, with one cheaper than the others. And the goal is to see which one is chosen depending of the state of the SQL Plan Baseline.

I will play with the baselines and will display the cursor execution with the following SQLcl alias:

SQL> alias sqlpb=select sql_id,plan_hash_value,sum(executions),sql_plan_baseline,id,object_name from v$sql join v$sql_plan using(sql_id,plan_hash_value,child_number) where sql_text like 'SELECT % FROM DEMO%' and id=1 group by sql_id,plan_hash_value,sql_plan_baseline,id,object_name order by 3;

So, I call my procedure to run the query 30 times when index DEMO1 is the cheapest:

SQL> exec runplans(1)
PL/SQL procedure successfully completed.

Here is my alias to show the 30 executions using DEMO1 (object_name from v$sqlplan):

SQL> sqlpb
 
SQL_ID            PLAN_HASH_VALUE   SUM(EXECUTIONS) SQL_PLAN_BASELINE     ID OBJECT_NAME
-------------     ---------------   --------------- --------------------  -- -----------
gqnkn2d17zjvv          3739632713                30                        1 DEMO1

I load this to SPM

SQL> exec dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(sql_id=>'gqnkn2d17zjvv'));
PL/SQL procedure successfully completed.

Here is my SQL Plan Baseline, enabled and accepted:

SQL> select plan_name,enabled,accepted,fixed,executions from dba_sql_plan_baselines;
 
PLAN_NAME                        ENABLED   ACCEPTED   FIXED     EXECUTIONS
------------------------------   -------   --------   -----     ----------
SQL_PLAN_dcc9d14j7k1vu97e16a35   YES       YES        NO                30

Now I run my procedure to run 30 times the cursor and for each, 8 times with one different index being the cheapest:

SQL> exec runplans(8)
PL/SQL procedure successfully completed.

So all executions have used the only one SQL Plan Baseline which is enabled and accepted:

SQL> sqlpb
 
SQL_ID            PLAN_HASH_VALUE   SUM(EXECUTIONS) SQL_PLAN_BASELINE                  ID OBJECT_NAME
-------------     ---------------   --------------- ---------------------------------  -- -----------
gqnkn2d17zjvv          3739632713               232 SQL_PLAN_dcc9d14j7k1vu97e16a35      1 DEMO1

And the other plans (because only 8 of them had this DEMO1 plan being the cheapest) were loaded, enabled but not accepted:

SQL> select plan_name,enabled,accepted,fixed,executions from dba_sql_plan_baselines;
 
PLAN_NAME                        ENABLED   ACCEPTED   FIXED     EXECUTIONS
------------------------------   -------   --------   -----     ----------
SQL_PLAN_dcc9d14j7k1vu287d1344   YES       NO         NO                 0
SQL_PLAN_dcc9d14j7k1vu452ab005   YES       NO         NO                 0
SQL_PLAN_dcc9d14j7k1vu4564f9cd   YES       NO         NO                 0
SQL_PLAN_dcc9d14j7k1vu4cdc9ee7   YES       NO         NO                 0
SQL_PLAN_dcc9d14j7k1vu5353a77e   YES       NO         NO                 0
SQL_PLAN_dcc9d14j7k1vu97e16a35   YES       YES        NO                30
SQL_PLAN_dcc9d14j7k1vuc6a3f323   YES       NO         NO                 0
SQL_PLAN_dcc9d14j7k1vufb8f9e5a   YES       NO         NO                 0

Now, I change the status of the baselines to get all combinations of enabled, fixed and accepted, and in addition to that change the plan name to tne line of plan which differs:

SQL> begin
  2    for i in (select rownum n,plan_name from dba_sql_plan_baselines) loop
  3     if i.n in (2,4,6,8) then dbms_output.put_line(dbms_spm.evolve_sql_plan_baseline(plan_name=>i.plan_name,verify=>'no',commit=>'yes')); end if;
  4     if i.n in (1,2,5,6) then dbms_output.put_line(dbms_spm.alter_sql_plan_baseline(plan_name=>i.plan_name,attribute_name=>'enabled',attribute_value=>'no')); end if;
  5     if i.n in (5,6,7,8) then dbms_output.put_line(dbms_spm.alter_sql_plan_baseline(plan_name=>i.plan_name,attribute_name=>'fixed',attribute_value=>'yes')); end if;
  6     for p in ( select plan_table_output from dbms_xplan.display_sql_plan_baseline(plan_name=>i.plan_name,format=>'basic') where plan_table_output like '%| DEMO%' ) loop
  7      dbms_output.put_line(dbms_spm.alter_sql_plan_baseline(plan_name=>i.plan_name,attribute_name=>'plan_name',attribute_value=>p.plan_table_output));
  8     end loop;
  9    end loop;
 10  end;
 11  /

So here they are, with their new name:

SQL> select plan_name,enabled,accepted,fixed,executions from dba_sql_plan_baselines;
 
PLAN_NAME                            ENABLED   ACCEPTED   FIXED     EXECUTIONS
----------------------------------   -------   --------   -----     ----------
|   1 |  INDEX RANGE SCAN| DEMO4 |   NO        NO         NO                 0
|   1 |  INDEX RANGE SCAN| DEMO3 |   NO        YES        NO                 0
|   1 |  INDEX RANGE SCAN| DEMO2 |   YES       NO         NO                 0
|   1 |  INDEX RANGE SCAN| DEMO6 |   YES       YES        NO                 0
|   1 |  INDEX RANGE SCAN| DEMO5 |   NO        NO         YES                0
|   1 |  INDEX RANGE SCAN| DEMO1 |   NO        YES        YES               30
|   1 |  INDEX RANGE SCAN| DEMO8 |   YES       NO         YES                0
|   1 |  INDEX RANGE SCAN| DEMO7 |   YES       YES        YES                0

Fixed plans have priority

I flush the shared pool and run my 240 statements again:

SQL> alter system flush shared_pool;
System FLUSH altered
SQL> exec runplans(8)
PL/SQL procedure successfully completed.

Here is the result in V$SQL, only one plan used for all those executions:

SQL> sqlpb
 
SQL_ID            PLAN_HASH_VALUE   SUM(EXECUTIONS) SQL_PLAN_BASELINE                      ID OBJECT_NAME
-------------     ---------------   --------------- -------------------------------------  -- -----------
gqnkn2d17zjvv          1698325646               240 |   1 |  INDEX RANGE SCAN| DEMO7 |      1 DEMO7

The only possible plans are those that are enabled and accepted, which are the DEMO6 and DEMO7 ones. However, fixed plans have a priority, so even when the CBO came with the DEMO6 plan it was not used. When there are fixed enabled accepted SQL Plan Baseline, those are the only one considered.

Enabled and Accepted are used

Now setting all fixed attribute to no:

SQL> begin
  2    for i in (select rownum n,plan_name from dba_sql_plan_baselines) loop
  3     dbms_output.put_line(dbms_spm.alter_sql_plan_baseline(plan_name=>i.plan_name,attribute_name=>'fixed',attribute_value=>'no'));
  4    end loop;
  5  end;
  6  /
PL/SQL procedure successfully completed.
 
SQL> select plan_name,enabled,accepted,fixed,executions from dba_sql_plan_baselines;
PLAN_NAME                            ENABLED   ACCEPTED   FIXED     EXECUTIONS
----------------------------------   -------   --------   -----     ----------
|   1 |  INDEX RANGE SCAN| DEMO4 |   NO        NO         NO                 0
|   1 |  INDEX RANGE SCAN| DEMO3 |   NO        YES        NO                 0
|   1 |  INDEX RANGE SCAN| DEMO2 |   YES       NO         NO                 0
|   1 |  INDEX RANGE SCAN| DEMO6 |   YES       YES        NO                 0
|   1 |  INDEX RANGE SCAN| DEMO5 |   NO        NO         NO                 0
|   1 |  INDEX RANGE SCAN| DEMO1 |   NO        YES        NO                30
|   1 |  INDEX RANGE SCAN| DEMO8 |   YES       NO         NO                 0
|   1 |  INDEX RANGE SCAN| DEMO7 |   YES       YES        NO                 0

Here is another run:

SQL> alter system flush shared_pool;
System FLUSH altered.
SQL> exec runplans(8)
PL/SQL procedure successfully completed.
 
SQL> sqlpb
 
SQL_ID            PLAN_HASH_VALUE   SUM(EXECUTIONS) SQL_PLAN_BASELINE                      ID OBJECT_NAME
-------------     ---------------   --------------- -------------------------------------  -- -----------
gqnkn2d17zjvv          1698325646                95 |   1 |  INDEX RANGE SCAN| DEMO7 |      1 DEMO7
gqnkn2d17zjvv          3449379882               145 |   1 |  INDEX RANGE SCAN| DEMO6 |      1 DEMO6

Now that there are no fixed plans taking the priority, all enabled and accepted plans are possible, but only them.

All possible plans in the baseline but none enabled and accepted

Then what happens when all possible plans are in the SQL Plan Baseline but none of them are both enabled and accepted?

SQL> begin
  2    for i in (select rownum n,plan_name from dba_sql_plan_baselines where accepted='YES') loop
  3     dbms_output.put_line(dbms_spm.alter_sql_plan_baseline(plan_name=>i.plan_name,attribute_name=>'enabled',attribute_value=>'no'));
  4    end loop;
  5  end;
  6  /
 
SQL> select plan_name,enabled,accepted,fixed,executions from dba_sql_plan_baselines;
 
PLAN_NAME                            ENABLED   ACCEPTED   FIXED     EXECUTIONS
----------------------------------   -------   --------   -----     ----------
|   1 |  INDEX RANGE SCAN| DEMO4 |   NO        NO         NO                 0
|   1 |  INDEX RANGE SCAN| DEMO3 |   NO        YES        NO                 0
|   1 |  INDEX RANGE SCAN| DEMO2 |   YES       NO         NO                 0
|   1 |  INDEX RANGE SCAN| DEMO6 |   NO        YES        NO                 0
|   1 |  INDEX RANGE SCAN| DEMO5 |   NO        NO         NO                 0
|   1 |  INDEX RANGE SCAN| DEMO1 |   NO        YES        NO                30
|   1 |  INDEX RANGE SCAN| DEMO8 |   YES       NO         NO                 0
|   1 |  INDEX RANGE SCAN| DEMO7 |   NO        YES        NO                 0

So all combinations of indexes are there (and my query forces index access with a hint) but none are accepted and enabled.

SQL> alter system flush shared_pool;
System FLUSH altered.
SQL> exec runplans(8)
PL/SQL procedure successfully completed.
 
SQL> sqlpb
SQL_ID            PLAN_HASH_VALUE   SUM(EXECUTIONS) SQL_PLAN_BASELINE     ID OBJECT_NAME
-------------     ---------------   --------------- --------------------  -- -----------
gqnkn2d17zjvv          3739632713                 8                        1 DEMO1
gqnkn2d17zjvv          4234411015                16                        1 DEMO2
gqnkn2d17zjvv          2199479965                24                        1 DEMO3
gqnkn2d17zjvv          1698325646                30                        1 DEMO7
gqnkn2d17zjvv          3449379882                30                        1 DEMO6
gqnkn2d17zjvv          2144220082                30                        1 DEMO5
gqnkn2d17zjvv           918903766                30                        1 DEMO4
gqnkn2d17zjvv            39208404                72                        1 DEMO8

When there are no enabled and accepted plans, then anything is possible and each execution keeps the one the CBO came with.

None enabled and accepted, but new plan possible

Now, in order to have a new plan possible I’ll still run the same query but after dropping all indexes.

SQL> exec for i in 1..8 loop execute immediate 'drop index DEMO'||i; end loop;
PL/SQL procedure successfully completed.

I’ve run the same as before but without the dbms_stats calls.

Here all executions have run with the only possible plan: a full table scan:

SQL> select sql_id,plan_hash_value,sum(executions),sql_plan_baseline,id,object_name from v$sql join v$sql_plan using(sql_id,plan_hash_value,child_number) where sql_text like 'SELECT % FROM DEMO%' and id=1 group by sql_id,plan_hash_value,sql_plan_baseline,id,object_name order by 3
 
SQL_ID            PLAN_HASH_VALUE   SUM(EXECUTIONS) SQL_PLAN_BASELINE     ID OBJECT_NAME
-------------     ---------------   --------------- --------------------  -- -----------
gqnkn2d17zjvv          4000794843                29                        1 DEMO

this plan has been added, enabled but not accepted, to the SQL Plan Baseline:

SQL> select plan_name,enabled,accepted,fixed,executions from dba_sql_plan_baselines;
 
PLAN_NAME                            ENABLED   ACCEPTED   FIXED     EXECUTIONS
-------------     ---------------   --------------- --------------------  -- -----------
|   1 |  INDEX RANGE SCAN| DEMO4 |   NO        NO         NO                 0
|   1 |  INDEX RANGE SCAN| DEMO3 |   NO        YES        NO                 0
|   1 |  INDEX RANGE SCAN| DEMO2 |   YES       NO         NO                 0
|   1 |  INDEX RANGE SCAN| DEMO6 |   NO        YES        NO                 0
|   1 |  INDEX RANGE SCAN| DEMO5 |   NO        NO         NO                 0
SQL_PLAN_dcc9d14j7k1vu838f84a8       YES       NO         NO                 0
|   1 |  INDEX RANGE SCAN| DEMO1 |   NO        YES        NO                30
|   1 |  INDEX RANGE SCAN| DEMO8 |   YES       NO         NO                 0
|   1 |  INDEX RANGE SCAN| DEMO7 |   NO        YES        NO                 0

Not accepted means that it cannot be used. But as there are no other plan possible, it will be used anyway.

In summary:
Fixed plans are like telling to the optimizer: You must use one of these.
Enabled accepted plans are like telling the optimizer: You should use one of these.
Disabled or non-accepted plans are like telling the optimizer: Try to find another plan.
The optimizer will always come with a plan, so if the rules cannot be applied, the optimizer best-estimated plan is used. It may be a non-enabled or non-accepted plan. Or it can be a new plan, which will then be added as non accepted.