SPM baseline and historical execution plans

I have often wondered why when we consult a past execution plan that is protected by an SPM baseline, there is no Note at the bottom of that execution plan showing the use of that SPM?

SQL> select banner_full from v$version;

BANNER_FULL
------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0                                                                 

SQL> select count(1) from emp where deptno=30;

  COUNT(1)
----------
         6

SQL> select * from table(dbms_xplan.display_cursor)

SQL_ID  89mvrxmzsd1v3, child number 1
-------------------------------------
Plan hash value: 2083865914
-------------------------------------------
| Id  | Operation          | Name | Rows  |
-------------------------------------------
|   0 | SELECT STATEMENT   |      |       |
|   1 |  SORT AGGREGATE    |      |     1 |
|*  2 |   TABLE ACCESS FULL| EMP  |     6 |
-------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("DEPTNO"=30)

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


exec dbms_workload_repository.add_colored_sql('89mvrxmzsd1v3');
exec dbms_workload_repository.create_snapshot;

SQL> select * from table(dbms_xplan.display_awr('89mvrxmzsd1v3')); --> please note: display_awr


SQL_ID 89mvrxmzsd1v3
--------------------
Plan hash value: 2083865914
-------------------------------------------
| Id  | Operation          | Name | Rows  |
-------------------------------------------
|   0 | SELECT STATEMENT   |      |       |
|   1 |  SORT AGGREGATE    |      |     1 |
|   2 |   TABLE ACCESS FULL| EMP  |     6 |
-------------------------------------------

--> There is no Note on SPM

There is good news. Starting with 21c this has been corrected by Oracle. Here is the demonstration:

SQL> select banner_full from v$version;

BANNER_FULL
------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0                                                                  

SQL> select count(1) from emp where deptno=30;

  COUNT(1)
----------
         6

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


SQL_ID  89mvrxmzsd1v3, child number 1
-------------------------------------
select count(1) from emp where deptno=30

Plan hash value: 2083865914

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     6 |    18 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("DEPTNO"=30)

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


exec dbms_workload_repository.add_colored_sql('89mvrxmzsd1v3');
exec dbms_workload_repository.create_snapshot;

SQL> select * from table(dbms_xplan.display_awr('89mvrxmzsd1v3')); --> please note: display_awr

SQL_ID 89mvrxmzsd1v3
--------------------
select count(1) from emp where deptno=30

Plan hash value: 2083865914
-------------------------------------------
| Id  | Operation          | Name | Rows  |
-------------------------------------------
|   0 | SELECT STATEMENT   |      |       |
|   1 |  SORT AGGREGATE    |      |     1 |
|*  2 |   TABLE ACCESS FULL| EMP  |     6 |
-------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("DEPTNO"=30)

Note
-----
   - SQL plan baseline "SQL_PLAN_1vbjb8bc7qryjc392520a" used for this statement

This is the first time if my memory doesn’t fail me, that I see this Note on an SPM baseline appearing at the bottom of an execution plan coming from AWR. This becomes possible because Oracle has finally enriched the other_xml column of dba_hist_sql_plan with the SPM baseline information as shown below:

SQL> select banner_full from v$version;

BANNER_FULL
----------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SELECT
     p.sql_id
    ,p.plan_hash_value
    ,t.spm_baseline
FROM   
  dba_hist_sql_plan p
 ,xmltable('for $i in /other_xml/info
               where $i/@type eq "baseline"
               return $i'
               passing xmltype(p.other_xml)
               columns spm_baseline varchar2(100) path '/') t
 WHERE p.sql_id = '89mvrxmzsd1v3'
 and   p.other_xml is not null;

no rows selected


SQL> select banner_full from v$version;

BANNER_FULL
-----------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SELECT
     p.sql_id
    ,p.plan_hash_value
    ,t.spm_baseline
FROM   
  dba_hist_sql_plan p
 ,xmltable('for $i in /other_xml/info
               where $i/@type eq "baseline"
               return $i'
               passing xmltype(p.other_xml)
               columns spm_baseline varchar2(100) path '/') t
 WHERE p.sql_id = '89mvrxmzsd1v3'
 and   p.other_xml is not null;

SQL_ID        PLAN_HASH_VALUE SPM_BASELINE
------------- --------------- -----------------------------------
89mvrxmzsd1v3      2083865914 "SQL_PLAN_1vbjb8bc7qryjc392520a"

I remind for those who are not yet in 21c, that they can use Dom Brooks’s script to track the use of SPM in AWR

SQL> select banner_full from v$version;

BANNER_FULL
----------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> @wasSPMUsed19c
Enter value for sql_id: 89mvrxmzsd1v3

   SNAP_ID DT                         SQL_ID        PLAN_HASH_VALUE       PHV2        SIG D I WHEN_BASELINE_CREATED
---------- -------------------------- ------------- --------------- ---------- ---------- - - --------------------------
      2241 04-FEB-2023 16:54          89mvrxmzsd1v3      2083865914 3281146378 2,1387E+18 Y Y 04-FEB-2023 16:52

12cR2: unreproducible SPM and cursor bind awareness

In the previous article we knew that, as of Oracle 12cR2, a cursor can’t be kept bind aware when it is protected by a single enabled and accepted SPM plan baseline. In other words, the preceding article explained that if you want a cursor to be both bind aware and protected against any unexpected execution plan change, you need then to have at least two enabled and accepted SPM baselines for this cursor.

That’s said multiple enabled and accepted SPM plan doesn’t necessarily means multiple enabled, accepted and reproducible SPM plans. And, as this article will demonstrate it, this reproducible path seems to have been ignored by the Oracle optimizer group when disabling the bind awareness property explained in the previous article. The new 12cR2 internal code disabling and enabling ACS seems to be based only on the accepted and enabled property of the bind aware cursor SPM plan. Let’s demonstrate this with a simple example

Setting the Scenes

-- create table, index and gather statistics
create table t1
    (col1  number
    ,col2  varchar2(50)
    ,flag  varchar2(2));
 
 insert into t1
    select rownum
          ,lpad('X',50,'X')
          ,case when rownum = 1
            then 'Y1'
               when rownum = 2
            then 'Y2'
               when mod(rownum,2) = 0
            then 'N1'
            else 'N2'
           end
    from   dual
connect by rownum <= 100000;
 
create index i1 on t1(flag);

BEGIN 
  dbms_stats.gather_table_stats
      (user
     ,'t1'
     ,method_opt => 'for all columns size skewonly' 
     ,estimate_percent => dbms_stats.auto_sample_size );
END; 
/

-- generate a bind aware cursor
alter session set cursor_sharing=force;

select count(*), max(col2) from t1 where flag = 'Y1';
select count(*), max(col2) from t1 where flag = 'N1';
select count(*), max(col2) from t1 where flag = 'N1';
select count(*), max(col2) from t1 where flag = 'Y1';

Observation

The above script will generate a bind sensitive and bind aware cursor as shown below:

select
    sql_id
  ,child_number
  ,is_bind_sensitive 
  ,is_bind_aware
  ,executions
  ,invalidations
  ,object_status
from
   gv$sql 
where
   sql_id = '6fbvysnhkvugw'
and is_shareable = 'Y';

SQL_ID        CHILD_NUMBER I I EXECUTIONS INVALIDATIONS OBJECT_STATUS      
------------- ------------ - - ---------- ------------- -------------
6fbvysnhkvugw            1 Y Y          1             0 VALID              
6fbvysnhkvugw            2 Y Y          1             0 VALID 

Let’s load these two execution plans (child number 1 and 2) into a SPM baseline and make few verifications:

declare
   rs pls_integer;
begin
   rs := dbms_spm.load_plans_from_cursor_cache('6fbvysnhkvugw');
end;
/

col plan_name format a30
select
  plan_name ,origin, enabled
from dba_sql_plan_baselines;

PLAN_NAME                      ORIGIN                        ENA
------------------------------ ----------------------------- ---
SQL_PLAN_d9tch6banyzg9616acf47 MANUAL-LOAD-FROM-CURSOR-CACHE YES
SQL_PLAN_d9tch6banyzg98576eb1f MANUAL-LOAD-FROM-CURSOR-CACHE YES

select
    sql_id
  ,child_number
  ,is_bind_sensitive 
  ,is_bind_aware
  ,executions
  ,invalidations
  ,object_status
from
   gv$sql 
where
   sql_id = '6fbvysnhkvugw'
and is_shareable = 'Y';

SQL_ID        CHILD_NUMBER I I EXECUTIONS INVALIDATIONS OBJECT_STATUS      
------------- ------------ - - ---------- ------------- -------------------
6fbvysnhkvugw            1 Y Y          1             1 INVALID_UNAUTH     
6fbvysnhkvugw            2 Y Y          1             1 INVALID_UNAUTH

As you can see the creation of the two SPM plans has already invalidated the two underlying child cursors making them both not anymore shareable for the next execution(INVALID_UNAUTHORIZED).

Now that we have a bind aware cursor protected by two enabled and accepted SPM plans let’s launch it a couple of time in order see what will happen:

SQL> select count(*), max(col2) from t1 where flag = 'Y1';

  COUNT(*) MAX(COL2)                                         
---------- --------------------------------------------------
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

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

SQL_ID  6fbvysnhkvugw, child number 0
-------------------------------------
 
Plan hash value: 3625400295
-----------------------------------------------------
| Id  | Operation                    | Name | Rows  | 
-----------------------------------------------------
|   0 | SELECT STATEMENT             |      |       | 
|   1 |  SORT AGGREGATE              |      |     1 | 
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |     1 | 
|*  3 |    INDEX RANGE SCAN          | I1   |     1 | 
-----------------------------------------------------

 
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("FLAG"=:SYS_B_0)
 
Note
-----
   - SQL plan baseline SQL_PLAN_d9tch6banyzg98576eb1f used for this statement

As expected the apparition of the child number 0 above means that Oracle has effectively flushed out the previous couple of bind aware child cursor (1,2). We need now to go again through a warming up period for ACS to kick in as shown below:

select
    sql_id
  ,child_number
  ,is_bind_sensitive 
  ,is_bind_aware
  ,executions
  ,invalidations
  ,object_status
from
   gv$sql 
where
   sql_id = '6fbvysnhkvugw'
and is_shareable = 'Y';

SQL_ID        CHILD_NUMBER I I EXECUTIONS INVALIDATIONS OBJECT_STATUS      
------------- ------------ - - ---------- ------------- -------------
6fbvysnhkvugw            1 Y N          1             1 VALID     

select count(*), max(col2) from t1 where flag = 'N1';
select count(*), max(col2) from t1 where flag = 'N1';
select count(*), max(col2) from t1 where flag = 'Y1';

select
    sql_id
  ,child_number
  ,is_bind_sensitive 
  ,is_bind_aware
  ,executions
  ,invalidations
  ,object_status
from
   gv$sql 
where
   sql_id = '6fbvysnhkvugw'
and is_shareable = 'Y';

SQL_ID        CHILD_NUMBER I I EXECUTIONS INVALIDATIONS OBJECT_STATUS      
------------- ------------ - - ---------- ------------- -------------
6fbvysnhkvugw            2 Y Y          1             1 VALID              
6fbvysnhkvugw            3 Y Y          1             0 VALID    

We’ve, finally, succeeded building a situation with which we can proof that the Oracle optimizer group has ignored disabling ACS even when it is protected by a single reproducible SPM plan as the following will demonstrate:

-- disabling the i1 index so that the SPM plan SQL_PLAN_d9tch6banyzg98576eb1f 
-- will be irreproducible

alter index i1 invisible;

Setting the i1 index invisible, the index range scan SPM plan will inevitably be ignored by the CBO because it is not anymore reproducible. The Full table scan SPM plan will become the unique enforced execution plan whatever the bind variable selectivity will be as shown below:

SQL> select count(*), max(col2) from t1 where flag = 'Y1';

  COUNT(*) MAX(COL2)                                         
---------- --------------------------------------------------
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

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

SQL_ID  6fbvysnhkvugw, child number 2
-------------------------------------
select count(*), max(col2) from t1 where flag = :"SYS_B_0"
 
Plan hash value: 3724264953
---------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |
---------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |
|   1 |  SORT AGGREGATE    |      |     1 |    54 |
|*  2 |   TABLE ACCESS FULL| T1   |     1 |    54 |
---------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("FLAG"=:SYS_B_0)
 
Note
-----
   - SQL plan baseline SQL_PLAN_d9tch6banyzg9616acf47 used for this statement

When Oracle has detected that the index range scan SPM plan is not anymore reproducible it did two things:

a. invalidated and flushed out both index scan (child number 3) and full table scan(child number 2) cursor
b. created a new bind sensitive and not yet bind aware cursor n°2 as proofed below:

select
   sql_id
  ,child_number
  ,is_bind_sensitive 
  ,is_bind_aware
  ,executions
  ,invalidations
  ,object_status
from
   gv$sql 
where
   sql_id = '6fbvysnhkvugw'
and is_shareable = 'Y';

SQL_ID        CHILD_NUMBER I I EXECUTIONS INVALIDATIONS OBJECT_STATUS      
------------- ------------ - - ---------- ------------- -------------
6fbvysnhkvugw            2 Y N          1             2 VALID  

While officially there are still two accepted and enabled SPM plan baselines, in fact, only one SPM plan can be effectively be used to guarantee unpredictable performance degradation. So why Oracle has not disabled the bind sensitiveness property of this cursor? Is Oracle not claiming that it is useless to keep ACS working in presence of a single SPM plan?

select
   child_number
  ,bucket_id
  ,count
from
   gv$sql_cs_histogram
where
   sql_id = '6fbvysnhkvugw'
and child_number = '2';

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           2          0          1
           2          1          0
           2          2          0

Given the above situation we need only two extra executions of the same query at bucket_id n°1 or 2 and the cursor will become bind aware as shown below:

select count(*), max(col2) from t1 where flag = 'N1';
select count(*), max(col2) from t1 where flag = 'N1';

SQL> select
        sql_id
      ,child_number
      ,is_bind_sensitive 
      ,is_bind_aware
      ,executions
      ,invalidations
      ,object_status
    from
      gv$sql 
   where
     sql_id = '6fbvysnhkvugw'
   and is_shareable = 'Y';

SQL_ID        CHILD_NUMBER I I EXECUTIONS INVALIDATIONS OBJECT_STATUS      
------------- ------------ - - ---------- ------------- -------------
6fbvysnhkvugw            3 Y Y          1             1 VALID  

Summary

The developer at Oracle corporation has not considered stopping a cursor from being bind aware when one of its two SPM plans is indirectly rendered irreproducible. The internal code implemented by Oracle when deciding to stop ACS seems to be based only the Enabled and Accepted property of the corresponding SPM plan. Unfortunately an accepted and enabled SPM plan is not guaranteed to be reproducible. This is why we can still find, in 12cR2, a bind aware cursor protected by a single SPM plan.

12cR2: SPM and cursor bind awareness property

In the previous article we knew that, as of Oracle 12cR2, it is now possible to capture SPM baselined plans from AWR historical tables. In this article we are going to see how in 12cR2 a cursor will immediately stop to be bind aware when it is protected by a single SPM plan. The basic idea driving this new implementation is that, since the Adaptive Cursor Sharing main goal is to generate multiple optimal execution plans, if you decide to constrain it with a single SPM plan, you are implicitly asking Oracle to stop generating multiple execution plans.

Let’s demonstrate this new ACS-SPM relationship using the same model as that of the preceding article

Setting the Scenes

create table t_acs(n1 number, n2 number);

BEGIN
 for j in 1..1200150 loop
  if j = 1 then
    insert into t_acs values (j, 1);
  elsif j>1 and j<=101 then
    insert into t_acs values(j, 100);
  elsif j>101 and j<=1101 then
    insert into t_acs values (j, 1000);
  elsif j>10001 and j<= 110001 then
    insert into t_acs values(j,10000); 
  else
    insert into t_acs values(j, 1000000); end if;
 end loop; 
commit;
END;
/

create index t_acs_i1 on t_acs(n2);

BEGIN 
  dbms_stats.gather_table_stats
     (user
     ,'t_acs'
     ,method_opt => 'for all columns size skewonly' 
     ,cascade => true
     ,estimate_percent => dbms_stats.auto_sample_size );
END; 
/

var ln2 number;
exec :ln2 := 100
select count(1) from t_acs where n2 = :ln2;

exec :ln2 := 1e6
select count(1) from t_acs where n2 = :ln2;
select count(1) from t_acs where n2 = :ln2;

exec :ln2 := 100
select count(1) from t_acs where n2 = :ln2;

Observation

The execution of the above script will end up by producing two bind sensitive, bind aware and shareable cursors as shown below:

select 
    sql_id
  ,child_number
  ,is_bind_sensitive 
  ,is_bind_aware
  ,executions
from
   v$sql 
where
   sql_id = 'f2pmwazy1rnfd'
and is_shareable = 'Y';

SQL_ID        CHILD_NUMBER I I EXECUTIONS
------------- ------------ - - ----------
f2pmwazy1rnfd            1 Y Y          1 → full scan plan
f2pmwazy1rnfd            2 Y Y          1 → index range scan plan

So far so good.
What if I decide to create a SPM execution plan in order to constrain the above query to use always the index range scan plan?

SQL> alter session set optimizer_capture_sql_plan_baselines = TRUE;

SQL> select count(1) from t_acs where n2 = :ln2;

  COUNT(1)
----------
       100

SQL> select count(1) from t_acs where n2 = :ln2;

  COUNT(1)
----------
       100

SQL> alter session set optimizer_capture_sql_plan_baselines = FALSE;

SQL> col plan_name format a40
SQL> select 
        plan_name ,origin, accepted, enabled
     from dba_sql_plan_baselines;

PLAN_NAME                        ORIGIN          ACC ENA
------------------------------- ---------------- --- ---
SQL_PLAN_fn4mhg52jx5z125348c47  AUTO-CAPTURE     YES YES

As such the next time I will run this query it will use the index range scan SPM baselined plan as shown below:

SQL> select count(1) from t_acs where n2 = :ln2;

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

SQL_ID  f2pmwazy1rnfd, child number 1                                                                                                                                              
-------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
Plan hash value: 1882749816                                                                                                                                                                                                                                                                                                                                    
----------------------------------------------                                                                                                    
| Id  | Operation         | Name     | Rows  |                                                                                                   
----------------------------------------------                                                                                                    
|   0 | SELECT STATEMENT  |          |       |                                                                                                    
|   1 |  SORT AGGREGATE   |          |     1 |                                                                                                     
|*  2 |   INDEX RANGE SCAN| T_ACS_I1 |   100 |                                                                                                  
----------------------------------------------                                                                                                    
                                                                                                                                                                                   
Predicate Information (identified by operation id):                                                                                                                                
---------------------------------------------------                                                                                                                                                                                                                                                                                              
   2 - access("N2"=:LN2)                                                                                                                                                           
                                                                                                                                                                                   
Note                                                                                                                                                                               
-----                                                                                                                                                                              
- SQL plan baseline SQL_PLAN_fn4mhg52jx5z125348c47 used for this statement  

But what you might ignore is that, following the creation of this SPM baselined plan, Oracle did something behind the scene as shown via the following cursor new situation:

select 
    sql_id
  ,child_number
  ,is_bind_sensitive 
  ,is_bind_aware
  ,executions
from
   v$sql 
where
   sql_id = 'f2pmwazy1rnfd'
and is_shareable = 'Y';

SQL_ID        CHILD_NUMBER I I EXECUTIONS
------------- ------------ - - ----------
f2pmwazy1rnfd            1 N N          1

If you analyze carefully the new situation compared to the old one you will realize that the following actions have been done behind the scenes

1. The previous child cursors n°1 and n°2 has been flushed out.
2. A new no bind sensitive and no bind aware child cursor n°1 has been created

It is the number of execution (only 1) of child cursor n°1 which clearly explains that the previous two bind aware cursors have been flushed out from memory due to the new SPM baselined plan. The child cursor n°0 is still in the shared pool but is in a non shareable status.

SQL> select 
        sql_id
      ,child_number
      ,is_bind_sensitive 
      ,is_bind_aware
      ,is_shareable
      ,executions
    from
       v$sql 
   where
      sql_id = 'f2pmwazy1rnfd';

SQL_ID        CHILD_NUMBER I I I EXECUTIONS
------------- ------------ - - - ----------
f2pmwazy1rnfd            0 Y N N          2 → non shareable
f2pmwazy1rnfd            1 N N Y          1

That’s the new 12cR2 implementation managing the relationship between ACS and SPM. ACS is disabled when it is constrained by a single accepted and enabled SPM plan.

If we disable the previous SPM baseline the cursor becomes bind aware after a warm up period as usual

SQL> @disSPM.sql 
Enter value for plan_name: SQL_PLAN_fn4mhg52jx5z125348c47

SQL> select 
          plan_name ,origin, enabled
      from dba_sql_plan_baselines;

PLAN_NAME                       ORIGIN       ENA
------------------------------- ------------ ---
SQL_PLAN_fn4mhg52jx5z125348c47  AUTO-CAPTURE NO 

exec :ln2 :=1e6
select count(1) from t_acs where n2 = :ln2;

exec :ln2 := 100
select count(1) from t_acs where n2 = :ln2;

etc…./...

SQL> select 
        sql_id
      ,child_number
      ,is_bind_sensitive 
      ,is_bind_aware
      ,is_shareable
      ,executions
    from
       v$sql 
   where
      sql_id = 'f2pmwazy1rnfd'
  ;

SQL_ID        CHILD_NUMBER I I I EXECUTIONS
------------- ------------ - - - ----------
f2pmwazy1rnfd            0 Y N N          2
f2pmwazy1rnfd            1 Y N N          3
f2pmwazy1rnfd            2 Y Y Y          1 → bind aware full scan
f2pmwazy1rnfd            3 Y Y Y          1 → bind aware range scan

Summary

As of Oracle 12cR2 when an ACS cursor having multiple optimal execution plans is constrained by a single enabled and accepted SPM plan, Oracle will age it out from memory and cancel its bind sensitive and bind aware properties. This will remain intact until the SPM is dropped or disabled. In the next article we will see what happens to an ACS cursor when one of its multiple accepted and enabled SPM plan is rendered unreproducible.

12cR2 : Capturing SPM plans from AWR

A couple of years ago I set myself a best practice goal of preferring SPM baselines over SQL-Profiles. I must recognize that I failed to achieve this goal. Fortunately as of Oracle 12cR2 it becomes possible to load SPM baselined plans directly from AWR tables. This is why I am now unexcused to do not operate the switch.

Let’s see first how we can load SPM plans using AWR historical tables

Setting the Scenes

create table t_acs(n1 number, n2 number);

BEGIN
 for j in 1..1200150 loop
  if j = 1 then
    insert into t_acs values (j, 1);
  elsif j>1 and j<=101 then
    insert into t_acs values(j, 100);
  elsif j>101 and j<=1101 then
    insert into t_acs values (j, 1000);
  elsif j>10001 and j<= 110001 then
    insert into t_acs values(j,10000); 
  else
    insert into t_acs values(j, 1000000); end if;
 end loop; 
commit;
END;
/

create index t_acs_i1 on t_acs(n2);

BEGIN 
  dbms_stats.gather_table_stats
     (user
     ,'t_acs'
     ,method_opt => 'for all columns size skewonly' 
     ,cascade => true
     ,estimate_percent => dbms_stats.auto_sample_size );
END; 
/

exec dbms_workload_repository.create_snapshot;
var ln2 number;
exec :ln2 := 100
select count(1) from t_acs where n2 = :ln2;

exec :ln2 := 1e6
select count(1) from t_acs where n2 = :ln2;
select count(1) from t_acs where n2 = :ln2;

exec :ln2 := 100
select count(1) from t_acs where n2 = :ln2;

exec dbms_workload_repository.create_snapshot;

If you copy and past the above SQL code into a SQL PLUS session and issue the following select you should find that you have already two bind aware cursors:

select 
    sql_id
  ,child_number
  ,is_bind_sensitive 
  ,is_bind_aware
  ,executions
from
   v$sql 
where
   sql_id = 'f2pmwazy1rnfd'
and is_shareable = 'Y';

SQL_ID        CHILD_NUMBER I I EXECUTIONS
------------- ------------ - - ----------
f2pmwazy1rnfd            1 Y Y          1
f2pmwazy1rnfd            2 Y Y          1

The bind awareness property of the above cursor has nothing to do with the bottom line of this article. It is here just because I will use the same model in my next article where this time this particular property becomes necessary.

If I would have decided to create a SQL profile over the above cursor I would have then opted for Carlos Sierra coe_xfr_sql_profile.sql script as shown below:

SQL> @coe_xfr_sql_profile.sql

Parameter 1:
SQL_ID (required)

Enter value for 1: f2pmwazy1rnfd


PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
      535703726 ,054       
     1882749816 ,085       

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 1882749816

Values passed:
~~~~~~~~~~~~~
SQL_ID         : "f2pmwazy1rnfd"
PLAN_HASH_VALUE: "1882749816"

Execute coe_xfr_sql_profile_f2pmwazy1rnfd_1882749816.sql
on TARGET system in order to create a custom SQL Profile
with plan 1882749816 linked to adjusted sql_text.

SQL>@coe_xfr_sql_profile_f2pmwazy1rnfd_1882749816.sql
... manual custom SQL Profile has been created

COE_XFR_SQL_PROFILE_f2pmwazy1rnfd_1882749816 completed

As such the next time I will run this query it will use the fixed SQL Profile as shown below:

SQL> select count(1) from t_acs where n2 = :ln2;

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

Plan hash value: 1882749816                                                     
                                                                                
------------------------------------------------------ 
| Id  | Operation         | Name     | Rows  | Bytes |  
------------------------------------------------------ 
|   0 | SELECT STATEMENT  |          |       |       |  
|   1 |  SORT AGGREGATE   |          |     1 |     3 |   
|*  2 |   INDEX RANGE SCAN| T_ACS_I1 |   100 |   300 |  
------------------------------------------------------
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                                                                                         
   2 - access("N2"=:LN2)                                                        
                                                                                
Note                                                                            
-----                                                                           
- SQL profile coe_f2pmwazy1rnfd_1882749816 used for this statement

However until the arrival of 12cR2 it was impossible to load a SPM plan baseline for the above cursor using corresponding AWR historical execution plan. Hopefully it is now possible. This is below how to capture SPM plan from AWR:

SQL>@LoadSPMfromAwr.sql
Listing latest AWR snapshots ...

   SNAP_ID END_INTERVAL_TIME          
---------- ---------------------------
        14 08/03/17 13:20:09,251000000
        15 08/03/17 14:00:13,233000000
        16 08/03/17 15:07:46,465000000
        17 09/03/17 01:13:41,092000000
        18 09/03/17 12:11:26,748000000
        19 10/03/17 01:07:36,836000000
        20 10/03/17 10:08:54,214000000
        21 10/03/17 12:35:26,590000000
        22 11/03/17 01:04:40,947000000
        23 12/03/17 12:41:35,578000000
        24 12/03/17 15:03:55,730000000
        25 13/03/17 02:21:01,517000000
        26 13/03/17 12:53:22,204000000
        27 13/03/17 15:43:46,522000000
        28 14/03/17 13:13:07,716000000
        29 15/03/17 12:55:54,089000000
        30 16/03/17 12:59:39,201000000
        31 17/03/17 02:09:14,047000000
        32 17/03/17 10:38:33,520000000
        33 17/03/17 12:50:59,072000000

20 rows selected. 

Enter begin snapshot id: 14
Enter end   snapshot id: 27
Enter value for sql_filter: sql_text like ''select count(1) from t_acs%''

SQL> select 
      plan_name ,origin
    from dba_sql_plan_baselines;

PLAN_NAME                                ORIGIN                       
---------------------------------------- ---------------------
SQL_PLAN_fn4mhg52jx5z125348c47           MANUAL-LOAD-FROM-AWR         
SQL_PLAN_fn4mhg52jx5z13069e6f9           MANUAL-LOAD-FROM-AWR   


SQL> select count(1) from t_acs where n2 = :ln2;

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


SQL_ID  f2pmwazy1rnfd, child number 1
-------------------------------------
select count(1) from t_acs where n2 = :ln2
 
Plan hash value: 1882749816
 
------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | 
------------------------------------------------------
|   0 | SELECT STATEMENT  |          |       |       |
|   1 |  SORT AGGREGATE   |          |     1 |     3 | 
|*  2 |   INDEX RANGE SCAN| T_ACS_I1 |   100 |   300 | 
-------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("N2"=:LN2)
 
Note
-----
   - SQL profile coe_f2pmwazy1rnfd_1882749816 used for this statement
   - SQL plan baseline SQL_PLAN_fn4mhg52jx5z125348c47 used for this statement

You have to choose the snap interval that includes historical details of your sql_id

Here’s below the content of the script I used to capture SPM plan from AWR

PROMPT Listing latest AWR snapshots ...
SELECT snap_id, end_interval_time 
FROM dba_hist_snapshot 
WHERE end_interval_time > SYSDATE - 30
ORDER BY end_interval_time;

ACCEPT bsnapid NUMBER PROMPT "Enter begin snapshot id: "
ACCEPT esnapid NUMBER PROMPT "Enter end   snapshot id: "

SET TERMOUT OFF PAGESIZE 0 HEADING OFF LINESIZE 1000 TRIMSPOOL ON TRIMOUT ON TAB OFF

declare
  rs pls_integer;
begin
  rs := dbms_spm.load_plans_from_awr('&bsnapid', '&esnapid', '&sql_filter');
end;
/

SET TERMOUT ON PAGESIZE 5000 HEADING ON

Oracle Optimizer and SPM plan interaction

Continuing in the inspiration instilled into me by Dominic Brooks’ post on SQL Plan Management choices, I decided to picture the Oracle CBO behavior in presence of enabled and accepted SPM plan(s) baseline:

The right part of the picture, when triggered, demonstrates the parsing penalty you will have to pay before running your SQL query. Particularly when there are multiple accepted and enabled SPM plans the CBO has to try reproducing and costing all of them before making its final decision. The picture also shows that under all circumstances the CBO will start first by compiling its execution plan as if it is not constrained by any SPM plan. This clearly demonstrates that if your query is suffering from a hard parsing execution time (when the plan generation takes a lot of time) then SPM will not help you. This is where the mantra “When you can hint it then Baseline it” ceases to be accurate.

SPM reproducibility: changing the _optimizer_skip_scan_enabled value

I have had several articles about Sql Plan Management stability and evolution. I have also been recently focusing my attention particularly on the criteria, scenarios and circumstances that impeach an enabled and accepted SPM plan to be reproducible. Below are summarized those impeachment reasons:

1. Changing the index name
2. Changing the index type (with particular situations for function based and reverse indexes)
3. Changing the index leading column(s)

Then I embarked on the investigation of the reaction a SPM plan could manifest, during a query execution time, to a change in one of the environment parameters that have been used during the SPM plan capture. I have investigated two situations in this context

1. One for a change in an optimizer parameter (optimizer mode) value
2. And the other one for a NLS parameter (nls_sort) change

For the optimizer mode I came up to a conclusion that a change of that mode in the current environment will not have an effect on the reproducibility of the SPM plan as far as this one will be reproduced using the optimizer mode stored against the SPM plan.

However, an nls_sort parameter change reveals to be a serious threat for the SPM reproducibility as far the CBO will use in this particular case the current nls_sort parameter value which might end up with an impossibility to reproduce the stored SPM plan.

Despite the above investigations, there is one thing that I wanted to investigate the effect it has on the reproducibility of a SPM plan: a change of an undocumented optimizer parameter value. This post is for that purpose. Follow me please

 create table t1
    (col1  number
    ,col2  varchar2(50)
    ,flag  varchar2(2));

 insert into t1
    select rownum
          ,lpad('X',50,'X')
          ,case when rownum = 1
            then 'Y1'
               when rownum = 2
            then 'Y2'
               when mod(rownum,2) = 0
            then 'N1'
            else 'N2'
           end
    from   dual
connect by rownum <= 100000;

create index i1 on t1(col1,flag);

exec dbms_stats.gather_table_stats(user ,'t1');

A simple model with an index which, as we will see later, has been engineered so that it will be skip scanned. The next lines of code will store a SPM plan for a given query using default optimizer parameters

SQL> var n varchar2(2);
SQL> exec :n := 'Y1'

SQL> alter session set optimizer_capture_sql_plan_baselines=TRUE;

SQL> select count(1) from t1 where flag = :n;

SQL> /

SQL> alter session set optimizer_capture_sql_plan_baselines=FALSE;

SQL> select count(1) from t1 where flag = :n;

SQL_ID  5k94675mwqz5j, child number 0
-------------------------------------
select count(1) from t1 where flag = :n

Plan hash value: 2775586896
-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |       |    67 (100)|          |
|   1 |  SORT AGGREGATE  |      |     1 |     3 |            |          |
|*  2 |   INDEX SKIP SCAN| I1   | 25000 | 75000 |    67   (2)| 00:00:01 |
-------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T1@SEL$1

Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_SS(@"SEL$1" "T1"@"SEL$1" ("T1"."COL1" "T1"."FLAG"))
END_OUTLINE_DATA
*/

Peeked Binds (identified by position):
--------------------------------------
1 - :N (VARCHAR2(30), CSID=873): 'Y1'

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FLAG"=:N)
   filter("FLAG"=:N)

Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]

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

A simple query constrained by a SPM plan using index skip scan access.

Having presented the model  I can now start my real investigations which can be summarize via this simple question : if I alter my current environment so that I will disable the use of index skip scan, will my query still be using the SPM plan? Or in more subtle and clear words: will the SPM plan be reproducible under such a change of an undocumented parameter value?

And the answer is : see below

SQL> alter session set "_optimizer_skip_scan_enabled"=FALSE;

SQL> alter session set optimizer_use_sql_plan_baselines = FALSE;

I have altered the hidden parameter and put on hold for a moment the use of sql plan baseline in order to show you that under this circumstances the CBO will come up with a new plan as shown below:

SQL> select count(1) from t1 where flag = :n;

SQL_ID  5k94675mwqz5j, child number 3
-------------------------------------
select count(1) from t1 where flag = :n

Plan hash value: 129980005
------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |       |       |    71 (100)|          |
|   1 |  SORT AGGREGATE       |      |     1 |     3 |            |          |
|*  2 |   INDEX FAST FULL SCAN| I1   | 25000 | 75000 |    71   (6)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)

There is now a new plan using an INDEX FAST FULL SCAN since I have disabled the use of INDEX SKIP SCAN. It is a clear indication that under this undocumented parameter change the CBO is not able to reproduce the index skip scan plan. Let’s see then what happen to our query.

SQL> alter session set optimizer_use_sql_plan_baselines = TRUE;

SQL> select count(1) from t1 where flag = :n;

---------------------------------------------------
SQL_ID  5k94675mwqz5j, child number 5
-------------------------------------
select count(1) from t1 where flag = :n

Plan hash value: 2775586896
-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |       |    67 (100)|          |
|   1 |  SORT AGGREGATE  |      |     1 |     3 |            |          |
|*  2 |   INDEX SKIP SCAN| I1   | 25000 | 75000 |    67   (2)| 00:00:01 |
-------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T1@SEL$1

Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_SS(@"SEL$1" "T1"@"SEL$1" ("T1"."COL1" "T1"."FLAG"))
END_OUTLINE_DATA
*/

Peeked Binds (identified by position):
--------------------------------------
1 - :N (VARCHAR2(30), CSID=873): 'Y1'

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FLAG"=:N)
   filter("FLAG"=:N)

Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]

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

The CBO has been able to reproduce the SPM INDEX SKIP SCAN plan.

Bottom line: if the CBO has been able to reproduce the SPM INDEX SKIP SCAN plan under a disabled _optimizer_skip_scan_enabled parameter in the query execution environment, this means that the CBO will use the value of that hidden parameter stored during the SPM plan capture and not the value this parameter has in the current execution environment.

NLS_SORT and SPM reproducibility

I wrote a blog article which aims mainly to show what optimizer parameter the CBO will use to reproduce aSPM baseline plan: the current environment parameters or the parameters used during the SPM plan baseline capture? Having tested in this blog article only the influence of the optimizer_mode (all_rows versus first_rows mode) I ended up with a conclusion that the CBO will use the optimizer_mode parameter stored during the SPM plan capture. I have also put a careful warning that this is not a conclusion one can spread to other CBO parameters without testing; particularly that I have already seen an otn thread dealing with the inability to reproduce a SPM plan because of a change in the _optim_peek_user_binds  hidden parameter. Then, a post on oracle list about the non reproducibility of a SPM baseline following an upgrade from 10gR2 to 11gR2 prompted me to investigate the influence the NLS_SORT parameter can have on the reproducibility of a SPM baseline plan. Below are my investigations and findings:

First, the model

CREATE TABLE t
(c1 VARCHAR2(64), c2 CHAR(15), d1 DATE);

INSERT INTO t
SELECT
    mod(ABS(dbms_random.random),3)+ 1||chr(ascii('Y')) ,
    dbms_random.string('L',dbms_random.value(1,5))||rownum ,
    to_date(TO_CHAR(to_date('01/01/1980','dd/mm/yyyy'),'J') + TRUNC(dbms_random.value(1,11280)),'J')
FROM dual
CONNECT BY level <= 2e6;

ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (c1,c2) USING INDEX;

EXEC dbms_stats.gather_table_stats (USER, 't', CASCADE => true, method_opt => 'FOR ALL COLUMNS SIZE 1');

Second, the preliminaries

SQL> select * from dba_sql_plan_baselines;

no rows selected  --  no baseline yet

SQL> show parameter nls_sort

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
nls_sort                             string      BINARY

SQL> alter session set optimizer_capture_sql_plan_baselines = TRUE;

SQL> SELECT  c1
FROM t
GROUP BY c1
ORDER BY c1 ASC NULLS LAST;

C1
------
1Y
2Y
3Y

SQL> /

C1
------
1Y
2Y
3Y

SQL> alter session set optimizer_capture_sql_plan_baselines = FALSE;

SQL> select plan_name from dba_sql_plan_baselines;

PLAN_NAME
------------------------------
SQL_PLAN_90sg67694zwyj641607ca  -- one SPM plan baseline

So far I have engineered a model against which I executed a query returning data in a certain order under the classical Binary NLS_SORT parameter. I added to that situation a SPM baseline plan so that any “resembling” query will use that SPM plan. That is plan stability. This SPM plan looks like:

SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name => 'SQL_PLAN_90sg67694zwyj641607ca', format => 'ADVANCED'));

--------------------------------------------------------------------------------
SQL handle: SQL_9061e639924ff3d1
SQL text: SELECT  c1     FROM t     GROUP BY c1    ORDER BY c1 ASC NULLS LAST
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_90sg67694zwyj641607ca         Plan id: 1679165386
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Outline Data from SMB:
/*+
BEGIN_OUTLINE_DATA
INDEX(@"SEL$1" "T"@"SEL$1" ("T"."C1" "T"."C2"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.3')
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
--------------------------------------------------------------------------------
Plan hash value: 2111031280
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     3 |     9 |  2069   (5)| 00:00:06 |
|   1 |  SORT GROUP BY NOSORT|      |     3 |     9 |  2069   (5)| 00:00:06 |
|   2 |   INDEX FULL SCAN    | T_PK |  2000K|  5859K|  2069   (5)| 00:00:06 |
-----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1

Outline Data
------------
/*+
BEGIN_OUTLINE_DATA
INDEX(@"SEL$1" "T"@"SEL$1" ("T"."C1" "T"."C2"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.3')
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) "C1"[VARCHAR2,256]
2 - "C1"[VARCHAR2,256]

An index full scan with which the CBO has avoided the order by sort operation.

Now, for my “tranquility”,  I will execute my query and check if the SPM plan is used or not.

SQL> SELECT  c1
     FROM t
     GROUP BY c1
     ORDER BY c1 ASC NULLS LAST;

C1
----
1Y
2Y
3Y

SQL_ID  28dazsm20sbw6, child number 2
-------------------------------------
SELECT  c1     FROM t     GROUP BY c1    ORDER BY c1 ASC NULLS LAST
Plan hash value: 2111031280
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |       |       |  2069 (100)|          |
|   1 |  SORT GROUP BY NOSORT|      |     3 |     9 |  2069   (5)| 00:00:06 |
|   2 |   INDEX FULL SCAN    | T_PK |  2000K|  5859K|  2069   (5)| 00:00:06 |
-----------------------------------------------------------------------------

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

Great it is used.

Third, the issue presentation and discussion

What happens if, in my current environment, I change the NLS_SORT parameter?

SQL> alter session set nls_sort=french; -- I altered my current environment when compared to the SPM capture time environment

SQL> SELECT  c1
FROM t
GROUP BY c1
ORDER BY c1 ASC NULLS LAST;

C1
----
1Y
2Y
3Y

SQL_ID  28dazsm20sbw6, child number 2
-------------------------------------
SELECT  c1     FROM t     GROUP BY c1    ORDER BY c1 ASC NULLS LAST

Plan hash value: 1760210272
------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |       |       |  2451 (100)|          |
|   1 |  SORT ORDER BY        |      |     3 |     9 |  2451  (20)| 00:00:07 |
|   2 |   SORT GROUP BY NOSORT|      |     3 |     9 |  2451  (20)| 00:00:07 |
|   3 |    INDEX FULL SCAN    | T_PK |  2000K|  5859K|  2069   (5)| 00:00:06 |
------------------------------------------------------------------------------

See how my query is not using the SPM baseline plan anymore. It is using a new plan where the sort order by operation has not been eliminated by the CBO. If my query is not using the SPM plan this is because the CBO was not able to reproduce the stored Baseline plan because of nls_sort parameter change. If this means something it then means that when trying to reproduce the SPM plan the CBO uses the current nls_sort parameter.

The new CBO plan have been added to the SPM baseline for an eventual evolution

SQL> select plan_name from dba_sql_plan_baselines;

PLAN_NAME
------------------------------
SQL_PLAN_90sg67694zwyj297df088
SQL_PLAN_90sg67694zwyj641607ca

If I alter again my current nls_sort parameter so that it will match the one stored against the baseline plan, then my query will be back to its initial use of the SPM plan

 SQL> alter session set nls_sort=binary;

 SQL> SELECT  c1
      FROM t
      GROUP BY c1
      ORDER BY c1 ASC NULLS LAST;

SQL_ID  5hrfv0352fzdr, child number 0
-------------------------------------
SELECT  c1 FROM t GROUP BY c1     ORDER BY c1 ASC NULLS LAST

Plan hash value: 2111031280
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |       |       |  2069 (100)|          |
|   1 |  SORT GROUP BY NOSORT|      |     3 |     9 |  2069   (5)| 00:00:06 |
|   2 |   INDEX FULL SCAN    | T_PK |  2000K|  5859K|  2069   (5)| 00:00:06 |
-----------------------------------------------------------------------------

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

Bottom line: in contrast to the optimizer_mode parameter when it comes to NLS_SORT (and NLS_LANG) parameter, the CBO seems to use the current environment NLS_SORT value (and not the one that existed at the baseline time capture) to reproduce the stored SPM plan baseline.

What optimizer mode is used during the re-cost of accepted SPM plans?

This question is tormenting me since several months. So here it is my answer.

create table t1
    as
 with generator as (
   select  --+ materialize
          rownum id
   from dual
   connect by
   level <= 10000)
 select
    rownum                           id,
    trunc(dbms_random.value(1,1000)) n1,
    lpad(rownum,10,'0')        small_vc,
    rpad('x',100)              padding
 from
 generator   v1,
 generator   v2
 where
    rownum <= 1000000;

 create index t1_n1 on t1(id, n1);

 create table t2
   as
 with generator as (
 select  --+ materialize
     rownum id
 from dual
 connect by
 level <= 10000)
 select
    rownum                  id,
    trunc(dbms_random.value(10001,20001))   x1,
    lpad(rownum,10,'0') small_vc,
    rpad('x',100)       padding
 from
    generator   v1,
    generator   v2
 where
 rownum <= 1000000;

 create index t2_i1 on t2(x1);

 exec dbms_stats.gather_table_stats(user, 't2', method_opt => 'FOR ALL COLUMNS SIZE 1');
 exec dbms_stats.gather_table_stats(user, 't1', method_opt => 'FOR ALL COLUMNS SIZE 1');
 

Now that we are done with the model, let’s execute a query under two different optimizer modes the classical all_rows mode andthe unsuitable first_rows mode

 var n1 number
 exec :n1 := 17335

 -- all_rows
 select * from t1 where id in (select id from t2 where x1 = :n1) order by id  ;

 -- first_rows
 alter session set optimizer_mode = FIRST_ROWS;

 select * from t1 where id in (select id from t2 where x1 = :n1) order by id  ;
 

Each of the above identical queries has been honored via a different execution plan due to the optimizer mode change. In order to complete my investigation setup, I will load the above two plans from the cursor cache into a SPM baseline

 DECLARE
  l_plans_loaded  PLS_INTEGER;
 BEGIN
  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => '7bfbmwdq70vzp');
 END;
 /
 

I can now verify that I have successfully added two enabled and accepted SPM plans to be used by the above sql query

 select signature, plan_name, enabled, accepted,reproduced, optimizer_cost
 from dba_sql_plan_baselines;

 SIGNATURE PLAN_NAME                      ENA ACC REP OPTIMIZER_COST
 ---------- ------------------------------ --- --- --- --------------
 1.7442E+19 SQL_PLAN_g43cagumm9pj8886a3342 YES YES YES      100315268  --> first_rows suboptimal plan
 1.7442E+19 SQL_PLAN_g43cagumm9pj8c2e7a81f YES YES YES            203  --> all_rows optimal plan
 

And now, we are ready for the experiments

ALL_ROWS

show parameter optimizer_mode

 NAME                                 TYPE        VALUE
 ------------------------------------ ----------- --------------------
 optimizer_mode                       string      ALL_ROWS

 select * from t1 where id in (select id from t2 where x1 = :n1) order by id  ;

 94 rows selected.

 Elapsed: 00:00:00.06 --> spot the execution time for the optimal plan

 Execution Plan
 ----------------------------------------------------------
 Plan hash value: 250297636
 ----------------------------------------------------------------------------------------
 | Id  | Operation                      | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
 ----------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT               |       |   100 | 13100 |   203   (2)| 00:00:01 |
 |   1 |  NESTED LOOPS                  |       |       |       |            |          |
 |   2 |   NESTED LOOPS                 |       |   100 | 13100 |   202   (1)| 00:00:01 |
 |   3 |    SORT UNIQUE                 |       |   100 |  1000 |   100   (0)| 00:00:01 |
 |   4 |     TABLE ACCESS BY INDEX ROWID| T2    |   100 |  1000 |   100   (0)| 00:00:01 |
 |*  5 |      INDEX RANGE SCAN          | T2_I1 |   100 |       |     1   (0)| 00:00:01 |
 |*  6 |    INDEX RANGE SCAN            | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
 |   7 |   TABLE ACCESS BY INDEX ROWID  | T1    |     1 |   121 |     2   (0)| 00:00:01 |
 ----------------------------------------------------------------------------------------

 Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("X1"=:N1)
6 - access("ID"="ID")Note
 -----
 - SQL plan baseline "SQL_PLAN_g43cagumm9pj8c2e7a81f" used for this statement
 

FIRST_ROWS

 alter session set optimizer_mode = first_rows;

 select * from t1 where id in (select id from t2 where x1 = :n1) order by id  ;

 94 rows selected.

 Elapsed: 00:01:20.04

 Execution Plan
 ----------------------------------------------------------
 Plan hash value: 1518369540
 --------------------------------------------------------------------------------------
 | Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
 --------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT             |       |   100 | 13100 |   100M  (1)| 73:27:12 |
 |   1 |  NESTED LOOPS SEMI           |       |   100 | 13100 |   100M  (1)| 73:27:12 |
 |   2 |   TABLE ACCESS BY INDEX ROWID| T1    |  1000K|   115M|  5070   (2)| 00:00:14 |
 |   3 |    INDEX FULL SCAN           | T1_N1 |  1000K|       |   722   (6)| 00:00:02 |
 |*  4 |   TABLE ACCESS BY INDEX ROWID| T2    |     1 |    10 |   100   (0)| 00:00:01 |
 |*  5 |    INDEX RANGE SCAN          | T2_I1 |   100 |       |     1   (0)| 00:00:01 |
 --------------------------------------------------------------------------------------

 Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("ID"="ID")
5 - access("X1"=:N1)
 Note
 -----
 - SQL plan baseline "SQL_PLAN_g43cagumm9pj8886a3342" used for this statement
 

Under all_rows mode the CBO produces a plan that exists into the SPM baseline. This is why it has been used. Under the unsuitable first_rows mode the CBO produces a suboptimal plan which exists into the SPM baseline and this is why it  has also been used.

So far so good.

To push the investigations further, I need to disturb this smooth environment. The serious stuff start only when:

1. The CBO generates a plan that is not into the SPM baseline
2. The SPM baseline contains more than one accepted and enabled plan for the current executed query

In this case the SPM plans enter in competition. The one with the better cost will be used. For that, the CBO needs to

1. Reproduce each plan
2. Compute their cost
3. And choose the best one

But the question is: will the CBO use the current optimizer mode to re-cost the SPM plans generated with different optimizer modes? Or will it use the optimizer mode used during the SPM plan capture?

Let’s see

In order to disturb the above clean situation, I will add an extra index so that the CBO will produce a plan that doesn’t exist in the SPM baseline

SQL> create index ind_t1_extra on t1(id);

SQL> show parameter optimizer_mode

 NAME                                 TYPE        VALUE
 ------------------------------------ ----------- -----------
 optimizer_mode                       string      FIRST_ROWS

And now I will execute my query under the unsuitable first_rows mode

 SQL> select * from t1 where id in (select id from t2 where x1 = :n1) order by id  ;

 94 rows selected.
 Elapsed: 00:00:00.06

 SQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));

 SQL_ID  7bfbmwdq70vzp, child number 1
 -------------------------------------
 select * from t1 where id in (select id from t2 where x1 = :n1) order
 by id

 Plan hash value: 250297636
 ----------------------------------------------------------------------------------------
 | Id  | Operation                      | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
 ----------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT               |       |       |       |   203 (100)|          |
 |   1 |  NESTED LOOPS                  |       |       |       |            |          |
 |   2 |   NESTED LOOPS                 |       |   100 | 13100 |   202   (1)| 00:00:01 |
 |   3 |    SORT UNIQUE                 |       |   100 |  1000 |   100   (0)| 00:00:01 |
 |   4 |     TABLE ACCESS BY INDEX ROWID| T2    |   100 |  1000 |   100   (0)| 00:00:01 |
 |*  5 |      INDEX RANGE SCAN          | T2_I1 |   100 |       |     1   (0)| 00:00:01 |
 |*  6 |    INDEX RANGE SCAN            | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
 |   7 |   TABLE ACCESS BY INDEX ROWID  | T1    |     1 |   121 |     2   (0)| 00:00:01 |
 ----------------------------------------------------------------------------------------

 Query Block Name / Object Alias (identified by operation id):
 -------------------------------------------------------------
 1 - SEL$5DA710D3
 4 - SEL$5DA710D3 / T2@SEL$2
 5 - SEL$5DA710D3 / T2@SEL$2
 6 - SEL$5DA710D3 / T1@SEL$1
 7 - SEL$5DA710D3 / T1@SEL$1

 Outline Data
 -------------
 /*+
 BEGIN_OUTLINE_DATA
 IGNORE_OPTIM_EMBEDDED_HINTS
 OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
 DB_VERSION('11.2.0.3')
 ALL_ROWS                       ------------------------------> ALL_ROWS
 OUTLINE_LEAF(@"SEL$5DA710D3")
 UNNEST(@"SEL$2")
 OUTLINE(@"SEL$1")
 OUTLINE(@"SEL$2")
 INDEX_RS_ASC(@"SEL$5DA710D3" "T2"@"SEL$2" ("T2"."X1"))
 INDEX(@"SEL$5DA710D3" "T1"@"SEL$1" ("T1"."ID" "T1"."N1"))
 LEADING(@"SEL$5DA710D3" "T2"@"SEL$2" "T1"@"SEL$1")
 USE_NL(@"SEL$5DA710D3" "T1"@"SEL$1")
 NLJ_BATCHING(@"SEL$5DA710D3" "T1"@"SEL$1")
 END_OUTLINE_DATA
 */
 Peeked Binds (identified by position):
 --------------------------------------
 1 - :N1 (NUMBER): 17335

 Predicate Information (identified by operation id):
 ---------------------------------------------------
 5 - access("X1"=:N1)
 6 - access("ID"="ID")

 Column Projection Information (identified by operation id):
 -----------------------------------------------------------
 1 - "ID"[NUMBER,22], "T1"."N1"[NUMBER,22], "T1"."SMALL_VC"[VARCHAR2,40],
 "T1"."PADDING"[VARCHAR2,400]
 2 - "T1".ROWID[ROWID,10], "ID"[NUMBER,22], "T1"."N1"[NUMBER,22]
 3 - (#keys=1) "ID"[NUMBER,22]
 4 - "ID"[NUMBER,22]
 5 - "T2".ROWID[ROWID,10]
 6 - "T1".ROWID[ROWID,10], "ID"[NUMBER,22], "T1"."N1"[NUMBER,22]
 7 - "T1"."SMALL_VC"[VARCHAR2,40], "T1"."PADDING"[VARCHAR2,400]

 Note
 -----
 - SQL plan baseline SQL_PLAN_g43cagumm9pj8c2e7a81f used for this statement ----> SPM of ALL_ROWS mode
 

Spot how, despite I’ve executed my query under the first_rows mode, I ended up by using the SPM plan that corresponds to the all_rows mode. This is confirmed via the outline, the Note and the execution time shown above.

In this particular case, thanks to the new extra index, the CBO comes up with a new plan that is not into the SPM baseline as shown via the following select:

SQL> select signature, plan_name, enabled, accepted,reproduced, optimizer_cost
 from dba_sql_plan_baselines;

SIGNATURE  PLAN_NAME                      ENA ACC REP OPTIMIZER_COST
---------- ------------------------------ --- --- --- --------------
1.7442E+19 SQL_PLAN_g43cagumm9pj8886a3342 YES YES YES 100315268 -- first_rows mode
1.7442E+19 SQL_PLAN_g43cagumm9pj8c1c6dd02 YES NO  YES 100315135 -- first_rows mode with new index
1.7442E+19 SQL_PLAN_g43cagumm9pj8c2e7a81f YES YES YES 203       -- all_rows mode

The new CBO plan has been added to the SPM baseline with the ACCPETED attributed put to NO for eventual future evolution. If you want to see the new “Non Accepted” execution plan generated by the CBO then you can get it using:

SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name => 'SQL_PLAN_g43cagumm9pj8c1c6dd02'));

--------------------------------------------------------------------------------
SQL handle: SQL_f20d8a7ea734d628
SQL text: select * from t1 where id in (select id from t2 where x1 = :n1) order by id
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_g43cagumm9pj8c1c6dd02         Plan id: 3251035394
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE

Plan rows: From dictionary
--------------------------------------------------------------------------------
Plan hash value: 2352314251
---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |   100 | 13100 |   102M  (1)| 01:06:26 |
|   1 |  NESTED LOOPS SEMI           |              |   100 | 13100 |   102M  (1)| 01:06:26 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1           |  1000K|   115M| 19786   (1)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | IND_T1_EXTRA |  1000K|       |  2234   (1)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| T2           |     1 |    10 |   102   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | T2_I1        |   100 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("ID"="ID")
5 - access("X1"=:N1)

The second interesting test to do is to execute the same query this time under all_rows mode:

SQL> alter session set optimizer_mode=all_rows;

SQL> select * from t1 where id in (select id from t2 where x1 = :n1) order by id  ;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID  7bfbmwdq70vzp, child number 0
-------------------------------------
select * from t1 where id in (select id from t2 where x1 = :n1) order by id

Plan hash value: 250297636
----------------------------------------------------------------------------------------
| Id  | Operation                      | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |       |       |       |   253 (100)|          |
|   1 |  NESTED LOOPS                  |       |       |       |            |          |
|   2 |   NESTED LOOPS                 |       |   100 | 13100 |   253   (0)| 00:00:01 |
|   3 |    SORT UNIQUE                 |       |   100 |  1000 |   103   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T2    |   100 |  1000 |   103   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | T2_I1 |   100 |       |     3   (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN            | T1_N1 |     1 |       |     2   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID  | T1    |     1 |   121 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("X1"=:N1)
6 - access("ID"="ID")

Note
-----
- SQL plan baseline SQL_PLAN_g43cagumm9pj8c2e7a81f used for this statement ----> SPM of ALL_ROWS mode

Where again it is the SPM plan of the all_rows mode that has been used.

Under the all_rows mode the CBO came up with a new plan which has also been consrtained by the SPM plan having the best cost reproduced using the optimizer mode stored against this SPM baseline and not using the current environnment optimizer mode.

The new generated CBO plan under all_rows mode is shown here below:

SQL> select signature, plan_name, enabled, accepted,reproduced, optimizer_cost
 from dba_sql_plan_baselines;

SIGNATURE  PLAN_NAME                      ENA ACC REP OPTIMIZER_COST
---------- ------------------------------ --- --- --- --------------
1.7442E+19 SQL_PLAN_g43cagumm9pj8886a3342 YES YES YES 100315268 -- first_rows mode
1.7442E+19 SQL_PLAN_g43cagumm9pj8c1c6dd02 YES NO  YES 100315135 -- first_rows mode with new index
1.7442E+19 SQL_PLAN_g43cagumm9pj8c2e7a81f YES YES YES 203       -- all_rows mode
1,7442E+19 SQL_PLAN_g43cagumm9pj8ff8eddc2 YES NO YES  253       -- all_rows mode with new index

With a corresponding execution plan shown also here below:

SQL>  select * from table(dbms_xplan.display_sql_plan_baseline(plan_name => 'SQL_PLAN_g43cagumm9pj8ff8eddc2'));

--------------------------------------------------------------------------------
SQL handle: SQL_f20d8a7ea734d628
SQL text: select * from t1 where id in (select id from t2 where x1 = :n1) order by id
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_g43cagumm9pj8ff8eddc2         Plan id: 4287552962
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------
Plan hash value: 2225889631
-----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |   100 | 13100 |   253   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                  |              |       |       |            |          |
|   2 |   NESTED LOOPS                 |              |   100 | 13100 |   253   (0)| 00:00:01 |
|   3 |    SORT UNIQUE                 |              |   100 |  1000 |   103   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T2           |   100 |  1000 |   103   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | T2_I1        |   100 |       |     3   (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN            | IND_T1_EXTRA |     1 |       |     2   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID  | T1           |     1 |   121 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("X1"=:N1)
6 - access("ID"="ID")

It is time now to summarize

When the CBO comes up with a plan that exists into the SPM plan baseline then this plan is used without regards to the optimizer environment be it the current environment or the one used during the SPM plan capture

When the CBO comes up with a plan that doesn’t exists into a SPM baseline while this baseline contains more than one plan that can likely honor the CBO query, those SPM plans enter in a cost competition. This plan reproduction and cost reevaluation will be done using the optimizer mode stored against each baseline plan. It will not use the query current optimizer mode to reproduce the SPM plan. This is why we have seen and prove by example that when you execute the query under first_rows mode it is the SPM plan of the all_rows mode that has been used.

In the following picture I tried to simplify the view by showing each plan name with its corresponding plan_id.

 SQL> select plan_name, enabled, accepted,reproduced, optimizer_cost
     from dba_sql_plan_baselines;

PLAN_NAME                                     ENA ACC REP OPTIMIZER_COST  PLAN_ID
--------------------------------------------- --- --- --- --------------  ----------
SQL_PLAN_g43cagumm9pj8886a3342                YES YES YES      102039969  2288661314   -- first rows mode without new index
SQL_PLAN_g43cagumm9pj8c1c6dd02                YES NO  YES      102039425  3251035394   -- first rows mode with new index
SQL_PLAN_g43cagumm9pj8c2e7a81f                YES YES YES            253  3269961759   -- all rows mode without index
SQL_PLAN_g43cagumm9pj8ff8eddc2                YES NO  YES            253  4287552962   -- all rows mode with new index

As such I can easily explain the 10053 trace file I have obtained when trying to execute my query under the first_rows mode which of course has been constrained with the SPM plan of the all_rows mode (plan_id 3269961759)

The below CBO trace file corresponds to the above query executed under the unsuitable first_rows mode which comes up with a plan having the plan_id =  3251035394 and that has been constrained with the all_rows SPM plan having plan_id =    3269961759

SPM: statement found in SMB --> signaling the presence of accepted SPM plans
SPM: setup to add new plan to existing plan baseline, sig = 17441749208451569192, planId = 3251035394
SPM: sql stmt=select * from t1 where id in (select id from t2 where x1 = :n1) order by id

The above lines indicates that the CBO comes up with a new plan (planId = 3251035394) that is going to be added into the SPM plan baseline

While the next lines indicates that the CBO is aware of the presence of two SPM plans that are enabled and accepted the first one for the first_rows mode and the second planid for the all_rows mode

SPM: planId's of plan baseline are: 2288661314 3269961759

In the next step the CBO will try to reproduce and to re cost the two plans starting by the first one

SPM: using qksan to reproduce, cost and select accepted plan, sig = 17441749208451569192
SPM: plan reproducibility round 1 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, planId = 2288661314

Taking into account the following information

***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************

************************************
PARAMETERS WITH ALTERED VALUES
******************************
Compilation Environment Dump
optimizer_mode_hinted               = true
optimizer_features_hinted           = 12.1.0.1
optimizer_mode                      = first_rows
is_recur_flags                      = 8

Which is clearly indicating that it is using the first_rows mode to reproduce the SPM first_rows mode. What is not clear is whether this is due to the current optimizer mode or to the optimizer mode stored against the SPM plan. But things will be clear later when the CBO will try to reproduce the all_rows plan

SPM: planId in plan baseline = 2288661314, planId of reproduced plan = 2288661314
SPM: best cost so far = 102039969.31, current accepted plan cost = 102039969.31

This is the CBO saying that it has succeeded to reproduce the first_rows mode plan.

Now it will start reproducing the all_rows mode (planid = 3269961759)

SPM: plan reproducibility round 1 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, planId = 3269961759

But spot below that the compilation environment is not showing any more that the optimizer_mode to be used is the first_rows mode. Don’t forget that I have executed the query under the first_rows mode. The below CBO trace file is clearly showing that the CBO is using the all_rows mode to reproduce the all_rows SPM plan confirming that the CBO will use the optimizer mode stored against the SPM baseline when reproducing their plans.

***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************

*************************************
PARAMETERS WITH ALTERED VALUES
******************************
Compilation Environment Dump
optimizer_mode_hinted               = true
optimizer_features_hinted           = 12.1.0.1
is_recur_flags                      = 8
*************************************

PARAMETERS WITH DEFAULT VALUES
******************************
optimizer_features_enable           = 12.1.0.1
optimizer_mode                      = all_rows

And finally the CBO saying that it has also reproduced the all_rows mode

SPM: planId in plan baseline = 3269961759, planId of reproduced plan = 3269961759
SPM: best cost so far = 253.11, current accepted plan cost = 253.11

And that, as far as this plan is having the best cost, it will be used

SPM: re-parsing to generate selected accepted plan,  planId = 3269961759

Bottom line:  when the CBO is trying to reproduce a plan stored into a SPM baseline it will use the optimizer mode stored against this SPM plan and not the current environment optimizer mode. Be aware that this is not a conclusion to be extended without tests for the other optimizer parameters particularly for the hidden ones

How to attach a hinted SPM baseline to a non hinted sql query?

The last Jonathan Lewis post on RAC Plans which finished by the following phrases:

“If you’re going to hard-code hints into a query then take a look at the outline it generates when it does the right thing, and that will tell you about the 15 or so hints you’ve missed out. (Better still, consider generating an SQL Baseline from the hinted code and attaching it to the unhinted code.)”

Prompted me to write this blog post. This is also a good coincidence because I have recently answered a similar question in the French forum. Here below is an example on how to attach a SQL plan Baseline of a hinted sql query to the same query without hint.

First the usual model (thanks Dom Brooks )

 SQL> create table t1
          (col1  number
          ,col2  varchar2(50)
          ,flag  varchar2(2));

SQL>  insert into t1
          select rownum
             ,lpad('X',50,'X')
             ,case when rownum = 1
              then 'Y1'
                   when rownum = 2
              then 'Y2'
                   when mod(rownum,2) = 0
              then 'N1'
                    else 'N2'
              end
          from   dual
          connect by rownum <= 100000;

SQL> CREATE INDEX i1 ON t1 (flag);

SQL> exec dbms_stats.gather_table_stats(user, 't1');

Second my two queries, the unhinted one

SQL>  var n varchar2(2);
SQL>  exec :n := 'Y1';

SQL> select count(*), max(col2) from t1 where flag = :n;

COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

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

SQL_ID  731b98a8u0knf, child number 0
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3625400295
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |   124 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |    54 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   | 25000 |  1318K|   124   (2)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I1   | 25000 |       |    13   (8)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"=:N)

And the hinted one

SQL> select /*+ full(t1) */ count(*), max(col2) from t1 where flag = :n;

COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

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

SQL_ID  cnazndd6j7d1v, child number 0
-------------------------------------
select /*+ full(t1) */ count(*), max(col2) from t1 where flag = :n
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   241 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    54 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 25000 |  1318K|   241   (3)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)

I have two queries an unhinted and a hinted one honored via two different execution plans. I want my unhinted query to be always honored by the plan of the hinted query. In order to achieve this goal I need to

1. Create a SQL Baseline for the hinted query
2. Attach this SQL Baseline to the unhinted query

So I will start by loading the above two plans into a SPM baseline using their corresponding sql_id

-- unhinted
SQL>declare
      l_op pls_integer;
    begin
      l_op := dbms_spm.load_plans_from_cursor_cache('731b98a8u0knf');
    end;
   /

-- hinted
SQL> declare
      l_op pls_integer;
     begin
      l_op := dbms_spm.load_plans_from_cursor_cache('cnazndd6j7d1v');
     end;
    /

I can now verify that I have two accepted and enabled SPM plans

SQL> select sql_handle, plan_name, sql_text, accepted, enabled
     from dba_sql_plan_baselines;

SQL_HANDLE           PLAN_NAME                      SQL_TEXT                                      ACC  ENA
-------------------- --------- -------------------- --------------------------------------------- ---- ---
SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd8576eb1f select count(*), max(col2) from t1 where flag YES  YES
 = :n
SQL_ba68302db880365b SQL_PLAN_bnu1h5qw80dkv616acf47 select /*+ full(t1) */ count(*), max(col2) fr YES YES
 om t1 where flag = :n

At this step we are very close to our goal which is to make the unhinted sql query(index range scan) use the baseline of the same but hinted sql query(full table scan). The first thing to do is to disable the index range scan SPM plan

declare
  ln_ps number;
begin
  ln_ps := dbms_spm.alter_sql_plan_baseline(sql_handle     => 'SQL_11f0e4472549338d'
                                           ,plan_name      => 'SQL_PLAN_13w748wknkcwd8576eb1f'
                                           ,attribute_name => 'enabled'
                                           ,attribute_value => 'NO');
end;
/

so that the original index range scan is not constrained anymore by its original SPM plan

SQL> select count(*), max(col2) from t1 where flag = :n;

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

 SQL_ID 731b98a8u0knf, child number 0
 -------------------------------------
 select count(*), max(col2) from t1 where flag = :n

 Plan hash value: 3625400295
 -------------------------------------------------------------------------------------
 | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 -------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT             |      |       |       |   124 (100)|          |
 |   1 |  SORT AGGREGATE              |      |     1 |    54 |            |          |
 |   2 |   TABLE ACCESS BY INDEX ROWID| T1   | 25000 |  1318K|   124   (2)| 00:00:01 |
 |*  3 |    INDEX RANGE SCAN          | I1   | 25000 |       |    13   (8)| 00:00:01 |
 -------------------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------
 3 - access("FLAG"=:N)

And finally I will attach the full table scan SPM plan to the sql_id of the index range scan

 SQL> declare
       l_op pls_integer;
    begin
      l_op := dbms_spm.load_plans_from_cursor_cache
                 (sql_id          => 'cnazndd6j7d1v'        -- sql_id of the full table scan
                 ,plan_hash_value => 3724264953             -- plan hash value of the full table scan
                 ,sql_handle      => 'SQL_11f0e4472549338d' -- sql handle of the index range scan query
                );
   end;
/

Spot now how the goal has been achieved

SQL> select count(*), max(col2) from t1 where flag = :n;

COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

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

SQL_ID  731b98a8u0knf, child number 0
-------------------------------------
select count(*), max(col2) from t1 where flag = :n

Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   241 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    54 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 25000 |  1318K|   241   (3)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)

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

SPM baseline selection: how it works?

In my last post about SQL Plan Management (SPM) I investigated the behavior of Adaptive Cursor Sharing (ACS) feature in the presence of SPM baselines. I will now start focusing my interests on the interaction between the CBO and the SPM plan selection steps using the model of the last post.  During this entire blog article I will be working with an existing SPM baseline which contains two accepted and enabled plans as shown below:

 SIGNATURE            SQL_HANDLE               PLAN_NAME                      ENA ACC  PLAN_ID    DESCRIPTION
 -------------------- ------------------------ ------------------------------ --- --- ---------- -------------------
 1292784087274697613  SYS_SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd616acf47 YES YES 1634389831 FULL TABLE SCAN(T1)
 1292784087274697613  SYS_SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd8576eb1f YES YES 2239163167 INDEX RANGE SCAN(i1)
 

And I will try to investigate the following issues

1. SPM contains two plans (1634389831, 2239163167) and CBO comes up with a plan that matches one of these existing SPM plans
2. SPM contains two plans (1634389831, 2239163167) and I drop the i1 index (making the plan 2239163167 not anymore reproducible) and execute the query with the bind variable value that favor the index range scan
3. SPM contains two plans (1634389831, 2239163167) and I add a new index i2 that will produce a new plan which is not in the SPM baseline.
4. SPM contains two plans (1634389831, 2239163167) and I add a new index i2 that will produce a new plan which is not in the SPM baseline but I drop also the existing index i1 (making the plan 2239163167 not anymore reproducible)

In order to follow my investigation without a lot of difficulties, please would you mind to remember that plan id finishing by 831 (1634389831) corresponds to T1 Table FULL SCAN while the plan id finishing by 167 (2239163167) corresponds to the index i1 RANGE SCAN.

Let’s now embark in the investigations starting by case number 1.

Case1: SPM contains two plans and CBO comes up with a plan that matches one of these existing SPM plans

I executed my query using bind variable = ‘Y1’ which is the bind variable that favor the i1 index range scan. The CBO should come up with the index i1 range scan plan matching the existing plan in the baseline (2239163167). Let’s see how the selection occurs via the 10053 trace file

 SPM: statement found in SMB

The first thing the CBO does is signaling that it realizes the presence of a SPM baseline. Then, follows the classical CBO query optimization

 ****************
 QUERY BLOCK TEXT
 ****************
 select count(*), max(col2) from t1 where flag = :n

 Access path analysis for T1
 ***************************************
 SINGLE TABLE ACCESS PATH
 Single Table Cardinality Estimation for T1[T1]

 Table: T1  Alias: T1
 Card: Original: 100000.000000  Rounded: 9  Computed: 9.10  Non Adjusted: 9.10
 Access Path: TableScan
 Cost:  275.38  Resp: 275.38  Degree: 0
 Cost_io: 272.00  Cost_cpu: 31121440
 Resp_io: 272.00  Resp_cpu: 31121440

 Access Path: index (AllEqRange)
 Index: I1
 resc_io: 2.00  resc_cpu: 18993
 ix_sel: 0.000091  ix_sel_with_filters: 0.000091
 Cost: 2.00  Resp: 2.00  Degree: 1
 Best:: AccessPath: IndexRange                 ---> Best Access Path Index I1 Range Scan with cost =2
 Index: I1
 Cost: 2.00
 ***************************************
 

Where the CBO found that the best access path is the Index Range scan (Index I1 with a cost of 2). However, as far as the CBO has already signaled the presence of SPM plan it knows that it is constrained. It can’t decide to use the plan it comes up with without comparing it to the existing SPM plans. This is why we see the following lines into the same 10053 trace file

 SPM: cost-based plan found in the plan baseline, planId = 2239163167 ---> 167 is the index range scan
 SPM: cost-based plan was successfully matched, planId = 2239163167 --> CBO comes up with a plan that matches a SPM plan
 

That’s all for this case:  when the cost-based generated plan matches one of the existing SPM plans, the CBO will use this plan.

Case2: SPM contains two plans but Idrop the i1 index (making the plan 2239163167 not anymore reproducible) and execute the query with the index bind variable value

The 10053 trace file in this case looks as follows

 SPM: statement found in SMB

****************
QUERY BLOCK TEXT
****************
select count(*), max(col2) from t1 where flag = :n

Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T1[T1]

Table: T1  Alias: T1
Card: Original: 100000.000000  Rounded: 1  Computed: 0.50  Non Adjusted: 0.50
Access Path: TableScan
Cost:  272.96  Resp: 272.96  Degree: 0

Best:: AccessPath: TableScan        ---> CBO comes up with a FULL TABLE SCAN plan having cost = 273
Cost: 272.96
***************************************

In the absence of the i1 index, the CBO produces a FULL TABLE SCAN as the best access path. As far as this generated plan exists in the baseline it will be used as shown below:

SPM: cost-based plan found in the plan baseline, planId = 1634389831 ---> T1 FULL TABLE SCAN plan
SPM: cost-based plan was successfully matched, planId = 1634389831 --> CBO comes up with a plan that matches a SPM plan

That’s all for this case also:  when the cost-based generated plan matches one of the existing SPM plans, the CBO will use this plan.

Case 3: SPM contains two plans and I add a new index i2 that will produce a new plan which is not in the SPM baseline

In this case I executed my query with a bind variable that favors a fast full scan of the newly created index i2.  The CBO comes up with a plan that doesn’t match any plan in the SPM baseline. This is confirmed here below:

 Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T1[T1]

Table: T1  Alias: T1
Card: Original: 100000.000000  Rounded: 50563  Computed: 50563.46  Non Adjusted: 50563.46
Access Path: TableScan
Cost:  272.96  Resp: 272.96  Degree: 0

Access Path: index (index (FFS))
Index: I2
resc_io: 44.00  resc_cpu: 18103823
ix_sel: 0.000000  ix_sel_with_filters: 1.000000

Access Path: index (FFS)
Cost:  44.56  Resp: 44.56  Degree: 1
Cost_io: 44.00  Cost_cpu: 18103823
Resp_io: 44.00  Resp_cpu: 18103823

Access Path: index (AllEqRange)
Index: I1
resc_io: 995.00  resc_cpu: 26806643
ix_sel: 0.505635  ix_sel_with_filters: 0.505635
Cost: 995.83  Resp: 995.83  Degree: 1
******** End index join costing ********

Best:: AccessPath: IndexFFS    ---> I2 Index FFS of cost 44 is the best access path
Index: I2
Cost: 44.56
***************************************

The CBO comes up with an INDEX FAST FULL SCAN on the newly created index i2. Sure this will not match an existing plan baseline because we keep repeating that we have only two enabled and accepted plan baselines one for index i1 range scan and the other one for t1 table full scan. It is then very interesting to see how the CBO will react in such a situation.

SPM: planId's of plan baseline are: 2239163167 1634389831
SPM: using qksan to reproduce, cost and select accepted plan, sig = 1292784087274697613
SPM: plan reproducibility round 0 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, planId = 2239163167 --> I1 INDEX RANGE SCAN (167)

Since the CBO realizes that it has produced a non matching plan,  It will try to reproduce the existing SPM plan baseline using outline hint and session OFE.

Why the CBO tries to reproduce one of the existing SPM plan?  And why the CBO started by trying to reproduce the index range scan plan first? Why not simply use one of the existing SPM plan?

When the CBO comes up with a plan that is not in the SPM baseline, it considers that something has changed and it is not anymore sure that the existing SPM plans are still reproducible. It also considers that even if the SPM plans are reproducible their corresponding “stored” cost might have changed. This is why, as will we see later, the CBO will not only tries to reproduce the I1 index range scan plan but it will also try to reproduce the full table scan plan in order to compare their costs using current optimizer parameters and table/index statistics. If the two plans are reproducible, then the one having the best current re-computed cost will be used.

We can see this in the 10053 trace file

SELECT /*+ INDEX_RS_ASC ("T1" "I1") */ COUNT(*) "COUNT(*)",MAX("T1"."COL2") "MAX(COL2)"
FROM "MHOURI"."T1" "T1" WHERE "T1"."FLAG"=:B1

Spot how the CBO is injecting the I1 index range scan hint in order to reproduce the SPM index i1 range scan plan

Access path analysis for T1

***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T1[T1]

Table: T1  Alias: T1
Card: Original: 100000.000000  Rounded: 25000  Computed: 25000.00  Non Adjusted: 25000.00

Access Path: index (AllEqRange)
Index: I1
resc_io: 492.00  resc_cpu: 13254598
ix_sel: 0.250000  ix_sel_with_filters: 0.250000
Cost: 492.41  Resp: 492.41  Degree: 1

Best:: AccessPath: IndexRange
Index: I1
Cost: 492.41  Degree: 1  Resp: 492.41  Card: 25000.00  Bytes: 0
***************************************

SPM: planId in plan baseline = 2239163167, planId of reproduced plan = 2239163167 ---> INDEX_RS plan reproduced
SPM: best cost so far = 492.41, current accepted plan cost = 492.409691           ---> cost = 492
***************************************

Since it has successfully reproduced the I1 index range scan plan and recomputed its corresponding cost, the second step, as indicated above, will be to reproduce the full table scan plan (1634389831) and it corresponding cost

SPM: plan reproducibility round 0 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, planId = 1634389831  ---> T1 FULL TABLE SCAN (831)

CBO succeed to reproduce the FULL table scan using the plan outline hint and calculated the “new” cost to be the 272 as shown below:

SPM: planId in plan baseline = 1634389831, planId of reproduced plan = 1634389831
SPM: best cost so far = 272.96, current accepted plan cost = 272.961944

So far, the CBO succeeded to reproduce the two SPM plans baseline and to calculate their corresponding cost. It found that the FULL TABLE SCAN cost (272) is better than the cost of the I1 INDEX RANGE SCAN (492). As such, it has decide to use the SPM FULL TABLE SCAN plan.

The above preceding 10053 trace file lines show clearly  how accepted SPM plans enter in competition when the generated CBO plan is not in the SPM baseline. The CBO doesn’t rely on the cost of the plan stored in the baseline. It has to reproduce all enabled and accepted plans and compares their costs using the current session CBO parameters. 

Now that the CBO succeeded to reproduce both plans and decide to use the FULL TABLE SCAN which is the plan with the smaller cost, there is bizarrely, a supplementary step that consist of re-parsing to generate the best costed reproduced plan i.e. the T1 FULL TABLE SCAN as shown below:

SPM: statement found in SMB
SPM: re-parsing to generate selected accepted plan, planId = 1634389831

Frankly speaking I didn’t understand the need of this last re-parsing step.

That’s all for this case also:  when the cost-based generated plan doesn’t match one of the existing SPM plans, the CBO will reproduce all the SPM plans and compare their cost. The reproduced plan having the best cost will be used.

Case4: SPM contains two plans and I added a new index i2 that will produce a new plan but I also dropped the existing index i1 (making the plan 2239163167 not anymore reproducible)

In this case, I dropped the i1 (flag) index and created a new index i2 (flag, col2) and executed the query with a bind variable that usually was favoring the INDEX i1 RANGE SCAN. Before exploring the corresponding 10053 trace file, let me tell you that the CBO in this configuration will comes up with a new plan using i2 INDEX RANGE SCAN which doesn’t exist in the SPM baseline as shown below:

*******************************************
Peeked values of the binds in SQL statement
*******************************************
----- Bind Info (kkscoacd) -----
Bind#0
oacdty=01 mxl=32(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=178 siz=32 off=0
kxsbbbfp=0d019fe0  bln=32  avl=02  flg=05
value="Y1"   -----> this bind value was favoring I1 INDEX RANGE SCAN before I dropped this index
Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH
Table: T1  Alias: T1
Access Path: TableScan                     ----> Full table scan ----> cost 272
Cost:    275.38  Resp: 275.38  Degree: 0
Cost_io: 272.00  Cost_cpu: 31121440
Resp_io: 272.00  Resp_cpu: 31121440

Access Path: index (index (FFS))           ----> I2 index fast full scan ----> cost 44
Index: I2
Access Path: index (FFS)
Cost:  45.97

Cost_io: 44.00  Cost_cpu: 18103823
Resp_io: 44.00  Resp_cpu: 18103823

Access Path: index (Index Only)           ----> I2 index range scan  ---> cost 3
Index: I2
resc_io: 3.00  resc_cpu: 21564
ix_sel: 0.000005  ix_sel_with_filters: 0.000005
Cost: 3.00  Resp: 3.00  Degree: 1

Best: Access Path: Index Range            ----> best access path index range scan I2
Index: I2
Cost: 3.00
***************************************

Now that the CBO comes up with a non matching plan, as always, it will start trying to reproduce all  SPM plans. But this time, despite the index i1 range scan plan is not reproducible because I dropped that i1 index, the CBO will, nevertheless, try to reproduce this plan as shown below:

SPM: plan Ids of plan baseline are: 1634389831 2239163167
SPM: using qksan to reproduce, cost and select accepted plan, sig = 1292784087274697613
SPM: plan reproducibility round 0 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, plan Id = 1634389831

During the first round, CBO is trying to reproduce the full table scan

SPM: plan Id in plan baseline = 1634389831, plan Id of reproduced plan = 1634389831
SPM: best cost so far = 275.38, current accepted plan cost = 275.379078

I will skip the  part showing  the FULL TABLE SCAN reproduction because the CBO has successfully reproduced it and there is no added value to present it here.

The next step in this round is to reproduce the I1 index range scan which is in fact impossible (How the CBO can ignore it?)

SPM: plan reproducibility round 0 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, plan Id = 2239163167

Look below how the CBO is hinting an index I2 while trying to reproduce a plan with index I1 that is not any more present in the database

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ INDEX_RS_ASC ("T1" "I2") */ COUNT(*) "COUNT(*)",MAX("T1"."COL2") "MAX(COL2)"
FROM "MOHAMED"."T1" "T1" WHERE "T1"."FLAG"=:B1

And how naturally it will be impossible to reproduce the I1 INDEX RANGES SCAN plan

SPM: plan Id in plan baseline = 2239163167, plan Id of reproduced plan = 3187078153
SPM: failed to reproduce the plan using the following info:
parse_schema name        : MOHAMED
plan_baseline signature  : 1292784087274697613
plan_baseline plan_id    : 2239163167  ---> I1 INDEX RANGE SCAN
plan_baseline hintset    :

hint num  1 len 27 text: IGNORE_OPTIM_EMBEDDED_HINTS
hint num  2 len 37 text: OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
hint num  3 len 22 text: DB_VERSION('11.2.0.1')
hint num  4 len  8 text: ALL_ROWS
hint num  5 len 22 text: OUTLINE_LEAF(@"SEL$1")
hint num  6 len 49 text: INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."FLAG"))
SPM: generated non-matching plan:

The CBO failed to reproduce the I1 index range scan and it produces an I2 index range scan instead. However the CBO is still considering that the game is not over and that there is a second chance. This is why we see the following lines about round 2 in the same  10053 trace file

SPM: plan reproducibility round 2 (hinted OFE only)
SPM: using qksan to reproduce accepted plan, plan Id = 2239163167  ---> I1 INDEX RANGE SCAN

And the evident conclusion of a non reproducible plan even in this round 2

SPM: failed to reproduce the plan using the following info:
parse_schema name        : MOHAMED
plan_baseline signature  : 1292784087274697613
plan_baseline plan_id    : 2239163167
plan_baseline hintset    :
hint num  1 len 37 text: OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

SPM: generated non-matching plan:

----- Explain Plan Dump -----
----- Plan Table -----

============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id  | Operation          | Name    | Rows  | Bytes | Cost  | Time      |
-------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT   |         |       |       |    42 |           |
| 1   |  SORT AGGREGATE    |         |     1 |    54 |       |           |
| 2   |   INDEX RANGE SCAN | I2      |   24K | 1318K |    42 |  00:00:01 |
-------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("FLAG"=:N)

Content of other_xml column
===========================
db_version     : 11.2.0.1
parse_schema   : MOHAMED
plan_hash      : 2583336616
plan_hash_2    : 3187078153 ----> I2 INDEX RANGE SCAN

Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."FLAG" "T1"."COL2"))  ---> (FLAG,COL2) are the I2 index columns
END_OUTLINE_DATA
*/
------- END SPM Plan Dump -------

Finally, after two impossible rounds, the CBO recognizes, what it should have recognized much earlier, that it is impossible to reproduce the I1 index range scan and decided to use the unique SPM plan it succeeded to reproduce .i.e. T1 TABLE FULL SCAN not without a extra re-parsing step

SPM: re-parsing to generate selected accepted plan, plan Id = 1634389831 ---> FULL TABLE SCAN

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ FULL ("T1") */ COUNT(*) "COUNT(*)",MAX("T1"."COL2") "MAX(COL2)"
FROM "MOHAMED"."T1" "T1" WHERE "T1"."FLAG"=:B1
============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |
--------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |         |       |       |   275 |           |
| 1   |  SORT AGGREGATE     |         |     1 |    54 |       |           |
| 2   |   TABLE ACCESS FULL | T1      |     1 |    54 |   275 |  00:00:04 |
--------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter("FLAG"=:N)

Content of other_xml column
===========================
db_version     : 11.2.0.1
parse_schema   : MOHAMED
plan_hash      : 3724264953
plan_hash_2    : 1634389831
Peeked Binds

============
Bind variable information
position=1
datatype(code)=1
datatype(string)=VARCHAR2(32)
char set id=178
char format=1
max length=32
value=Y1

Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/

Bottom line: when using SPM baseline to guarantee plan stability, be warn that when you have several enabled and accepted plan for the same SQL matching signature and, if for any reason those plans become non reproducible,  you might pay a parsing time penalty because the CBO will use two rounds trying to reproduce all SPM plans – even though they are impossible to reproduce–