Why my query is not using my unique index

Here’s a point that I have been asked to explain and that I failed to answer. It is a simple select on a table with an equality predicate on two columns. These two columns are indexed using a two columns unique index. So it seems quite obvious that when selecting from this table using these two columns to (a) select a unique row and (b) use that unique index for that purpose. However, the CBO seems making an illogic decision per regards to the choice of the index access when there is no statistics on the table (num_rows and last_analyzed are null) while there is 0 statistics in the indexes. Things will become clear with a concrete example.

As very often, I will use one of the Jonathan Lewis table scripts creation and population.

 create table t1
   (id number,
    n_1000 number,
    n_5000 number,
    n_10000 number,
    small_vc varchar2(20),
    padding  varchar2(100)
   );

create unique index a_unq_ind on t1(id, n_1000);
create index b_non_unq_ind on t1(n_1000, n_5000);

Spot how I have managed to name my indexes(unique and non-unique) in an alphabetic order so that when this specific order matters the CBO will choose the unique index as far as it starts with the ‘a’ letter.

Now that I have created my table and my two indexes I can populate the table with data without transmitting statistics to the indexes.

 insert into t1
  with generator as (
   select   --+ materialize
    rownum id
   from dual
  connect by
  rownum <= 10000
)
select
    rownum                    id,
    mod(rownum,1000) n_1000,
    mod(rownum,5000) n_5000,
    mod(rownum,10000) n_10000,
    lpad(rownum,10,'0')       small_vc,
    rpad('x',100)             padding
from
    generator        v1,
    generator        v2
where
rownum <= 100000
;

commit;

Before starting my select, let me show you the actual table and index statistics

SQL> select table_name, num_rows, last_analyzed from user_tables where table_name = 'T1';

TABLE_NAME   NUM_ROWS LAST_ANALYZED
---------- ---------- -----------------
T1

SQL> select index_name, num_rows, clustering_factor from user_indexes where table_name = 'T1';

INDEX_NAME        NUM_ROWS CLUSTERING_FACTOR
--------------- ---------- -----------------
B_NON_UNQ_IND            0                 0
A_UNQ_IND                0                 0

And now my desired select and its corresponding execution plan (statistics_level have been set to all before)

SQL> select * from t1
     where id = 1
     and   n_1000 = 1;

ID     N_1000     N_5000    N_10000 SMALL_VC             PADDING
---------- ---------- ---------- ---------- -------------------- -----------
1          1          1          1 0000000001           x

SQL_ID  29tnq7b69swdr, child number 0
-------------------------------------
select * from t1  where id = 1  and   n_1000 = 1

Plan hash value: 3790258116

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |      1 |        |      1 |00:00:00.01 |     103 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1            |      1 |     15 |      1 |00:00:00.01 |     103 |
|*  2 |   INDEX RANGE SCAN                  | B_NON_UNQ_IND |      1 |    615 |    100 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1)
2 - access("N_1000"=1)

It is clear by comparing the E-Rows and A-Rows that the statistics are not reflecting the reality.

And when I force the use of the unique index the CBO seems doing good estimations and generation less buffer gets

SQL> select /*+ index( t1 A_UNQ_IND ) */ * from t1  where id = 1  and   n_1000= 1;

ID     N_1000     N_5000    N_10000 SMALL_VC             PADDING
---------- ---------- ---------- ---------- --------------------
1          1          1          1 0000000001           x

SQL_ID  bt11jwur90xg0, child number 0
-------------------------------------
select /*+ index( t1 A_UNQ_IND ) */ * from t1  where id = 1  and n_1000= 1;

Plan hash value: 3585360496
---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  2 |   INDEX UNIQUE SCAN         | A_UNQ_IND |      1 |      1 |      1 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1 AND "N_1000"=1)

While this unique index execution plan seems optimal with correct estimations and less Buffers gets it is nevertheless not the index that the CBO desires to use.

Anyway, you are going to object and to say we are not going to run a real life application without having correct statistics gathered. Believe me that if I am writing this case here it is because it has happened in a real life production application. Having said that let’s collect statistics and see the new reaction of the CBO

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

PL/SQL procedure successfully completed.

SQL> select * from t1
where id = 1
and   n_1000 = 1;

ID     N_1000     N_5000    N_10000 SMALL_VC             PADDING
---------------------------------------------------------------------------------------
1          1          1          1 0000000001           x

SQL_ID  29tnq7b69swdr, child number 1
-------------------------------------
select * from t1  where id = 1  and   n_1000 = 1

Plan hash value: 3585360496

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  2 |   INDEX UNIQUE SCAN         | A_UNQ_IND |      1 |      1 |      1 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1 AND "N_1000"=1)

Note
-----
- statistics feedback used for this statement

After having collected statistics the CBO is now choosing the right index.

By the way I am doing this test case on a 12c data base (you might have already guessed that through the presence of the word BATCHED in the execution plan) and seeing this statistics feedback used note, pushed me to flush the shared pool and re-run my query in order to be sure that the use of the unique index is not due to this statistics feedback feature

SQL> alter system flush shared_pool;

System altered.

SQL> select * from t1
where id = 1
and   n_1000 = 1;

ID     N_1000     N_5000    N_10000 SMALL_VC             PADDING
--------- ---------- ---------- ---------- -------------------- ---------
1          1          1          1 0000000001           x

SQL_ID  29tnq7b69swdr, child number 0
-------------------------------------
select * from t1  where id = 1  and   n_1000 = 1

Plan hash value: 3585360496
---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  2 |   INDEX UNIQUE SCAN         | A_UNQ_IND |      1 |      1 |      1 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1 AND "N_1000"=1)

That is the whole story. If you want to learn more about this particular situation then you have to read this related otn thread.

Bottom line: always be sure to have adequate statistics so that you will give the CBO all chances to produce an adequate plan

PS : I started by trying to explain why the CBO didn’t choose the unique index and ended up while writing this article to two questions

1 : why dynamic sampling didn’t occur : Martin Press gave a clue that needs only to be tested

2 : when the CBO uses the index range scan the table is accessed via this new 12c  TABLE ACCESS BY INDEX ROWID BATCHED  that has been explained by Timur Akhmadeed but when it uses the unique index scan the CBO opted for the classical TABLE ACCESS BY INDEX ROWID

12c display_awr

It is well known that display_awr function is unable to show the predicate part of a captured execution plan. Is this still the case with the 12c advent?

 select * from v$version;

BANNER                                                                           CON_ID
 -------------------------------------------------------------------------------- ----------
 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production     0
 PL/SQL Release 12.1.0.1.0 - Production                                           0
 CORE 12.1.0.1.0 Production                                                       0
 TNS for 64-bit Windows: Version 12.1.0.1.0 - Production                          0
 NLSRTL Version 12.1.0.1.0 - Production                                           0

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

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

 select count(*), max(col2) from t2 where flag = :n;

 COUNT(*) MAX(COL2)
 --------- ------------------------------------------
 0

 select * from table(dbms_xplan.display_cursor);

 SQL_ID 47n9zu0w7ht8d, child number 0
 -------------------------------------
 select count(*), max(col2) from t2 where flag = :n

 Plan hash value: 3321871023
 ---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T2   |     1 |    30 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

 Note
 -----
  - dynamic statistics used: dynamic sampling (level=2)
 

Let’s “color” the above sql query so that it will be captured by the next AWR snapshot and check the captured execution plan to see if it reports the predicate part or not

exec dbms_workload_repository.add_colored_sql('47n9zu0w7ht8d');

exec dbms_workload_repository.create_snapshot;

select * from table(dbms_xplan.display_awr('47n9zu0w7ht8d'));

SQL_ID 47n9zu0w7ht8d
--------------------
select count(*), max(col2) from t2 where flag = :n

Plan hash value: 3321871023
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|   2 |   TABLE ACCESS FULL| T2   |     1 |    30 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
 Note
 -----
  - dynamic statistics used: dynamic sampling (level=2)
 

The predicate part is still not shown in the execution plan taken from AWR. Damn!!

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