SQL Plan Management and table flashback

Continuing my series on what makes a SPM plan not used anymore, today I will show the collateral effect a surgical drop/flashback table operation can have on the reproducibility (or on the use which is most adequat word in this case) of an enabled and accepted SQL baseline

Let’s see this in action

 create table rang_list_tab
( creation_date   date                not null,
  status_code     varchar2(30 char)   not null,
  n1              number              not null,
  v1              varchar2(1 char)    not null
)
partition by range (creation_date)
subpartition by list (status_code)
subpartition template
(subpartition sub_p1 values ('FULL') ,
 subpartition sub_p2 values ('EMPTY') ,
 subpartition sub_p3 values ('UNKNOWN') ,
 subpartition others  values (default)
)
(
partition p_20100131 values less than ( to_date('20100201','yyyymmdd') ),
partition p_20100201 values less than ( to_date('20100202','yyyymmdd') ),
partition p_20100202 values less than ( to_date('20100203','yyyymmdd') ),
partition p_20100203 values less than ( to_date('20100204','yyyymmdd') ),
partition p_20100204 values less than ( to_date('20100205','yyyymmdd')),
partition p_20100205 values less than (to_date('20100206','yyyymmdd')),
partition p_20100206 values less than ( to_date('20100207','yyyymmdd')),
partition p_20100207 values less than ( to_date('20100208','yyyymmdd'))
)
;

create index idx_n1 on rang_list_tab(n1);

INSERT INTO rang_list_tab VALUES (to_date('20100201','yyyymmdd'), 'FULL', 1000, 'P');
INSERT INTO rang_list_tab VALUES (to_date('20100202','yyyymmdd'), 'FULL', 30000, 'P');
INSERT INTO rang_list_tab VALUES (to_date('20100203','yyyymmdd'), 'EMPTY', 2000, 'P');
INSERT INTO rang_list_tab VALUES (to_date('20100204','yyyymmdd'), 'UNKNOWN', 1000, 'P');
INSERT INTO rang_list_tab VALUES (to_date('20100205','yyyymmdd'), 'EMPTY', 2400, 'P');
INSERT INTO rang_list_tab VALUES (to_date('20100201','yyyymmdd'), 'EMPTY', 500, 'P');
INSERT INTO rang_list_tab VALUES (to_date('20100201','yyyymmdd'), 'UNKNOWN', 700, 'P');
INSERT into rang_list_tab values (to_date('20100202','yyyymmdd'), 'UNKNOWN', 1200, 'P');
INSERT INTO rang_list_tab VALUES (to_date('20100201','yyyymmdd'), 'UNKNOWN', 400, 'P');
INSERT INTO rang_list_tab VALUES (to_date('20100206','yyyymmdd'), 'FULL', 600, 'P');
INSERT INTO rang_list_tab VALUES (to_date('20100204','yyyymmdd'), 'FULL', 700, 'P');

commit;

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

The following select is covered by an already enabled and accepted stored Baseline as shown below:

SQL> select * from rang_list_tab where n1 = :ln;

CREATION_DATE     STATUS_CODE                            N1 V
----------------- ------------------------------ ---------- -
20100201 00:00:00 FULL                                 1000 P
20100204 00:00:00 UNKNOWN                              1000 P

SQL_ID  6fsr5hnbrbrkx, child number 2
-------------------------------------
select * from rang_list_tab where n1 = :ln

Plan hash value: 1798114219

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

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N1"=:LN)

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

What if by error someone drops the rang_list_tab table and then realizes that he needs to flash it back

SQL> -- drop the table
SQL> drop table rang_list_tab;

Table dropped.

SQL> -- damn!!! I need it back
SQL> flashback table rang_list_tab to before drop;

Flashback complete.

Well, all things are back and we are safe. Right? Until someone will start complaining about a baseline that used to be used and is not anymore constraining the CBO from selecting other paths

SQL> select * from rang_list_tab where n1 = :ln;

CREATION_DATE     STATUS_CODE                            N1 V
----------------- ------------------------------ ---------- -
20100201 00:00:00 FULL                                 1000 P
20100204 00:00:00 UNKNOWN                              1000 P

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

SQL_ID  6fsr5hnbrbrkx, child number 2
-------------------------------------
select * from rang_list_tab where n1 = :ln

Plan hash value: 1757268378
------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                                |       |       |     3 (100)|          |       |      |

|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| RANG_LIST_TAB                  |     1 |    19 |     3   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                 | BIN$8LTnuCCRXdfgQ20MIwp/NA==$0 |     1 |       |     1   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N1"=:LN)

The SQL plan baseline has not been used thanks to this table drop/flashback.

In fact, this not anymore than a collateral effect of the drop/flashback of the table which has the consequence of not flashing back the original index name baseline

If you want to put your baseline back to its initial behavior then rename the index as shown below

SQL> alter index  "BIN$8LTnuCCRXdfgQ20MIwp/NA==$0" rename to idx_n1;

Index altered.

SQL> select * from rang_list_tab where n1 = :ln;

CREATION_DATE     STATUS_CODE                            N1 V
----------------- ------------------------------ ---------- -
20100201 00:00:00 FULL                                 1000 P
20100204 00:00:00 UNKNOWN                              1000 P

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

---------
SQL_ID  6fsr5hnbrbrkx, child number 2
-------------------------------------
select * from rang_list_tab where n1 = :ln

Plan hash value: 1798114219

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

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N1"=:LN)

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

Bottom line:  When you drop and flashback a table, then think about the following points

1. the foreign key constraints are not flashed back
2. the original index name, the trigger name and constraint name are not flashed back
3. any SQL plan baseline based on an index created on a table that has been dropped and flashed back will not be reproducible until you give that index its original name

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

SQL Plan Management: what’s new in 12c

Don’t be disappointed by the title. This is a simple note to show one difference I have noticed in how a stored SPM execution plan is managed when a dependent object is dropped or renamed. To keep it simple, let me start by an existing SPM baseline as shown below

 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
 

If I want to display the execution plan of this stored SPM baseline I will proceed as follow:

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

 --------------------------------------------------------------------------------
 SQL handle: SQL_11f0e4472549338d
 SQL text: select count(*), max(col2) from t1 where flag = :n
 --------------------------------------------------------------------------------
 --------------------------------------------------------------------------------
 Plan name: SQL_PLAN_13w748wknkcwd8576eb1f         Plan id: 2239163167
 Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
 --------------------------------------------------------------------------------

 Plan hash value: 3625400295
 -------------------------------------------------------------------------------------
 | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 -------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT             |      |     1 |    59 |   124   (2)| 00:00:01 |
 |   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)
 

It is a baseline using an index range scan. What happen to that stored execution plan when I drop or rename that index

 drop index i1;

 select * from table(dbms_xplan.display_sql_plan_baseline(plan_name =>'SQL_PLAN_13w748wknkcwd8576eb1f'));
 --------------------------------------------------------------------------------
 SQL handle: SQL_11f0e4472549338d
 SQL text: select count(*), max(col2) from t1 where flag = :n

 --------------------------------------------------------------------------------
 --------------------------------------------------------------------------------
 Plan name: SQL_PLAN_13w748wknkcwd8576eb1f         Plan id: 2239163167
 Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
 --------------------------------------------------------------------------------

 Plan hash value: 3724264953

 ---------------------------------------------------------------------------
 | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 ---------------------------------------------------------------------------
 |   0 | SELECT STATEMENT   |      |     1 |    59 |   241   (3)| 00:00:01 |
 |   1 |  SORT AGGREGATE    |      |     1 |    59 |            |          |
 |*  2 |   TABLE ACCESS FULL| T1   | 25000 |  1440K|   241   (3)| 00:00:01 |
 ---------------------------------------------------------------------------

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

The baseline stored execution plan has been automatically updated to reflect the disappeared index.  But wait; this is an 11g behavior. Do the same thing in 12c and you will get a different functioning.

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

 --------------------------------------------------------------------------------
 SQL handle: SQL_11f0e4472549338d
 SQL text: select count(*), max(col2) from t1 where flag = :n
 --------------------------------------------------------------------------------

 --------------------------------------------------------------------------------
 Plan name: SQL_PLAN_13w748wknkcwd7823646b         Plan id: 2015585387
 Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
 Plan rows: From dictionary
 --------------------------------------------------------------------------------

 Plan hash value: 497086120

 ---------------------------------------------------------------------------------------------
 | Id  | Operation                            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 ---------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT                     |      |       |       |     2 (100)|          |
 |   1 |  SORT AGGREGATE                      |      |     1 |    30 |     0   (0)|          |
 |   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1   |     1 |    30 |     2   (0)| 00:00:01 |
 |*  3 |    INDEX RANGE SCAN                  | I1   |     1 |       |     1   (0)| 00:00:01 |
 ---------------------------------------------------------------------------------------------

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

This time dropping the index in a 12c release will not influence the stored SPM execution plan as shown below:

 SQL> drop index I1;

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

 SQL_PLAN_13w748wknkcwd8576eb1f
 --------------------------------------------------------------------------------
 SQL handle: SQL_11f0e4472549338d
 SQL text: select count(*), max(col2) from t1 where flag = :n
 --------------------------------------------------------------------------------

 --------------------------------------------------------------------------------
 Plan name: SQL_PLAN_13w748wknkcwd7823646b         Plan id: 2015585387
 Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD

 Plan rows: From dictionary
 --------------------------------------------------------------------------------
 Plan hash value: 497086120

 ---------------------------------------------------------------------------------------------
 | Id  | Operation                            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 ---------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT                     |      |       |       |     2 (100)|          |
 |   1 |  SORT AGGREGATE                      |      |     1 |    30 |     0   (0)|          |
 |   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1   |     1 |    30 |     2   (0)| 00:00:01 |
 |*  3 |    INDEX RANGE SCAN                  | I1   |     1 |       |     1   (0)| 00:00:01 |
 ---------------------------------------------------------------------------------------------

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

The interesting question which motivated this post is: is this change good or bad for our proper use/debug of SPM baseline?

When I was deeply investigation, in the last release, how a baseline is chosen or discarded following a drop of a dependent object, I have already remarked that kind of automatic update of the stored execution plan. I was having two stored baselines one using full table scan and the other one was using the index range scan. In such a situation this index drop let me with the two different stored baselines but this time having both an identical full table scan plan. I knew why, because I originated the two baselines and I provoked that change. But a new developer who might come after me would then question what are those two different baselines having the same execution plan. Would he be able to understand that this situation has been made as such because someone in the past has dropped and index? Not sure.

So, saving definitely the execution plan of the stored SPM baseline as it was at its creation time is a good news in my honest opinion. Why?  Because if you see your query not using the baseline your want it to be used, you will dig into its stored plan and you will remark that this stored plan is using an index named I1. It is then easy to go to that table and verify if the I1 index exists or has been dropped which might immediately explain why your stored baseline is not anymore selected.

Bottom line: I think that the most important thing to remember when looking to the stored SPM execution plan is that, starting from 12c, the displayed execution plan is the one that was available at the baseline captured time. It could be now still available and it might be not anymore reproducible