Which execution plan will be used?

Following a question raised by Franck Pachot on Twitter, I was surprised that 43% of the answers were false. The question was terribly simple : an SQL Plan Baseline contains two execution plans:

• Plan 1: is enabled but not accepted
• Plan 2: is not enabled but accepted

If the Cost Based Optimiser comes up with Plan 1, which execution plan will the query finally be allowed to use?

Quiz. A SQL Plan Baseline has two plans:
Plan 1: enabled but not accepted
Plan 2: accepted but not enabled
The CBO comes with Plan 1
Which plan is used?

— Franck Pachot (@FranckPachot) March 7, 2018

There are very simple rules to know when it comes to the interaction between the CBO and the presence of SPM baselined plans:

• Not enabled SPM plans will not be used regardless of their acceptance status
• Enabled SPM plans will not be used if they are not accepted

From Franck’s question we understand that SPM Plan 2 is not enabled; so we will rule it out from the correct answer possibilities. We can see as well that SPM Plan 1 is not accepted which makes it unusable by the CBO. Since both SPM execution plans are not usable because of their enabled or accepted properties the CBO will use the plan it will come up with : Plan 1 in this case.

Here below the demonstration (the model can be found here):

alter session set cursor_sharing= force;
alter session set optimizer_capture_sql_plan_baselines=true;

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

alter session set optimizer_capture_sql_plan_baselines=false;

select  sql_handle, plan_name, enabled, accepted
from dba_sql_plan_baselines
where signature = '7542536418437113777';

SQL_HANDLE             PLAN_NAME                      ENA ACC
---------------------- ------------------------------ --- ---
SQL_68ac78e9166427b1   SQL_PLAN_6jb3sx4b689xj3069e6f9 YES YES

DECLARE
   l_p  PLS_INTEGER;
BEGIN
  l_p := DBMS_SPM.alter_sql_plan_baseline(
            sql_handle      => 'SQL_68ac78e9166427b1',
            plan_name       => 'SQL_PLAN_6jb3sx4b689xj3069e6f9',
            attribute_name  => 'enabled',
            attribute_value => 'NO');
END;
/

select  sql_handle, plan_name, enabled, accepted
from dba_sql_plan_baselines
where signature = '7542536418437113777';

SQL_HANDLE                     PLAN_NAME                      ENA ACC
------------------------------ ------------------------------ --- ---
SQL_68ac78e9166427b1           SQL_PLAN_6jb3sx4b689xj3069e6f9 NO  YES

We have our Plan 2: Accepted but not Enabled.

Next I will generate Plan 1: Enabled but not Accepted

select count(1) from t_acs where n2 = 1;

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

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

SQL> select * from table(dbms_xplan.display_cursor)


SQL_ID  7ck8k47bnqpnv, child number 1

An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

NOTE: cannot fetch plan for SQL_ID: 7ck8k47bnqpnv, CHILD_NUMBER: 1
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)

Whenever I see this error I know that it has been raised because the CBO comes up with an execution plan that doesn’t match one of the SPM baselined plans:

select  sql_handle, plan_name, enabled, accepted
from dba_sql_plan_baselines
where signature = '7542536418437113777';

SQL_HANDLE                     PLAN_NAME                      ENA ACC
------------------------------ ------------------------------ --- ---
SQL_68ac78e9166427b1           SQL_PLAN_6jb3sx4b689xj3069e6f9 NO  YES
SQL_68ac78e9166427b1           SQL_PLAN_6jb3sx4b689xjf5f8c88e YES NO

Here we go: we have an SPM baseline with two execution plans exactly as asked by Franck

select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SQL_68ac78e9166427b1'));
 
--------------------------------------------------------------------------------
SQL handle: SQL_68ac78e9166427b1
SQL text: select count(:"SYS_B_0") from t_acs where n2 = :"SYS_B_1"
--------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_6jb3sx4b689xjf5f8c88e         Plan id: 4126722190
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------
 
Plan hash value: 1687207741
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |     3 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |            |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| T_ACS_IDX1 |     1 |     3 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("N2"=:SYS_B_1)
 
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_6jb3sx4b689xj3069e6f9         Plan id: 812246777
Enabled: NO      Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------
 
Plan hash value: 535703726
----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |     3 |   628   (3)| 00:00:01 |
|   1 |  SORT AGGREGATE    |       |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T_ACS |  1099K|  3219K|   628   (3)| 00:00:01 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("N2"=:SYS_B_1)

Next I will execute the above query with a bind variable value that will favor the index range scan plan which is present in the SPM baseline, is enabled but not accepted:

SQL> @53on
alter session set events '10053 trace name context forever, level 1';
alter session set "_optimizer_trace"=all;

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

SQL_ID  7ck8k47bnqpnv, child number 1

Plan hash value: 1687207741
--------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes |
--------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |
|   1 |  SORT AGGREGATE   |            |     1 |     3 |
|*  2 |   INDEX RANGE SCAN| T_ACS_IDX1 |   100 |   300 |
--------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("N2"=:SYS_B_1)

SQL> @53off
alter session set events '10053 trace name context off';

The absence of the Note at the bottom of the above execution plan indicates that the CBO didn’t use any execution plan from the SPM baseline but it uses rather the execution plan it comes up with during the compilation phase. In the corresponding 10053 trace file we can read the following lines:

SPM: statement found in SMB

SPM: finding a match for the generated plan, planId = 4126722190
SPM: plan baseline non-existant or empty (no accepted plans) so using cost-based plan, planId = 4126722190

The developer at Oracle could not be clearer than this : non-existant or empty (no accepted plans) so using cost-based plan

SQL Plan Baseline, SQL Profile and materialized CTE

This is a reformulated simple answer to one of my colleague’s question of whether a materialized CTE would affect the functioning of a SQL Profile or a SQL Plan Baseline or both.

A very short answer is : it will not affect neither the first nor the second

A little bit more elaborated answer is:

• it will not affect the use of SQL Profile because the transient name of the materialized SYS TEMP table is not part of the force matching signature of both the SQL query and the SQL profile
• The changing generated name of the materialized SYS TEMP table has no effect on the PHV2 of the generated CBO plan.

An answer with demonstration is:

-- table
create table t_st 
as select 
  rownum n1, mod(rownum,5) n2, trunc((rownum-1/4)) n3, dbms_random.value(20,30) n4
from dual 
connect by level <=1e4;

-- query
with got_my_t2 as
 (select /*+ materialize */ n1, d1 from t2_st where n1 <= 11)
 select
      max(n4)
 from t_st a
 where exists
    (select null
     from got_my_t2 b
     where a.n1 = b.n1);

-- execution plan
select * from table(dbms_xplan.display_cursor);

SQL_ID  53fhbt0gjrwb3, child number 0
-------------------------------------
Plan hash value: 1035791491
--------------------------------------------------------------------------------------
| Id  | Operation                                | Name                      | Rows  |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                           |       |
|   1 |  TEMP TABLE TRANSFORMATION               |                           |       |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6667_32BBFD |       |
|*  3 |    TABLE ACCESS FULL                     | T2_ST                     |    11 |
|   4 |   SORT AGGREGATE                         |                           |     1 |
|*  5 |    HASH JOIN RIGHT SEMI                  |                           |    11 |
|   6 |     VIEW                                 |                           |    11 |
|   7 |      TABLE ACCESS FULL                   | SYS_TEMP_0FD9D6667_32BBFD |    11 |
|   8 |     TABLE ACCESS FULL                    | T_ST                      | 10000 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("N1"<=11)
   5 – access("A"."N1"="B"."N1")

To the above setup I am going to add a SQL profile and a SQL plan baseline as shown below:

–- first SQL profile using Oracle script
SQL>@coe_xfr_sql_profile

Parameter 1:
SQL_ID (required)
Enter value for 1: 53fhbt0gjrwb3

PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     1035791491        ,014

Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 1035791491

SQL>@coe_xfr_sql_profile_53fhbt0gjrwb3_1035791491.sql

... manual custom SQL Profile has been created
COE_XFR_SQL_PROFILE_53fhbt0gjrwb3_1035791491 completed

-- second a SQL Plan Baseline
declare
   spm_op pls_integer;
begin
   spm_op := dbms_spm.load_plans_from_cursor_cache (sql_id => '53fhbt0gjrwb3');
end;
/

As such, re-executing the initial query will show the above double execution plan protection in action:

with got_my_t2 as
 (select /*+ materialize */ n1, d1 from t2_st where n1 <= 11)
 select
      max(n4)
 from t_st a
 where exists
    (select null
     from got_my_t2 b
     where a.n1 = b.n1);

Plan hash value: 1035791491
--------------------------------------------------------------------------------------
| Id  | Operation                                | Name                      | Rows  |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                           |       |
|   1 |  TEMP TABLE TRANSFORMATION               |                           |       |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6668_32BBFD |       |
|*  3 |    TABLE ACCESS FULL                     | T2_ST                     |    11 |
|   4 |   SORT AGGREGATE                         |                           |     1 |
|*  5 |    HASH JOIN RIGHT SEMI                  |                           |    11 |
|   6 |     VIEW                                 |                           |    11 |
|   7 |      TABLE ACCESS FULL                   | SYS_TEMP_0FD9D6668_32BBFD |    11 |
|   8 |     TABLE ACCESS FULL                    | T_ST                      | 10000 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("N1"<=11)
   5 - access("A"."N1"="B"."N1")

Note
-----
   - SQL profile coe_53fhbt0gjrwb3_1035791491 used for this statement
   - SQL plan baseline SQL_PLAN_a9tbt2yhxacpz46c3c689 used for this statement

Now that I have built the setup I would like to demonstrate that if the name of the TEMP table transformation

SYS_TEMP_0FD9D6668_32BBFD

changes it will not prevent both the SQL Profile and the SPM baseline from constraining the initial query to stick with the fixed execution plan. Before going further I would like to get the PHV2 of the above execution plan:

SELECT
               p.sql_id
              ,p.plan_hash_value
              ,p.child_number
              ,t.phv2
        FROM   v$sql_plan p
              ,xmltable('for $i in /other_xml/info
                        where $i/@type eq "plan_hash_2"
                        return $i'
                        passing xmltype(p.other_xml)
                        columns phv2 number path '/') t
          WHERE p.sql_id = '&1'
          and   p.other_xml is not null;

SQL>@phv2
Enter value for 1: 53fhbt0gjrwb3

SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER       PHV2
------------- --------------- ------------ ----------
53fhbt0gjrwb3      1035791491            0 1187235465

Let’s now force Oracle to use a new name for the materialized CTE

 
SQL>alter system flush shared_pool;
System altered.

with got_my_t2 as
 (select /*+ materialize */ n1, d1 from t2_st where n1 <= 11)
 select
      max(n4)
 from t_st a
 where exists
    (select null
     from got_my_t2 b
     where a.n1 = b.n1);

Plan hash value: 1035791491
--------------------------------------------------------------------------------------
| Id  | Operation                                | Name                      | Rows  |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                           |       |
|   1 |  TEMP TABLE TRANSFORMATION               |                           |       |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6669_32BBFD |       |
|*  3 |    TABLE ACCESS FULL                     | T2_ST                     |    11 |
|   4 |   SORT AGGREGATE                         |                           |     1 |
|*  5 |    HASH JOIN RIGHT SEMI                  |                           |    11 |
|   6 |     VIEW                                 |                           |    11 |
|   7 |      TABLE ACCESS FULL                   | SYS_TEMP_0FD9D6669_32BBFD |    11 |
|   8 |     TABLE ACCESS FULL                    | T_ST                      | 10000 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("N1"<=11)
   5 - access("A"."N1"="B"."N1")

Note
-----
   - SQL profile coe_53fhbt0gjrwb3_1035791491 used for this statement
   - SQL plan baseline SQL_PLAN_a9tbt2yhxacpz46c3c689 used for this statement

As you can see Oracle has derived a new CTE TEMP Table name:

 
SYS_TEMP_0FD9D6669_32BBFD

which, as expected, has not prevented both the SPM and the SQL Profile from being used. Furthermore, despite the TEMP table name change, the PHV2 of the generated plan is still identical to the first one mentioned above:

 
SQL>@phv2
Enter value for 1: 53fhbt0gjrwb3

SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER       PHV2
------------- --------------- ------------ ----------
53fhbt0gjrwb3      1035791491            0 1187235465

Bottom Line : don’t worry about your materialized Common Table Expression used in critical queries that you want to stabilize via a SQL profile or a SQL Plan Baseline. Their changing name will not affect these two execution plan stabilizing techniques.

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 : 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

Real Time SQL Monitoring oddity

This is a small note about a situation I have encountered and which I thought it is worth sharing with you. There was an insert/select executing in parallel DOP 16 on a 11.2.0.3 Oracle database for which the end user was complaining about the exceptional time it was taking without completing. Since the job was still running I tried getting its Real Time SQL monitoring report:

Global Information
------------------------------
 Status              :  DONE (ERROR)        
 Instance ID         :  1                   
 Session             :  XXXXX (392:229)    
 SQL ID              :  bbccngk0nn2z2       
 SQL Execution ID    :  16777216            
 Execution Started   :  06/22/2015 11:57:06 
 First Refresh Time  :  06/22/2015 11:57:06 
 Last Refresh Time   :  06/22/2015 11:57:46 
 Duration            :  40s                 

Global Stats
=================================================================================================
| Elapsed |   Cpu   |    IO    | Concurrency |  Other   | Buffer | Read | Read  | Write | Write |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) |  Gets  | Reqs | Bytes | Reqs  | Bytes |
=================================================================================================
|   15315 |   15220 |       54 |        0.38 |       40 |     2G | 8601 |   2GB |  5485 |   1GB |

The insert/select according to the above report summary is DONE with (ERROR).
So why the end user is still complaining about the not ending batch job? And why he didn’t receive an error?

After having ruled out the resumable time out hypothesis I came back to the v$sql_monitor and issued the following two selects:

SQL> SELECT
  2    sql_id,
  3    process_name,
  4    status
  5  FROM v$sql_monitor
  6  WHERE sql_id = 'bbccngk0nn2z2'
  7  AND status   ='EXECUTING'
  8  ORDER BY process_name ;

SQL_ID        PROCE STATUS
------------- ----- ------------
bbccngk0nn2z2 p000  EXECUTING
bbccngk0nn2z2 p001  EXECUTING
bbccngk0nn2z2 p002  EXECUTING
bbccngk0nn2z2 p003  EXECUTING
bbccngk0nn2z2 p004  EXECUTING
bbccngk0nn2z2 p005  EXECUTING
bbccngk0nn2z2 p006  EXECUTING
bbccngk0nn2z2 p007  EXECUTING
bbccngk0nn2z2 p008  EXECUTING
bbccngk0nn2z2 p009  EXECUTING
bbccngk0nn2z2 p010  EXECUTING
bbccngk0nn2z2 p011  EXECUTING
bbccngk0nn2z2 p012  EXECUTING
bbccngk0nn2z2 p013  EXECUTING
bbccngk0nn2z2 p014  EXECUTING
bbccngk0nn2z2 p015  EXECUTING
bbccngk0nn2z2 p019  EXECUTING
bbccngk0nn2z2 p031  EXECUTING

SQL> SELECT
  2    sql_id,
  3    process_name,
  4    status
  5  FROM v$sql_monitor
  6  WHERE sql_id = 'bbccngk0nn2z2'
  7  AND status   ='DONE (ERROR)'
  8  ORDER BY process_name ;

SQL_ID        PROCE STATUS
------------- ----- -------------------
bbccngk0nn2z2 ora   DONE (ERROR)
bbccngk0nn2z2 p016  DONE (ERROR)
bbccngk0nn2z2 p017  DONE (ERROR)
bbccngk0nn2z2 p018  DONE (ERROR)
bbccngk0nn2z2 p020  DONE (ERROR)
bbccngk0nn2z2 p021  DONE (ERROR)
bccngk0nn2z2  p022  DONE (ERROR)
bbccngk0nn2z2 p023  DONE (ERROR)
bbccngk0nn2z2 p024  DONE (ERROR)
bbccngk0nn2z2 p025  DONE (ERROR)
bbccngk0nn2z2 p026  DONE (ERROR)
bbccngk0nn2z2 p027  DONE (ERROR)
bbccngk0nn2z2 p028  DONE (ERROR)
bbccngk0nn2z2 p029  DONE (ERROR)
bbccngk0nn2z2 p030  DONE (ERROR)

Among the 32 parallel servers half are executing and half are in error! How could this be possible? I have already been confronted to a parallel process that ends in its entirety when a single parallel server is in error. For example I have encountered several times the following error which is due to a parallel broadcast distribution of a high data row source exploding henceforth the TEMP tablespace:

ERROR at line 1:
ORA-12801: error signaled in parallel query server P013
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

A simple select against v$active_session_history confirmed that the insert/select is still running and it is consuming CPU

SQL> select sql_id, count(1)
  2  from gv$active_session_history
  3  where sample_time between to_date('22062015 12:30:00', 'ddmmyyyy hh24:mi:ss')
  4                    and     to_date('22062015 13:00:00', 'ddmmyyyy hh24:mi:ss')
  5  group by  sql_id
  6  order by 2 desc;

SQL_ID          COUNT(1)
------------- ----------
bbccngk0nn2z2       2545
                       4
0uuczutvk6jqj          1
8f1sjvfxuup9w          1

SQL> select decode(event,null, 'on cpu', event), count(1)
  2  from gv$active_session_history
  3  where sample_time between to_date('22062015 12:30:00', 'ddmmyyyy hh24:mi:ss')
  4                    and     to_date('22062015 13:00:00', 'ddmmyyyy hh24:mi:ss')
  5  and sql_id = 'bbccngk0nn2z2'
  6  group by  event
  7  order by 2 desc;

DECODE(EVENT,NULL,'ONCPU',EVENT)  COUNT(1)
--------------------------------  ---------
on cpu                            5439
db file sequential read           3

SQL> /

DECODE(EVENT,NULL,'ONCPU',EVENT)  COUNT(1)
--------------------------------- ---------
on cpu                            5460
db file sequential read           3

SQL> /

DECODE(EVENT,NULL,'ONCPU',EVENT)  COUNT(1)
--------------------------------  ---------
on cpu                            5470
db file sequential read           3

And after a while

SQL> /

DECODE(EVENT,NULL,'ONCPU',EVENT)   COUNT(1)
---------------------------------- ---------
on cpu                             15152
db file sequential read            9

While the parallel insert is still running I took several SQL monitoring reports of which the two followings ones:

Parallel Execution Details (DOP=16 , Servers Allocated=32)
============================================================================================
|      Name      | Type  | Server# | Elapsed |Buffer | Read  |         Wait Events         |
|                |       |         | Time(s) | Gets  | Bytes |         (sample #)          |
============================================================================================
| PX Coordinator | QC    |         |    0.48 |  2531 | 16384 |                             |
| p000           | Set 1 |       1 |    1049 |  128M |  63MB | direct path read (1)        |
| p001           | Set 1 |       2 |    1518 |  222M |  61MB |                             |
| p002           | Set 1 |       3 |     893 |  109M |  59MB |                             |
| p003           | Set 1 |       4 |    1411 |  194M |  62MB | direct path read (1)        |
| p004           | Set 1 |       5 |     460 |   64M |  62MB | direct path read (1)        |
| p005           | Set 1 |       6 |     771 |   87M | 322MB | direct path read (1)        |
|                |       |         |         |       |       | direct path read temp (5)   |
| p006           | Set 1 |       7 |     654 |   67M |  62MB | direct path read (1)        |
| p007           | Set 1 |       8 |     179 |   24M |  55MB | direct path read (1)        |
| p008           | Set 1 |       9 |    1638 |  235M |  70MB |                             |
| p009           | Set 1 |      10 |     360 |   46M |  54MB | direct path read (1)        |
| p010           | Set 1 |      11 |    1920 |  294M | 337MB | direct path read temp (6)   | --> 1920s
| p011           | Set 1 |      12 |     289 |   30M |  69MB |                             |
| p012           | Set 1 |      13 |     839 |   98M |  66MB | direct path read (1)        |
| p013           | Set 1 |      14 |     524 |   63M |  55MB |                             |
| p014           | Set 1 |      15 |    1776 |  263M |  69MB |                             |
| p015           | Set 1 |      16 |    1016 |  130M |  61MB | direct path read (1)        |
| p016           | Set 2 |       1 |    0.22 |  1166 |   3MB |                             |
| p017           | Set 2 |       2 |    1.36 |  6867 |  51MB |                             |
| p018           | Set 2 |       3 |    1.02 |  1298 |  36MB |                             |
| p019           | Set 2 |       4 |    6.71 |  2313 | 129MB | direct path read temp (2)   |
| p020           | Set 2 |       5 |    0.40 |   978 |  16MB |                             |
| p021           | Set 2 |       6 |    1.32 |  8639 |  41MB | direct path read temp (1)   |
| p022           | Set 2 |       7 |    0.18 |   896 |   2MB |                             |
| p023           | Set 2 |       8 |    0.23 |   469 |   9MB |                             | --> 0.23s
| p024           | Set 2 |       9 |    0.52 |  3635 |  19MB |                             | --> 0.52s
| p025           | Set 2 |      10 |    0.33 |  1163 |   3MB |                             |
| p026           | Set 2 |      11 |    0.65 |   260 |  31MB | db file sequential read (1) |
| p027           | Set 2 |      12 |    0.21 |  1099 |   6MB |                             |
| p028           | Set 2 |      13 |    0.58 |   497 |  20MB |                             |
| p029           | Set 2 |      14 |    1.43 |  4278 |  54MB |                             |
| p030           | Set 2 |      15 |    0.30 |  3481 |   8MB |                             |
| p031           | Set 2 |      16 |    2.86 |   517 |  91MB |                             |
============================================================================================

Parallel Execution Details (DOP=16 , Servers Allocated=32)
=============================================================================================
|      Name      | Type  | Server# | Elapsed | Buffer | Read  |         Wait Events         |
|                |       |         | Time(s) |  Gets  | Bytes |         (sample #)          |
=============================================================================================
| PX Coordinator | QC    |         |    0.48 |   2531 | 16384 |                             |
| p000           | Set 1 |       1 |    1730 |   202M |  63MB | direct path read (1)        |
| p001           | Set 1 |       2 |    2416 |   351M |  61MB |                             |
| p002           | Set 1 |       3 |    1094 |   133M |  59MB |                             |
| p003           | Set 1 |       4 |    2528 |   348M |  64MB | direct path read (1)        |
| p004           | Set 1 |       5 |     965 |   129M |  63MB | direct path read (1)        |
| p005           | Set 1 |       6 |    1089 |   129M | 322MB | direct path read (1)        |
|                |       |         |         |        |       | direct path read temp (5)   |
| p006           | Set 1 |       7 |    1459 |   165M |  62MB | direct path read (1)        |
| p007           | Set 1 |       8 |     221 |    30M |  55MB | direct path read (1)        |
| p008           | Set 1 |       9 |    2640 |   357M |  70MB |                             |
| p009           | Set 1 |      10 |     952 |   115M |  54MB | direct path read (1)        |
| p010           | Set 1 |      11 |    3117 |   471M | 337MB | direct path read temp (6)   | --> 3117s
| p011           | Set 1 |      12 |     400 |    42M |  69MB |                             |
| p012           | Set 1 |      13 |    1621 |   195M |  66MB | direct path read (1)        |
| p013           | Set 1 |      14 |    1126 |   132M |  55MB |                             |
| p014           | Set 1 |      15 |    2662 |   370M |  72MB |                             |
| p015           | Set 1 |      16 |    1194 |   147M |  61MB | direct path read (1)        |
| p016           | Set 2 |       1 |    0.22 |   1166 |   3MB |                             |
| p017           | Set 2 |       2 |    1.36 |   6867 |  51MB |                             |
| p018           | Set 2 |       3 |    1.02 |   1298 |  36MB |                             |
| p019           | Set 2 |       4 |    6.72 |   2313 | 131MB | direct path read temp (2)   |
| p020           | Set 2 |       5 |    0.40 |    978 |  16MB |                             |
| p021           | Set 2 |       6 |    1.32 |   8639 |  41MB | direct path read temp (1)   |
| p022           | Set 2 |       7 |    0.18 |    896 |   2MB |                             |
| p023           | Set 2 |       8 |    0.23 |    469 |   9MB |                             | --> 0.23s
| p024           | Set 2 |       9 |    0.52 |   3635 |  19MB |                             | --> 0.52s
| p025           | Set 2 |      10 |    0.33 |   1163 |   3MB |                             |
| p026           | Set 2 |      11 |    0.65 |    260 |  31MB | db file sequential read (1) |
| p027           | Set 2 |      12 |    0.21 |   1099 |   6MB |                             |
| p028           | Set 2 |      13 |    0.58 |    497 |  20MB |                             |
| p029           | Set 2 |      14 |    1.43 |   4278 |  54MB |                             |
| p030           | Set 2 |      15 |    0.30 |   3481 |   8MB |                             |
| p031           | Set 2 |      16 |    2.89 |    517 |  92MB |                             |
=============================================================================================

If you look carefully to the above reports you will notice that the elapsed time of the parallel servers mentioned being in ERROR (p16-p30) is not increasing in contrast to the elapsed time of the parallel servers mentioned being in EXECUTION (p0-p15) which is continuously increasing.

Thanks to Randolf Geist (again) I knew that there is a bug in Real time SQL monitoring report which occurs when a parallel server is not working for more than 30 minutes. In such a case the Real time SQL monitoring will starts showing those parallel severs in in ERROR confusing the situation.

As far as I was able to reproduce the issue I started the process again at 16h03 and I kept executing the following select from time to time having no rows for each execution

SELECT 
  sql_id,
  process_name,
  status
FROM v$sql_monitor
WHERE sql_id = '5np4u0m0h69jx' –- changed a little bit the sql_id
AND status   ='DONE (ERROR)'
ORDER BY process_name ;

no rows selected

Until at around 16h37 i.e. after 30 minutes (and a little bit more) of execution the above select started showing processes in error:

SQL> SELECT
  2    sql_id,
  3    process_name,
  4    status
  5  FROM v$sql_monitor
  6  WHERE sql_id = '5np4u0m0h69jx'
  7  AND status   ='DONE (ERROR)'
  8  ORDER BY process_name ;

SQL_ID        PROCE STATUS
------------- ----- ---------------
5np4u0m0h69jx ora   DONE (ERROR)
5np4u0m0h69jx p016  DONE (ERROR)
5np4u0m0h69jx p017  DONE (ERROR)
5np4u0m0h69jx p018  DONE (ERROR)
5np4u0m0h69jx p020  DONE (ERROR)
5np4u0m0h69jx p021  DONE (ERROR)
5np4u0m0h69jx p022  DONE (ERROR)
5np4u0m0h69jx p023  DONE (ERROR)
5np4u0m0h69jx p024  DONE (ERROR)
5np4u0m0h69jx p025  DONE (ERROR)
5np4u0m0h69jx p026  DONE (ERROR)
5np4u0m0h69jx p027  DONE (ERROR)
5np4u0m0h69jx p028  DONE (ERROR)
5np4u0m0h69jx p029  DONE (ERROR)
5np4u0m0h69jx p030  DONE (ERROR)

At the very beginning of the process several parallel servers was not running while several others were busy. And when the first parallel server (p10 in this case) reaches more than 1800 seconds (1861 seconds in this case) the real time Sql monitoring started showing the not working parallel servers in ERROR.

Bottom line: don’t be confused (as I have been) by that DONE (ERROR) status, your SQL statement might still be running consuming time and energy despite this wrong real time SQL monitoring reporting status

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.

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

SQL Plan Management and Indexes: Part ii

It is strange sometimes in the Oracle word to find a similarity between two concepts that seem to be not related at all. These two concepts I am insinuating to are the functioning of indexes in (a) covering the deadlock threat of unindexed foreign key constraints and (b) reproducibility of a SQL plan baseline.

In my last post about the reproducibility of an index based SQL plan baseline I have found that this plan baseline ceases to be used when

1.    The index is dropped (obvious)
2.    The index is renamed(not obvious)
3.    The leading column(s) of the index is (are) changed

In this post I would like to show another situation where this SPM plan stops to be reproducible and will write few words about the point related to the change of the index leading column(s). You will see the resemblance between the  functioning of the foreign key indexes and the reproducibility of the ”indexed” SPM plans.

As always I will start by the model

 CREATE TABLE t_range
(
ID           NUMBER              NOT NULL,
X            VARCHAR2(30 CHAR)   NOT NULL,
D            DATE,
C1           NUMBER
)
PARTITION BY RANGE (ID)
(
PARTITION P_10000 VALUES LESS THAN (10000) ,
PARTITION P_20000 VALUES LESS THAN (20000) ,
PARTITION P_30000 VALUES LESS THAN (30000) ,
PARTITION P_40000 VALUES LESS THAN (40000) ,
PARTITION P_50000 VALUES LESS THAN (50000) ,
PARTITION P_60000 VALUES LESS THAN (60000)
);

INSERT INTO t_range VALUES (150,   'First Part', sysdate - 2, 42);
INSERT INTO t_range VALUES (11500, 'Second Part',sysdate + 12, 82 );
INSERT INTO t_range VALUES (25000, 'Third Part',sysdate + 5, 102);
INSERT INTO t_range VALUES (34000, 'Fourt Part',sysdate -25, 302);
INSERT INTO t_range VALUES (44000, 'Fifth Part',sysdate -1, 525);
INSERT INTO t_range VALUES (53000, 'Sixth Part',sysdate +15, 1000);
commit;

create index t_r_i1 on t_range(id, c1);
exec dbms_stats.gather_table_stats(user, 't_range');

I have a simple query with two predicates on a range partitioned table which looks like

var n1 number;
var n2 number;
exec :n1 := 150
exec :n2 := 42;

select * from t_range where id = :n1 and c1 = :n2;

Already constrained or protected against any plan instability by the following ”indexed” SPM baseline (see Note below)

SQL_ID  ahufs9gr5x2pm, child number 1
-------------------------------------

select * from t_range where id = :n1 and c1 = :n2

Plan hash value: 2219242098
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |         |       |       |     2 (100)|          |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| T_RANGE |     1 |    26 |     2   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                 | T_R_I1  |     1 |       |     1   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:N1 AND "C1"=:N2)

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

I said above that changing the leading column(s) of the index will stop the reproducibility of the SPM plan. Let me be more precise about this point. I will reverse the order of the t_r_i1 index columns as shown below:

drop index t_r_i1;

create index t_r_i1 on t_range(c1,id); --> columns are reversed

select * from t_range where id = :n1 and c1 = :n2;

ID X                              D                         C1
---------- ------------------------------ ----------------- ----------
150 First Part                     20140118 11:20:53         42

SQL> start c:\x

SQL_ID  ahufs9gr5x2pm, child number 1
-------------------------------------
select * from t_range where id = :n1 and c1 = :n2

Plan hash value: 2219242098

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |         |       |       |     2 (100)|          |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| T_RANGE |     1 |    26 |     2   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                 | T_R_I1  |     1 |       |     1   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=:N2 AND "ID"=:N1)

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

Despite I have reversed the index columns the baseline is still used. When you have a predicate on two (or more) columns covered by an index on these columns, the new index you might re-create should always start by these two columns in any order if you want to ensure the reproducibility of the originally attached SPM plan. Spot the resemblance between the indexes on the foreign key which should start by the foreign key columns in any order in order to be able to avoid the deadlock threat when deleting from the parent table.

Having made this precision, let me now investigate the index type influence. Let’s change the orginal baselined index from b-tree to a locally partitioned index type.

drop index t_r_i1;

create index t_r_i1 on t_range(id,c1) local;

select * from t_range where id = :n1 and c1 = :n2;

ID X                              D                         C1
---------- ------------------------------ ----------------- ----------
150 First Part                     20140118 11:20:53         42

SQL_ID  ahufs9gr5x2pm, child number 1
-------------------------------------

select * from t_range where id = :n1 and c1 = :n2

Plan hash value: 963134062
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |         |       |       |     2 (100)|          |       |       |
|   1 |  PARTITION RANGE SINGLE            |         |     1 |    26 |     2   (0)| 00:00:01 |   KEY |   KEY |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T_RANGE |     1 |    26 |     2   (0)| 00:00:01 |   KEY |   KEY |
|*  3 |    INDEX RANGE SCAN                | T_R_I1  |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"=:N1 AND "C1"=:N2)

The SPM baseline ceases to be used.

Changing the index type has an influence on the re-usability of the SPM plan even when you don’t change any order of the indexed columns . In the same context of index type, I have also tried several changes (a) from b-tree to bitmap (b) from global to local and vice versa. For each index type change, the SPM stops to be reproducible.

Nevertheless, I have found two situations where changing the index type did not influenced the reproducibility of the SPM plan : (1) function based index under certain circumstances and (b) reversing an index. Here below how I did observed these two situations respectively

drop index t_r_i1;

create index t_r_i1 on t_range(c1,id, trunc(d));

I added an extra column to the “baselined index” which has the consequence to change its type from b-tree to function based index. Spot what happens to the SPM plan in this case:

select * from t_range where id = :n1 and c1 = :n2;

ID X                              D                         C1
---------- ------------------------------ ----------------- ----------
150 First Part                     20140118 11:20:53         42

start c:\x

SQL_ID  ahufs9gr5x2pm, child number 1
-------------------------------------
select * from t_range where id = :n1 and c1 = :n2

Plan hash value: 2219242098
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |         |       |       |     2 (100)|          |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| T_RANGE |     1 |    26 |     2   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                 | T_R_I1  |     1 |       |     1   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=:N2 AND "ID"=:N1)

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

The baseline is still reproducible. When you change a “baselined” b-tree index to a function based index starting with the “baselined” index columns, then the SPM will still be reproducible. Spot the resemblance with the function based index that are able to cover the foreign key(FK) deadlock threat when they start by the FK columns in any order.

And finally Reversing my original ”baselined” index

create index t_r_i1 on t_range(id, c1) reverse;

select * from t_range where id = :n1 and c1 = :n2;

ID X                              D                         C1
---------- ------------------------------ ----------------- ----------
150 First Part                     20140118 11:20:53         42

start c:\x

SQL_ID  ahufs9gr5x2pm, child number 1
-------------------------------------
select * from t_range where id = :n1 and c1 = :n2

Plan hash value: 2219242098
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |         |       |       |     2 (100)|          |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| T_RANGE |     1 |    26 |     2   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                 | T_R_I1  |     1 |       |     1   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:N1 AND "C1"=:N2)

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

Not good. I would have been inclined  to say that reversing the index should stop the reproducibility of the baseline plan. Because reversing an index might solve a buffer busy wait problem but might also in the meantime create a db file sequential read wait problem. Why then continue restricting other plans to be used because of this SPM plan reproducibility which is, in contrast to its original situation, is using a costly reversed index?

 

SQL Plan Management and indexes Part I: What matters? The index name or the indexed columns?

Here’s a point that I wanted to investigate since several months. Hopefully time comes when I deigned digging into it. The issue could be stated as follows: If I have an accepted SQL plan baseline using a given index, is this SPM reproducibility depending on that index name or on its indexed columns?

Let’s see

As always when blogging about SPM I use the same model and the same SPM baseline

  select sql_text, plan_name
  from dba_sql_plan_baselines
  where signature = '1292784087274697613';

  SQL_TEXT                                            PLAN_NAME
  --------------------------------------------------- ------------------------------
  select count(*), max(col2) from t1 where flag = :n  SQL_PLAN_13w748wknkcwd8576eb1f
  

The following select will use the above stored Baseline as mentioned by the Note at the end of the execution plan

  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 1
 -------------------------------------
 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 |    59 |            |          |
 |   2 |   TABLE ACCESS BY INDEX ROWID| T1   | 25000 |  1440K|   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)

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

 

The most important object in this baseline is the index I1 which has been defined with a single column as shown below:

 SQL> select table_name, index_name,column_name, column_position
 from user_ind_columns
 where table_name = 'T1';

 TABLE_NAME   INDEX_NAME  COLUMN_NAME  COLUMN_POSITION
 ------------ ----------- ------------ ---------------
 T1           I1         FLAG           1
 

Let’s first rename it and see the consequences

 SQL> alter index i1 rename to i2;
 Index altered.

 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 1
 -------------------------------------
 select count(*), max(col2) from t1 where flag = :n

 Plan hash value: 718843153

 -------------------------------------------------------------------------------------
 | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 -------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT             |      |       |       |   124 (100)|          |
 |   1 |  SORT AGGREGATE              |      |     1 |    59 |            |          |
 |   2 |   TABLE ACCESS BY INDEX ROWID| T1   | 25000 |  1440K|   124   (2)| 00:00:01 |
 |*  3 |    INDEX RANGE SCAN          | I2   | 25000 |       |    13   (8)| 00:00:01 |
 -------------------------------------------------------------------------------------

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

The SQL plan baseline has not been used thanks to this index name change. The index name matters.

Let’s now go back to the original I1 index but this time I will add to it an extra column

 SQL> drop index i1;

 SQL> create index i1 on t1(flag,col1);

 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 1
 -------------------------------------
 select count(*), max(col2) from t1 where flag = :n
 Plan hash value: 3625400295

 -------------------------------------------------------------------------------------
 | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 -------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT             |      |       |       |   130 (100)|          |
 |   1 |  SORT AGGREGATE              |      |     1 |    59 |            |          |
 |   2 |   TABLE ACCESS BY INDEX ROWID| T1   | 25000 |  1440K|   130   (2)| 00:00:01 |
 |*  3 |    INDEX RANGE SCAN          | I1   | 25000 |       |    19   (6)| 00:00:01 |
 -------------------------------------------------------------------------------------

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

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

The SQL plan baseline has been used in this situation (a) the new index has the same name as the index name in the stored SPM plan i.e. I1 (b) and the new index has an extra column but it is still starting with the same column as the index originally used during the baseline capture.

Finally let’s change the index columns order and use the same index name.

 SQL> drop index i1;

 SQL> create index i1 on t1(col1,flag);

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

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

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

 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 1
 -------------------------------------
 select count(*), max(col2) from t1 where flag = :n
 Plan hash value: 2289867667

 -------------------------------------------------------------------------------------
 | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 -------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT             |      |       |       |   123 (100)|          |
 |   1 |  SORT AGGREGATE              |      |     1 |    59 |            |          |
 |   2 |   TABLE ACCESS BY INDEX ROWID| T1   | 25000 |  1440K|   123   (2)| 00:00:01 |
 |*  3 |    INDEX SKIP SCAN           | I1   | 25000 |       |    67   (2)| 00:00:01 |
 -------------------------------------------------------------------------------------

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

Look now how it is the indexed column position that matters in this situation. The SPM baseline is not anymore used because the leading column of the I1 index (col1) is not the same leading column of the I1 index (flag) when compared to the index definition at the baseline capture time.

Bottom line: Baseline selection depends on both the index name and its leading columns as it was during the baseline capture.

1. If you change the index name, whatever the index definition will be, the baseline will not be selected
2. If you keep the index name and you change the leading column (s) of the index, the baseline will not be selected
3. If you keep the index name and you add an extra column at the trailing edge of the index the baseline might be selected

When we are in the third case above, the clustering factor of the I1 index might change to a bad value making the index cost bigger than the full table scan cost. In such a situation, choosing to use the SPM baseline might not be the good choice.  This is why it is nice to have an extra accepted baseline that foresee an extra plan (full table scan for example)  in order to not see the CBO plan constrained by this index SPM baseline.

PS : I might have been very quick making the above conclusions. I will update this post if needed when I will investigate a little bit further or when comments will introduce doubts on what I have stated above