Interpreting Execution Plan

I have been confronted to a performance issue with a query that started performing badly (6 sec.  instead of the usual 2 sec. ) following a change request that introduces a new business requirement. Below is the new execution plan stripped to the bare minimum and where table and index names have been a little bit disguised to protect the innocent.  I have manually introduced two aliases (MHO and YAS) in this execution plan so that the predicate part will be easily linked to its corresponding table (I know there is a difference between E-Rows and A-Rows for certain operations; that’s not my intention to deal with  here in this blog post)

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                        | Starts | E-Rows | A-Rows |   A-Time   |
---------------------------------------------------------------------------------------------------------------------------
|*  8 |         HASH JOIN OUTER                    |                              |      1 |      1 |   2218 |00:00:05.45 |
|   9 |--->      NESTED LOOPS                      |                              |      1 |      1 |   2218 |00:00:03.44 |
|  10 |           NESTED LOOPS                     |                              |      1 |      1 |   2218 |00:00:03.43 |
|  11 |       ---> VIEW                            | XXX_DEMANDE_MANAGMENT_V      |      1 |    251 |    125K|00:00:02.43 |
|  12 |             UNION-ALL                      |                              |      1 |        |    125K|00:00:02.43 |
|  13 |              NESTED LOOPS                  |                              |      1 |      1 |      0 |00:00:00.01 |
|  14 |               INDEX FULL SCAN              | XXX_CLS_BUR_OPR_UK           |      1 |      1 |      0 |00:00:00.01 |
|  15 |               TABLE ACCESS BY INDEX ROWID  | XXX_ASPECT                   |      0 |      1 |      0 |00:00:00.01 |
|* 16 |                INDEX UNIQUE SCAN           | XXX_BUR_PK                   |      0 |      1 |      0 |00:00:00.01 |
|* 17 |              FILTER                        |                              |      1 |        |    125K|00:00:02.31 |
|* 18 |               HASH JOIN                    |                              |      1 |    126K|    125K|00:00:01.18 |
|  19 |                NESTED LOOPS                |                              |      1 |    251 |    251 |00:00:00.01 |
|  20 |                 VIEW                       | index$_join$_054             |      1 |    251 |    251 |00:00:00.01 |
|* 21 |                  HASH JOIN                 |                              |      1 |        |    251 |00:00:00.01 |
|  22 |                   INDEX FAST FULL SCAN     | XXX_BUR_SOM_FK_I             |      1 |    251 |    251 |00:00:00.01 |
|  23 |                   INDEX FAST FULL SCAN     | XXX_BUR_MSF_BUR_FK_I         |      1 |    251 |    251 |00:00:00.01 |
|* 24 |                 INDEX UNIQUE SCAN          | XXX_SOM_PK                   |    251 |      1 |    251 |00:00:00.01 |
|  25 |                VIEW                        | index$_join$_053             |      1 |    126K|    125K|00:00:00.79 |
|* 26 |                 HASH JOIN                  |                              |      1 |        |    125K|00:00:00.67 |
|  27 |                  INDEX FAST FULL SCAN      | XXX_RIP_PK                   |      1 |    126K|    125K|00:00:00.01 |
|  28 |                  INDEX FAST FULL SCAN      | XXX_RIP_BUR_FK_I             |      1 |    126K|    125K|00:00:00.01 |
|* 29 |               INDEX RANGE SCAN             | XXX_CLS_RIP_FK_I             |    125K|      1 |      0 |00:00:00.66 |
|* 30 |       ---> TABLE ACCESS BY INDEX ROWID     | XXX_DEMANDE_ORDINAIR (MHO)   |    125K|      1 |   2218 |00:00:02.97 |
|* 31 |             INDEX UNIQUE SCAN              | XXX_RIP_PK                   |    125K|      1 |    125K|00:00:00.89 |
|  33 |--->      VIEW (YAS)                        |                              |      1 |     82 |   1218 |00:00:00.08 |
|  34 |           SORT UNIQUE                      |                              |      1 |     82 |   1218 |00:00:00.08 |
--------------------------------------------------------------------------------------------------------------------------

8 - access("YAS"."PK_ID"="MHO"."PK_ID")
30 – filter(("MHO"."CLOSED"<>3 AND TRUNC(INTERNAL_FUNCTION("MHO"."CREATION_DATE"))=TRUNC(TO_DATE('12042013','ddmmrrrr')))
OR (TRUNC(TO_DATE('12042013','ddmmrrrr'))=TO_DATE('01010001','ddmmrrrr') ))

Well, from where am I going to start here?

Hmmm… always the same question when trying to interpret an execution plan.

After looking carefully to that execution plan and to its predicate part (always consider the predicate part) I ended up asking myself the following question:

There is HASH JOIN OUTER (Id 8) between a NESTED LOOPS (Id 9) and the VIEW (YAS – id 33)

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows | A-Rows |   A-Time   |
--------------------------------------------------------------------------------------
|*  8 |         HASH JOIN OUTER     |        |      1 |      1 |   2218 |00:00:05.45 |
|   9 |--->      NESTED LOOPS       |        |      1 |      1 |   2218 |00:00:03.44 |
|  33 |--->      VIEW (YAS)         |        |      1 |     82 |   1218 |00:00:00.08 |
--------------------------------------------------------------------------------------

On which a filter operation is applied in order to filter the result by comparing the YAS view with the MHO table via their ”primary key” (PK_ID)

8 - access("YAS"."PK_ID"="MHO"."PK_ID")

That’s seems a little bit strange. Why not a direct HASH JOIN OUTER between the YAS view and the MHO table instead of a JOIN between the YAS view and that NESTED LOOPS (where an access to MHO table is made)?

Have you already pointed out how the predicate part can make you asking good questions?

My second step has been to look at the predicate part of the MHO table access (operation 30) re-printed here below:

30 – filter(("MHO"."CLOSED"<>3 AND TRUNC(INTERNAL_FUNCTION("MHO"."CREATION_DATE"))=TRUNC(TO_DATE('12042013','ddmmrrrr')))
OR (TRUNC(TO_DATE('12042013','ddmmrrrr'))=TO_DATE('01010001','ddmmrrrr') ))

I have an adequate function based index in place that should have been used provided this INTERNAL_FUNCTION has not being used by the CBO

create index XXX_RIP_CREATION_DATE_I on MHO(trunc(creation_date)) ;

This is why I was tempted to force my query to use this index via the appropriate hint. Here below is the resulting execution plan

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                        | Starts | E-Rows | A-Rows |   A-Time    |
---------------------------------------------------------------------------------------------------------------------------
|   8 |         NESTED LOOPS                        |                              |      1 |      1 |   2218 |00:00:02.57 |
|*  9 |--->      HASH JOIN OUTER                    |                              |      1 |      1 |   2218 |00:00:02.48 |
|* 10 |      ---> TABLE ACCESS BY INDEX ROWID       | XXX_DEMANDE_ORDINAIR(MHO)    |      1 |      1 |   2218 |00:00:02.34 |
|  11 |            INDEX FULL SCAN                  | XXX_RIP_CREATION_DATE_I      |      1 |    126K|    125K|00:00:01.82 |
|  12 |      ---> VIEW(YAS)                         |                              |      1 |     82 |   1218 |00:00:00.08|
|  13 |            SORT UNIQUE                      |                              |      1 |     82 |   1218 |00:00:00.08 |
|  41 |--->      VIEW                               | XXX_DEMANDE_MANAGMENT_V      |   2218 |      1 |   2218 |00:00:00.08 |
|  42 |           UNION-ALL PARTITION               |                              |   2218 |        |   2218 |00:00:00.08 |
|  43 |            NESTED LOOPS                     |                              |   2218 |      1 |      0 |00:00:00.01 |
|  44 |             TABLE ACCESS BY INDEX ROWID     | XXX_DEMANDE_RESPONSABLE      |   2218 |      1 |      0 |00:00:00.01 |
|* 45 |              INDEX RANGE SCAN               | XXX_CLS_RIP_FK_I             |   2218 |      1 |      0 |00:00:00.01 |
|  46 |             TABLE ACCESS BY INDEX ROWID     | XXX_ASPECT                   |      0 |      1 |      0 |00:00:00.01 |
|* 47 |              INDEX UNIQUE SCAN              | XXX_BUR_PK                   |      0 |      1 |      0 |00:00:00.01 |
|  48 |            NESTED LOOPS                     |                              |   2218 |      1 |   2218 |00:00:00.06 |
|  49 |             NESTED LOOPS                    |                              |   2218 |      1 |   2218 |00:00:00.05 |
|  50 |              TABLE ACCESS BY INDEX ROWID    | XXX_DEMANDE_ORDINAIR         |   2218 |      1 |   2218 |00:00:00.03 |
|* 51 |               INDEX UNIQUE SCAN             | XXX_RIP_PK                   |   2218 |      1 |   2218 |00:00:00.02 |
|* 52 |                INDEX RANGE SCAN             | XXX_CLS_RIP_FK_I             |   2218 |      1 |      0 |00:00:00.01 |
|  53 |              TABLE ACCESS BY INDEX ROWID    | XXX_ASPECT_                  |   2218 |    251 |   2218 |00:00:00.01 |
|* 54 |               INDEX UNIQUE SCAN             | XXX_BUR_PK                   |   2218 |      1 |   2218 |00:00:00.01 |
|* 55 |             INDEX UNIQUE SCAN               | XXX_SOM_PK                   |   2218 |     36 |   2218 |00:00:00.01 |
---------------------------------------------------------------------------------------------------------------------------
9 - access("YAS"."PK_ID"="MHO"."PK_ID")
10 - filter(("MHO"."CLOSED"<>3) AND "PRI"."SYS_NC00047$"=TRUNC(TO_DATE('12042013','ddmmrrrr'))) OR
(TRUNC(TO_DATE('12042013','ddmmrrrr'))=TO_DATE('01010001','ddmmrrrr')))

That is a better step accomplished (from 5,45 to 2,57 seconds). Isn’t it?  Look now how my HASH OUTER JOIN became

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                     | Starts | E-Rows | A-Rows |   A-Time   |
------------------------------------------------------------------------------------------------------------------
|   8 |         NESTED LOOPS                  |                          |      1 |      1 |   2218 |00:00:02.57 |
|*  9 |--->      HASH JOIN OUTER              |                          |      1 |      1 |   2218 |00:00:02.48 |
|* 10 |      ---> TABLE ACCESS BY INDEX ROWID | XXX_DEMANDE_ORDINAIR(MHO)|      1 |      1 |   2218 |00:00:02.34 |
|  11 |            INDEX FULL SCAN            | XXX_RIP_CREATION_DATE_I  |      1 |    126K|    125K|00:00:01.82 |
|  12 |      ---> VIEW(YAS)                   |                          |      1 |     82 |   1218 |00:00:00.08 |
------------------------------------------------------------------------------------------------------------------

As I wanted it to be: directly between the MHO table and the YAS view.

But wait a moment please. It seems for me that this INDEX FULL SCAN operation is still to be tuned.  Do you know why? Because this operation is feeding back its parent operation (id 10) with 125,000 rowids of which only 2218 records are kept. 98% of those rowids are thrown away by the filter operation n° 10

10 - filter(("MHO"."CLOSED"<>3) AND "PRI"."SYS_NC00047$"=TRUNC(TO_DATE('12042013','ddmmrrrr'))) OR
(TRUNC(TO_DATE('12042013','ddmmrrrr'))=TO_DATE('01010001','ddmmrrrr')))

Clearly this is a waste of time and resource. It is better to have a more precise index or to not use that XXX_RIP_CREATION_DATE_I index at all.  But how can I  (see this is another question again) make the CBO generating the HASH JOIN OUTER I want without forcing the use of that function based index?

Okay…

It’s time to look to the content of the query. The part of the query involving my two tables join looks like this

select
 mho.*
,abc.col1
,abc.col2
from
  XXX_DEMANDE_ORDINAIR       mho
, XXX_DEMANDE_MANAGMENT_V    abc
, my_view                    yas
where  mho.pk        = abc.pk
and    mho.pk_id     = yas.pk_id(+)
etc…

The view YAS is not selected from. It should be taken out from the join and put into the where clause as an EXISTS condition.

select
mho.*
,abc.col1
,abc.col2
from
 XXX_DEMANDE_ORDINAIR       mho
,XXX_DEMANDE_MANAGMENT_V    abc
where  mho.pk        = abc.pk
and  exists (select null
             from  my_view yas
             where mho.pk_id  = yas.pk_id)
etc…

I was going to change this when one of my colleagues suggested me to change the above query as follows (please spot the difference there is only a (+) added)

select
 mho.*
,abc.col1
,abc.col2
from
 XXX_DEMANDE_ORDINAIR       mho
,XXX_DEMANDE_MANAGMENT_V    abc
,my_view                    yas
where  mho.pk        = abc.pk(+) --> This will not change the result because I know there is always a record in abc table
and    mho.pk_id     = yas.pk_id(+)
etc…

Doing so, the ABC table will not be considered by the CBO as the driving table because it is the table that is outer joined (is this correct? I have to admit that I need to test it deeply in order to be sure enough about that fact).  As such, the CBO will directly join the MHO table with the YAS view first (this is what I want in fact) and then outer join the result to the third table.

Anyway, I did as suggested and here below what I ended up with

---------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                     | Starts | E-Rows | A-Rows |   A-Time   |
--------------------------------------------------------------------------------------------------------
|   9 |         NESTED LOOPS          |                          |      1 |      1 |   2218 |00:00:01.07 |
|* 10 |--->      HASH JOIN OUTER      |                          |      1 |      1 |   2218 |00:00:00.99 |
|  11 |       -->  TABLE ACCESS FULL  | XXX_DEMANDE_ORDINAIR(MHO)|      1 |      1 |   2218 |00:00:00.27 |
|  12 |       -->  VIEW(YAS)          |                          |      1 |     82 |   1218 |00:00:00.08 |
---------------------------------------------------------------------------------------------------------
9 - access("YAS"."PK_ID"="MHO"."PK_ID")
10 – filter(("MHO"."CLOSED"<>3 AND TRUNC(INTERNAL_FUNCTION("MHO"."CREATION_DATE"))=TRUNC(TO_DATE('12042013','ddmmrrrr')))
OR (TRUNC(TO_DATE('12042013','ddmmrrrr'))=TO_DATE('01010001','ddmmrrrr') ))

The query is now responding better than before the change request. Doing a full table scan on MHO table in this case is better than to access it via the existing non precise function based index and then filter the returned rows(by rowid) to through 97% of them.

Bottom line: the goal of this article is to show how important the predicate part can be in tuning a query via its execution plan. I started questioning myself from the join predicate part followed by the use of an adequate index and finally I ended up by searching the best  order of the table  join

What can impeach Adaptive Cursor Sharing kicking off?

I ended my last post about the interaction between ACS and SPM by the following observation

How could a creation of an extra index disturb the ACS behavior?

Well, it seems that there is a different combination which leads to this situation. Instead of jumping to a conclusion that might be wrong I prefer presenting my demo upon which I will make a proposition and let you (readers thanks in advance for that) criticizing what I tend to affirm.

For sake of simplicity, the following sql against v$sql will be referred to as is_bind_aware.sql.

SQL > select sql_id
2    , child_number
3    , is_bind_aware
4    , is_bind_sensitive
5    , is_shareable
6    , to_char(exact_matching_signature) sig
7    , executions
8    , plan_hash_value
9    from v$sql
10    where sql_id = '731b98a8u0knf';

The model used for this demo can be found in Dominic Brook’s article and I will start from here

SQL > exec :n := 'N2'; --> FULL TABLE SCAN

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

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

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

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

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   275 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    54 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 50110 |  2642K|   275   (2)| 00:00:04 |
---------------------------------------------------------------------------

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

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

SQL > exec :n := 'Y2'; --> INDEX RANGE SCAN

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 3
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3625400295
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |    54 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |     9 |   486 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I1   |     9 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

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

After several executions of the above sql, using alternately index and full table scan bind variables, I ended up with the following situation:

SQL > @is_bind_aware.sql

SQL_ID        CHILD_NUMBER I I I SIG                EXECUTIONS  PLAN_HASH_VALUE
------------- ------------ - - - ------------------ ----------- -----------------
731b98a8u0knf 0            N Y N 1292784087274697613 2          3724264953
731b98a8u0knf 1            Y Y N 1292784087274697613 1          3625400295
731b98a8u0knf 2            Y Y Y 1292784087274697613 1          3724264953   --> TABLE FULL SCAN
731b98a8u0knf 3            Y Y Y 1292784087274697613 1          3625400295   --> INDEX RANGE SCAN

SQL > select * from v$sql_cs_statistics where sql_id = '731b98a8u0knf';

ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED
-------- ---------- ------------- ------------ ------------------- - ---------- --------------
22A4D04C 2443201166 731b98a8u0knf            3          3066078819 Y          1              3
22A4D04C 2443201166 731b98a8u0knf            2          3938583969 Y          1          50000
22A4D04C 2443201166 731b98a8u0knf            1          2780714206 Y          1              3
22A4D04C 2443201166 731b98a8u0knf            0          1896453392 Y          1          50000

SQL > select * from v$sql_cs_histogram where sql_id = '731b98a8u0knf';

ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
-------- ---------- ------------- ------------ ---------- ----------
22A4D04C 2443201166 731b98a8u0knf            3          0          1 --> rows_processed < 1,000
22A4D04C 2443201166 731b98a8u0knf            3          1          0
22A4D04C 2443201166 731b98a8u0knf            3          2          0
22A4D04C 2443201166 731b98a8u0knf            2          0          0
22A4D04C 2443201166 731b98a8u0knf            2          1          1 --> 1,000 <rows_processed <1,000,000
22A4D04C 2443201166 731b98a8u0knf            2          2          0
22A4D04C 2443201166 731b98a8u0knf            1          0          1
22A4D04C 2443201166 731b98a8u0knf            1          1          0
22A4D04C 2443201166 731b98a8u0knf            1          2          0
22A4D04C 2443201166 731b98a8u0knf            0          0          1
22A4D04C 2443201166 731b98a8u0knf            0          1          1
22A4D04C 2443201166 731b98a8u0knf            0          2          0

Two child cursor(2 and 3) that are (a) shareable (b) bind sensitive and (c) bind aware so that ACS can associate each bind variable to it’s a corresponding child number and hence the execution plan that best fits each bind variable.

Up to this point,  ACS is working very well in presence of a SPM baseline

SQL > select
2      to_char(signature) signature
3    , sql_handle
4    , plan_name
5    , enabled
6    , accepted
7    from dba_sql_plan_baselines
8    where signature = 1292784087274697613;

SIGNATURE                                SQL_HANDLE                     PLAN_NAME                      ENA ACC
---------------------------------------- ------------------------------ ------------------------------ --- ---
1292784087274697613                      SYS_SQL_11f0e4472549338d       SQL_PLAN_13w748wknkcwd616acf47 YES YES
1292784087274697613                      SYS_SQL_11f0e4472549338d       SQL_PLAN_13w748wknkcwd8576eb1f YES YES

I have two enabled and accepted sql plan baseline (one, SQL_PLAN …eb1f, for the index range scan and the other one, SQL_PLAN … acf47, for the table full scan). Now, I will create an extra index(i2) in addition to the existing i1 index  and I will continue my selects

SQL > create index i2 on t1(flag,col2) compress;

Index created.

I will then first execute my query for FULL TABLE SCAN bind variable

SQL > exec :n := 'N1';

PL/SQL procedure successfully completed.

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

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

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

SQL_ID  731b98a8u0knf, child number 2
An uncaught error happened in prepare_sql_statement : ORA-01403: no data found
NOTE: cannot fetch plan for SQL_ID: 731b98a8u0knf, CHILD_NUMBER: 2
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)

This error is an  indication that something went abnormally as already notified by the Oracle Optimizer blog.

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

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

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

SQL_ID  731b98a8u0knf, child number 2
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   275 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    54 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 49872 |  2629K|   275   (2)| 00:00:04 |
---------------------------------------------------------------------------

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

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

For the sake of clarity I will present below the results of is_bind_aware.sql before the creation of the I2 index and after its creation

SQL > @is_bind_aware.sql  --> before the index creation

SQL_ID        CHILD_NUMBER I I I SIG                EXECUTIONS  PLAN_HASH_VALUE
------------- ------------ - - - ------------------ ----------- -----------------
731b98a8u0knf 0            N Y N 1292784087274697613 2          3724264953
731b98a8u0knf 1            Y Y N 1292784087274697613 1          3625400295
731b98a8u0knf 2            Y Y Y 1292784087274697613 1          3724264953   --> TABLE FULL SCAN
731b98a8u0knf 3            Y Y Y 1292784087274697613 1          3625400295   --> INDEX i1 RANGE SCAN

SQL > @is_bind_aware.sql  --> after the index creation

SQL_ID        CHILD_NUMBER I I I SIG                                      EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - - ---------------------------------------- ---------- ---------------
731b98a8u0knf            0 N Y N 1292784087274697613                               2      3724264953
731b98a8u0knf            1 Y Y N 1292784087274697613                               1      3625400295
731b98a8u0knf            2 N N Y 1292784087274697613                               1      3724264953 --> TABLE FULL SCAN

Wow!!! Child cursor n° 3 has gone while child cursor n° 2, despite it is still shareable, becomes however not bind sensitive and not bind aware. And how this has influenced the ACS view?

SQL > select * from v$sql_cs_statistics where sql_id = '731b98a8u0knf'; --> before the index creation

ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED
-------- ---------- ------------- ------------ ------------------- - ---------- --------------
22A4D04C 2443201166 731b98a8u0knf            3          3066078819 Y          1              3
22A4D04C 2443201166 731b98a8u0knf            2          3938583969 Y          1          50000
22A4D04C 2443201166 731b98a8u0knf            1          2780714206 Y          1              3
22A4D04C 2443201166 731b98a8u0knf            0          1896453392 Y          1          50000

SQL > select * from v$sql_cs_statistics where sql_id = '731b98a8u0knf'; --> after the index creation

ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED
-------- ---------- ------------- ------------ ------------------- - ---------- --------------
22A4D04C 2443201166 731b98a8u0knf            1          2780714206 Y          1              3
22A4D04C 2443201166 731b98a8u0knf            0          1896453392 Y          1          50000

No trace of child cursor n ° 2 or n ° 3 in this view while the presence of child cursor n ° 0 and n ° 1 can be considered as obsolete because they represent a non shareable cursors.

Let’s continue executing the query this time using the INDEX RANGE SCAN bind variable

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 2
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   275 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    54 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 49872 |  2629K|   275   (2)| 00:00:04 |
---------------------------------------------------------------------------

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

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

Repeat the same sql several times to see if  ACS will kick off and produce the INDEX RANGE SCAN plan  (the one identified into the SPM baseline SQL_PLAN …eb1f)

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

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

SQL > /

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

SQL > /

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

… Execute this several times

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 2
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   275 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    54 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 49872 |  2629K|   275   (2)| 00:00:04 |
---------------------------------------------------------------------------

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

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

No way for the CBO to produce the INDEX RANGE SCAN Plan so that the SPM will select it. And why the CBO is unable to produce the INDEX RANGE SCAN plan? There might be two answers to that question (a) either the ACS is working well but it is producing a plan that is not in the SPM and hence it is constrained or (b) the ACS is not working and the CBO is always sharing the existing FULL TABLE SCAN until a hard parse occurs. Let see first if the ACS is working well

SQL> @is_bind_aware

SQL_ID        CHILD_NUMBER I I I SIG                  EXECUTIONS  PLAN_HASH_VALUE
------------- ------------ - - - -------------------- ----------- -----------------
731b98a8u0knf  0           N Y N 1292784087274697613  2           3724264953
731b98a8u0knf  1           Y Y N 1292784087274697613  1           3625400295
731b98a8u0knf  2           N N Y 1292784087274697613  18          3724264953

SQL > select * from v$sql_cs_statistics where sql_id = '731b98a8u0knf';

ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED
-------- ---------- ------------- ------------ ------------------- - ---------- --------------
22A4D04C 2443201166 731b98a8u0knf            1          2780714206 Y          1              3
22A4D04C 2443201166 731b98a8u0knf            0          1896453392 Y          1          50000

ACS is not working!!!

What if I disable the use of sql baseline?

SQL > show parameter '%baseline%'

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE

SQL > alter session set optimizer_use_sql_plan_baselines = FALSE;

Session 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 3
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3625400295
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |    54 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |    18 |   972 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I1   |    18 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

SQL > @is_bind_aware

SQL_ID        CHILD_NUMBER I I I SIG                                      EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - - ---------------------------------------- ---------- ---------------
731b98a8u0knf            0 N Y N 1292784087274697613                               2      3724264953
731b98a8u0knf            1 Y Y N 1292784087274697613                               1      3625400295
731b98a8u0knf            2 N N Y 1292784087274697613                              18      3724264953
731b98a8u0knf            3 N Y Y 1292784087274697613                               1      3625400295

SQL > select * from v$sql_cs_statistics where sql_id = '731b98a8u0knf';

ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED
-------- ---------- ------------- ------------ ------------------- - ---------- --------------
22A4D04C 2443201166 731b98a8u0knf            3          2780714206 Y          1              3
22A4D04C 2443201166 731b98a8u0knf            1          2780714206 Y          1              3
22A4D04C 2443201166 731b98a8u0knf            0          1896453392 Y          1          50000

The new plan is not due to ACS because the corresponding child cursor n°3 is not yet bind aware. So this new plan is due to a hard parse. Let’s continue with the FULL TABLE SCAN bind variable

SQL > exec :n := 'N1'

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

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

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

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

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

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

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

SQL_ID  731b98a8u0knf, child number 4
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 2348726875

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |       |       |    46 (100)|          |
|   1 |  SORT AGGREGATE       |      |     1 |    54 |            |          |
|*  2 |   INDEX FAST FULL SCAN| I2   | 49872 |  2629K|    46   (5)| 00:00:01 |
------------------------------------------------------------------------------

It seems that ACS is back.

SQL >@is_bind_aware

SQL_ID        CHILD_NUMBER I I I SIG                  EXECUTIONS  PLAN_HASH_VALUE
------------- ------------ - - - -------------------- ----------- ----------------
731b98a8u0knf    0         N Y N 1292784087274697613  2           3724264953
731b98a8u0knf    1         Y Y N 1292784087274697613  1           3625400295
731b98a8u0knf    2         N N Y 1292784087274697613  18          3724264953
731b98a8u0knf    3         N Y Y 1292784087274697613  2           3625400295
731b98a8u0knf    4         Y Y Y 1292784087274697613   1          2348726875  --> INDEX FAST FULL SCAN

SQL > select * from v$sql_cs_statistics where sql_id = '731b98a8u0knf';

ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED
-------- ---------- ------------- ------------ ------------------- - ---------- --------------
22A4D04C 2443201166 731b98a8u0knf            4          1896453392 Y          1          50000 -->INDEX FAST FULL S.
22A4D04C 2443201166 731b98a8u0knf            3          2780714206 Y          1              3
22A4D04C 2443201166 731b98a8u0knf            1          2780714206 Y          1              3
22A4D04C 2443201166 731b98a8u0knf            0          1896453392 Y          1          50000

ACS is in fact really back. After several executions I have the following ACS picture

SQL >@is_bind_aware

SQL_ID        CHILD_NUMBER I I I SIG                     EXECUTIONS  PLAN_HASH_VALUE
------------- ------------ - - - ----------------------------------- ----- ---------
731b98a8u0knf            0 N Y N 1292784087274697613      2          3724264953
731b98a8u0knf            1 Y Y N 1292784087274697613      1          3625400295
731b98a8u0knf            2 N N Y 1292784087274697613      18         3724264953
731b98a8u0knf            3 N Y N 1292784087274697613      2          3625400295
731b98a8u0knf            4 Y Y Y 1292784087274697613      1          2348726875 --> INDEX i2 FAST FULL SCAN
731b98a8u0knf            5 Y Y Y 1292784087274697613      1          3625400295 --> INDEX i1 RANGE SCAN

SQL > select * from v$sql_cs_statistics where sql_id = '731b98a8u0knf';

ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED
-------- ---------- ------------- ------------ ------------------- - ---------- --------------
22A4D04C 2443201166 731b98a8u0knf            5          3066078819 Y          1              3  --> INDEX i1 RANGE SCAN
22A4D04C 2443201166 731b98a8u0knf            4          1896453392 Y          1          50000  --> INDEX i2 FFS
22A4D04C 2443201166 731b98a8u0knf            3          2780714206 Y          1              3
22A4D04C 2443201166 731b98a8u0knf            1          2780714206 Y          1              3
22A4D04C 2443201166 731b98a8u0knf            0          1896453392 Y          1          50000

SQL > select * from v$sql_cs_histogram where sql_id = '731b98a8u0knf';

ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
-------- ---------- ------------- ------------ ---------- ----------
22A4D04C 2443201166 731b98a8u0knf            5          0          1   ---> bucket id 0 incremented
22A4D04C 2443201166 731b98a8u0knf            5          1          0
22A4D04C 2443201166 731b98a8u0knf            5          2          0
22A4D04C 2443201166 731b98a8u0knf            4          0          0
22A4D04C 2443201166 731b98a8u0knf            4          1          1   ---> bucket id 1 incremented
22A4D04C 2443201166 731b98a8u0knf            4          2          0
22A4D04C 2443201166 731b98a8u0knf            3          0          1
22A4D04C 2443201166 731b98a8u0knf            3          1          1
22A4D04C 2443201166 731b98a8u0knf            3          2          0
22A4D04C 2443201166 731b98a8u0knf            1          0          1
22A4D04C 2443201166 731b98a8u0knf            1          1          0
22A4D04C 2443201166 731b98a8u0knf            1          2          0
22A4D04C 2443201166 731b98a8u0knf            0          0          1
22A4D04C 2443201166 731b98a8u0knf            0          1          1
22A4D04C 2443201166 731b98a8u0knf            0          2          0

The post is becoming long and may be annoying so I will stop here not without mentioning that I did played with the demo setting the optimizer_capture_sql_plan_baselines to TRUE/FALSE and observing the behavior of ACS through its corresponding views and it seems that adding an extra index generates a new sql plan baseline that is not into the SPM and influence a little bit the work of ACS without knowing the exact reason.

Sql Plan Mangement(SPM) and Adaptive Cursor Sharing(ACS) : My résumé

I read Dominic Brook’s interesting article about Adaptive Cursor Sharing and SQL Plan Baseline. I, then, have read the also interesting follow-up blog article written by one of those modest and smart Oracle guys Coskan Gundogar which he has entitled Adaptive Cursor Sharing with SQL Plan Baselines – Bind Sensitiveness. Finally, I have ended up my “SPM-ACS collaboration Giro” with the Optimizer blog article entitled How do adaptive cursor sharing and SQL Plan Management interact

Let me start by presenting the conclusions of these articles respectively

Dominic’s conclusion

Even with multiple plans in the a baseline, if your ACS information is flushed or ages out of the cache, you’re going to have to repeat the relevant executions required to get that ACS information back. Baselines can’t act as a shortcut to bringing back in that ACS feedback.

Coskan’s conclusion

I personally think they work perfectly fine together but I also wish if Oracle gives option to hold this runtime monitoring info in SYSAUX for env where people can accommodate more data in SYSAUX. This will save a lot of time for the initial loads.

Optimizer group conclusion

If a child cursor is bind-aware, the decision to share or not is made irrespective of whether the query is controlled by SPM.  But once the    query and its current bind values are sent to the optimizer for optimization, SPM constrains the optimizer’s choice of plans, without regard to whether this query is being optimized due to ACS.

I like very much the optimizer group conclusion:

“SPM constrains the optimizer’s choice of plans, without regard to whether this query is being optimized due to ACS”

Yes, that’s very correct.  Because ACS and SPM are playing different goals:

ACS is a feature that helps the CBO  adapt itself to the input bind variable in order to generate an execution plan that best fits that bind variable. ACS, given certain conditions, kicks of independently of the presence or not of a SQL Baseline

SPM is a feature which guarantees plan stability and allow plan evolution. It ensures only accepted plan will be executed whatever the technology used by the CBO to generate the best execution plan: ACS or Cardinality Feedback (even thought that Dominic and Kerry Osborne have already investigated the Interaction of SPM and Cardinality Feedback where they both demonstrated that this interaction is not as simple as it looks).

The logic of plan selection when SPM is used follows the following diagram:

In which we can see that when the best generated CBO plan is not already inside the SQL plan baseline (i.e. plan is ENABLED and ACCEPETD) then it will not be used. Instead, it will be inserted into the SQL plan history (i.e. ENABLED and not ACCEPTED) waiting to be evolved either manually using DBMS_SPM package or automatically when the Tuning Advisor consent to do so.

What does this means all in all?

In my opinion, in order to have a good collaboration between ACS and SPM, we need to load ACS plans (we have better to do that manually than automatically because they will be immediately ENABLED and ACCEPTED) and hope that all plans generated by the CBO via ACS will match the plans we have already loaded into the SPM baseline.  When the CBO comes up with a plan that is not into the SPM baseline it will not be used. Instead all ENABLED and ACCEPTED plans will compete against each other and the best plan from the Baseline will be selected for use.

The optimizer group example is largely sufficient to explain what I have stated above. The goal of this article is to start from the Coskan’s article end and present a curious observation.

A picture is worth a thousand words (in order to make this post as short as possible, select against dba_sql_plan_baseline will be referred to as pbaseline)

 SQL> > select
  2 to_char(signature) signature
  3 , sql_handle
  4 , plan_name
  5 , enabled
  6 , accepted
  7 from dba_sql_plan_baselines
  8 where signature = 1292784087274697613;

 SIGNATURE           SQL_HANDLE                PLAN_NAME                     ENA  ACC
 ---------------------------------------- ------------------------------ ---------------------------------
 1292784087274697613 SYS_SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd616acf47 YES YES -> FULL SCAN
 1292784087274697613 SYS_SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd8576eb1f YES YES -> INDEX RANGE SCAN

 SQL> select sql_id
  2 , child_number
  3 , is_bind_aware
  4 , is_bind_sensitive
  5 , is_shareable
  6 , to_char(exact_matching_signature) sig
  7 , executions
  8 , plan_hash_value
  9 from v$sql
  10 where sql_id = '731b98a8u0knf';

 SQL_ID        CHILD_NUMBER I I I SIG                 EXECUTIONS  PLAN_HASH_VALUE
 ------------- ------------ - - - ------------------- ----------- -----------------
 731b98a8u0knf 0            N Y N 1292784087274697613  2           3625400295
 731b98a8u0knf 1            Y Y Y 1292784087274697613  2           3724264953  -> bind aware
 731b98a8u0knf 2            Y Y N 1292784087274697613  1           3625400295
 731b98a8u0knf 3            Y Y Y 1292784087274697613  1           3625400295  -> bind aware
 

Two plan baselines and two shareable sql child (1 and 3) that are bind sensitive and bind aware so that when FULL bind variable  (‘N1’) is used we get a FULL TABLE SCAN and when INDEX bind variable (‘Y1’) is used we get an INDEX RANGE SCAN.

•  FULL scan: n=’N1’

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

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

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

 Plan hash value: 3724264953

 ---------------------------------------------------------------------------
 | Id | Operation        | Name | Rows  | Bytes    | Cost (%CPU)| Time     |
 ---------------------------------------------------------------------------
 | 0  | SELECT STATEMENT |      |       |          | 275 (100)  |          |
 | 1  | SORT AGGREGATE   |      | 1     | 30       |            |          |
 |* 2 | TABLE ACCESS FULL| T1   | 49872 | 1461K    | 275 (2)    | 00:00:04 |
 ---------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------

 2 - filter("FLAG"=:N)

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

•  INDEX scan : n=’Y1’

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

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

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

 Plan hash value: 3625400295

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

 Predicate Information (identified by operation id):
 ---------------------------------------------------

 3 - access("FLAG"=:N)

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

ACS produces the  plan which best fits the input bind variable and SPM used that plan because it found it into its  SPM baseline.

 SQL> @pbaseline

 SQL_ID        CHILD_NUMBER I I I SIG                  EXECUTIONS   PLAN_HASH_VALUE
 ------------- ------------ - - - -------------------- ----------- -----------------
 731b98a8u0knf 0            N Y N 1292784087274697613   2          3625400295
 731b98a8u0knf 1            N Y N 1292784087274697613   2          3625400295
 731b98a8u0knf 2            Y Y N 1292784087274697613   1          3625400295
 731b98a8u0knf 3            Y Y Y 1292784087274697613   6          3724264953   -> bind aware
 731b98a8u0knf 4            Y Y N 1292784087274697613   2          3625400295
 731b98a8u0knf 5            Y Y Y 1292784087274697613   1          3625400295   -> bind aware
 

So far so good.

Let’s disturb a little bit this situation by creating an extra index on t1.

 SQL> create index I2 on t1(flag, col2) compress;

 Index created.

SQL> select count(*), max(col2) from t1 where flag = :n;
 COUNT(*) MAX(COL2)
 ---------- --------------------------------------------------
  49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

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

 Plan hash value: 3724264953

 ------------------------------------------------------------------------
 | Id | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 ------------------------------------------------------------------------
 | 0  | SELECT STATEMENT |      |       |       | 275 (100)  |          |
 | 1  | SORT AGGREGATE   |      | 1     | 30    |            |          |
 |* 2 | TABLE ACCESS FULL| T1   | 50110 | 1468K | 275 (2)    | 00:00:04 |
 ------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------

 2 - filter("FLAG"=:N)

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

That’s nice. The FULL TABLE SCAN baseline (cf47) kicks off appropriately. But let see what plan the CBO comes up with

 SQL > @pbaseline
 SIGNATURE           SQL_HANDLE               PLAN_NAME                     ENA  ACC
 ------------------- ------------------------ --------------------------- ------ ----
 1292784087274697613 SYS_SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd495f4ddb YES  NO
 1292784087274697613 SYS_SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd616acf47 YES  YES
 1292784087274697613 SYS_SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd8576eb1f YES  YES
 

The CBO comes up with a new execution plan (SQL_PLAN_13w748wknkcwd495f4ddb) which has been constrained(discarded) by the SPM baseline. This new plan has been inserted into the SPM plan history (ACCEPTED =’NO’) for future evolution. The newly generated execution plan uses a INDEX FAST FULL SCAN of the new I2 index and it resembles to:

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

 --------------------------------------------------------------------------------
 Plan name: SQL_PLAN_13w748wknkcwd495f4ddb Plan id: 1230982619
 Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
 --------------------------------------------------------------------------------

 Plan hash value: 2348726875

 ---------------------------------------------------------------------------
 | Id | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 ---------------------------------------------------------------------------
 | 0  | SELECT STATEMENT    |      | 1     | 30    | 46 (5)     | 00:00:01 |
 | 1  | SORT AGGREGATE      |      | 1     | 30    |            |          |
 |* 2 | INDEX FAST FULL SCAN| I2   | 25000 | 732K  | 46 (5)     | 00:00:01 |
 ---------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------

  2 - filter("FLAG"=:N)
 

But what looks strange it this

 SQL> @pbaseline

 SQL_ID        CHILD_NUMBER I I I SIG                 EXECUTIONS PLAN_HASH_VALUE
 ------------- ------------ - - - ------------------- ----------- ---------------

 731b98a8u0knf 0            N Y N 1292784087274697613  2          3625400295
 731b98a8u0knf 1            N Y N 1292784087274697613  2          3625400295
 731b98a8u0knf 2            Y Y N 1292784087274697613  1          3625400295
 731b98a8u0knf 3            N N Y 1292784087274697613  1          3724264953   -> Shareable but not bind aware
 731b98a8u0knf 4            Y Y N 1292784087274697613  2          3625400295
 

The child cursor number 5 has gone!!! I still have only one shareable child cursor (number 3 the one for FULL TABLE SCAN) which became no bind sensitive and no bind aware. Let’s execute the case of an INDEX RANGE SCAN

 SQL> exec :n := 'Y1';

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

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

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

 Plan hash value: 3724264953

 ------------------------------------------------------------------------
 | Id | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 ------------------------------------------------------------------------
 | 0  | SELECT STATEMENT |      |       |       | 275 (100)  |          |
 | 1  | SORT AGGREGATE   |      | 1     | 30    |            |          |
 |* 2 | TABLE ACCESS FULL| T1   | 50110 | 1468K | 275 (2)    | 00:00:04 |
 ------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------

 2 - filter("FLAG"=:N)

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

 SQL> @pbaseline
SQL_ID        CHILD_NUMBER I I I SIG                 EXECUTIONS  PLAN_HASH_VALUE
 ------------- ------------ - - - ------------------- ----------- ---------------
 731b98a8u0knf 0            N Y N 1292784087274697613 8           3724264953
 731b98a8u0knf 1            Y Y N 1292784087274697613 2           3625400295
 731b98a8u0knf 2            N Y N 1292784087274697613 6           3625400295
 731b98a8u0knf 3            N N Y 1292784087274697613 15          3724264953 -> Shareable not bind aware
 731b98a8u0knf 4            Y Y N 1292784087274697613 2           3625400295

No way to make a shareable cursor bind sensitive and bind aware in order for the ACS to kick off and generate a plan that is in the SPM baseline.

Don’t tell me that this is due to the new index I2 I have created.

Will you?

SQL> drop index i2;

Index dropped.

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

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

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

Plan hash value: 3724264953

------------------------------------------------------------------------
 | Id | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 ------------------------------------------------------------------------
 | 0  | SELECT STATEMENT |      |       |       | 275 (100)  |          |
 | 1  | SORT AGGREGATE   |      | 1     | 30    |            |          |
 |* 2 | TABLE ACCESS FULL| T1   | 49872 | 1461K | 275 (2)    | 00:00:04 |
 ------------------------------------------------------------------------

Predicate Information (identified by operation id):
 ---------------------------------------------------

2 - filter("FLAG"=:N)

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

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

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

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

Plan hash value: 3625400295

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

Predicate Information (identified by operation id):
 ---------------------------------------------------

3 - access("FLAG"=:N)

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

SQL> @pbaseline

SQL_ID        CHILD_NUMBER I I I SIG                  EXECUTIONS  PLAN_HASH_VALUE
 ------------- ------------ - - - -------------------------------  ------------------
 731b98a8u0knf 0            N Y N 1292784087274697613   2          3625400295
 731b98a8u0knf 1            N Y N 1292784087274697613   2          3625400295
 731b98a8u0knf 2            Y Y N 1292784087274697613   1          3625400295
 731b98a8u0knf 3            N Y Y 1292784087274697613   6          3724264953
 731b98a8u0knf 4            Y Y N 1292784087274697613   2          3625400295
 731b98a8u0knf 5            Y Y Y 1292784087274697613   1          3625400295  -> bind aware

When I dropped the index, after a warm up execution my ACS is back.

How could a creation of an extra index disturb the ACS behavior? That’s the subject of my next investigation