Mohamed Houri’s Oracle Notes

April 4, 2013

What can impeach Adaptive Cursor Sharing kicking off?

Filed under: Sql Plan Managment — hourim @ 10:54 am

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.

About these ads

5 Comments »

  1. > How could a creation of an extra index disturb the ACS behavior?
    Before the index creation you have four child cursors only two of which are valid and shareable.
    On index creation, children #2 and #3 are also invalidated.
    This is expected, right?
    When you execute the sql immediately after the sql creation, child #2 is revalidated and child#3 is cleaned up (but not #0 and #1).

    The point being that you need the multiple executions to get the bind awareness. And if, for whatever reason, this information is lost (ages out or is invalidated for example sue to index creation) then you need to repeat the multiple executions to regain it.

    Comment by Dom Brooks — April 4, 2013 @ 2:01 pm | Reply

  2. Hi Dominic,

    Thanks for your comment. But see how many times I have executed the query with the index range bind variable value: 18 times without making the corresponding child cursor (n°2) at least bind sensitive .

    SQL> @is_bind_aware
     
    SQL_ID        CHILD_NUMBER I I I SIG                  EXECUTIONS  PLAN_HASH_VALUE
    ------------- ------------ - - - -------------------- ----------- -----------------
    731b98a8u0knf  2           N N Y 1292784087274697613  18          3724264953
    

    And what looks strange for me is that when I disable the use of sql plan baseline, only 2 executions bring back the ACS at work

    SQL >@is_bind_aware
     
    SQL_ID        CHILD_NUMBER I I I SIG                  EXECUTIONS  PLAN_HASH_VALUE
    ------------- ------------ - - - -------------------- ----------- ----------------
    731b98a8u0knf    2         N N Y 1292784087274697613  18          3724264953
    731b98a8u0knf    3         N Y Y 1292784087274697613   2          3625400295  --> only two execution
    731b98a8u0knf    4         Y Y Y 1292784087274697613   1          2348726875  --> INDEX FAST FULL SCAN
    

    Then when I enabled the use of sql plan baseline again, ACS ceases to work correctly.

    May be if I evolve the SQL Plan Baseline related to that newly created index, ACS will be working as expected with SPM.

    I need to re-test this carefully again to be sure about my experiment

    Comment by Houri — April 4, 2013 @ 2:44 pm | Reply

    • Ok. I see where you’re coming from.

      I’ve got a feeling this reply could be the length of post in itself… sorry.

      So, a quick recap for my own sake.

      Bind sensitivity (is_bind_sensitive) is just a flag that indicates that information exists that means a statement could be bind sensitive
      i.e. can be as simple as using binds and peeking @ binds, stats seem to be needed but not necessarily histograms.
      There doesn’t even need to be an index present to offer an alternative access plan.

      Bind awareness is then a step on from bind sensitivity which can indicate (but not always – let’s not go there) that the optimizer has different cursors for different sets of binds.

      But, once bind sensitivity and bind awareness has aged out, you’ve shown that the baseline can have an influence – albeit in a different way from my original post which was just intended to show that a baselined plan contains nothing about bind sensitivity, etc.

      So, looking at your illustration:

      Immediately after the index creation you have some invalid, non-shareable cursors and some invalid, shareable cursors.

      What happens next depends on which bind you run with first and whether you have the two plans – FTS and Index RS – baselined.

      The critical point is that the i2 index now means that for bind := ‘N1′, the optimizer favours an index FFS rather than the FTS previously.

      Regardless of baselines, the first execution of whichever bind results in:
      1. The “clean up” of the invalid, shareable child cursors.

      If you DO NOT have the two baselined plans in place, the first execution of whichever bind results in:
      2. A new child cursor which IS bind sensitive but not bind aware
      If the execution is with bind := ‘N1′ then it should be the “new” index FFS plan, for ‘Y1′ the previous index RS plan.

      If you DO have the two baselined plans in place, if you execute the SQL with bind := ‘Y1′ then:
      2. A new child cursor which IS bind sensitive but not bind aware

      If you DO have the two baselined plans in place, if you execute the SQL with bind := ‘N1′ then:
      2. A new child cursor which IS NOT bind sensitive (and therefore not bind aware either)

      The influence of the baseline here is that for bind N1, the optimizer cannot pick the best cost generated plan with index FFS of i2 because
      it is not baselined so the optimizer has to use an alternative baselined plan.

      That seems to be the critical factor that means that bind sensitivity is not picked up until the ‘Y1′ execution happens.

      There are some interesting observations to be made when running and rerunning the tests.

      I’ve rerun these tests so many times and sometimes the results are not consistent – I’m always hopeful that that’s because I’ve done something in a slightly different order or missed a step but I’m not 100% convinced.
      Even the number of times you have to initially run the ‘Y1′ execution to get the initial bind sensitivity can change.

      Interesting stuff.

      Comment by Dom Brooks — April 4, 2013 @ 5:24 pm | Reply

  3. In order to make things simple, I dropped all the baselines, dropped the index i2, set the optimizer_use_sql_plan_baseline to FALSE and played with my query: ACS works perfectly alternating between FULL TABLE SCAN and INDEX RANGE SCAN. Then I created the i2 index and again ACS works perfectly alternating between INDEX FAST FULL SCAN and INDEX RANGE SCAN. I dropped again the index and ACS works also perfectly as expected.

    In a second step, I set the optimizer_use_sql_plan_baseline to TRUE, and let the optimizer to capture the baseline (in absence of the index) and I have evolved the FTS and the IRS baselines to be ACCEPETED AND ENABLED: and ACS works perfectly with SPM alternating between TABLE FULL SCAN and INDEX RANGE SCAN baselines.

    Finally I created the index, run a query to produce the INDEX FAST FULL SCAN plan which I have immediately set into the baseline as ENABLED and ACCEPTED: ACS works perfectly with SPM alternating between INDEX FAST FULL SCAN and INDEX RANGE SCAN baselines.

    As I have expected when the IFFS baseline is enabled and accepted the underneath index i2 ceases to disturb the good work of ACS.

    Best regards

    FootNote : When I dropped the 3 baselines I got some trouble using the INDEX FAST FULL SCAN baseline with the following error

    SQL> select count(*), max(col2) from t1 where flag = :n;
    select count(*), max(col2) from t1 where flag = :n
                                    *
    ERROR at line 1:
    ORA-00600: internal error code, arguments: [qsmoAlterPlans:4], [], [], [], [], [], [], [], [], [], [], []
    
    SQL> select count(*), max(col2) from t1 where flag = :n;
    select count(*), max(col2) from t1 where flag = :n
                                    *
    ERROR at line 1:
    ORA-38141: SQL plan baseline SQL_PLAN_13w748wknkcwd495f4ddb does not exist 
    

    I was obliged to set the optimizer_capture_sql_plan_baseline to TRUE in order to capture the SQL_PLAN_13w748wknkcwd495f4ddb plan and to avoid this error. Thought that I didn’t do this for the other two baselines despite they have been dropped. This is another story

    Comment by hourim — April 4, 2013 @ 7:26 pm | Reply

  4. […] scan that are both shareable. Thanks to these two child cursors (until they are flushed out, or something disturbs their good working), the CBO will be alternating between the two executions plans giving […]

    Pingback by Literal, bind variable and adaptive cursor sharing: simplify them please!!! | Mohamed Houri’s Oracle Notes — May 18, 2013 @ 11:00 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Database 11g

Oracle Database

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Oracle SQL Tuning Tools and Tips

SQL Tuning, eAdam, eDB360, SQLT, SQLHC

Oracle Scratchpad

Just another Oracle weblog

Tanel Poder's blog: Responsible data management

Linux, Oracle, Exadata and Hadoop.

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

Follow

Get every new post delivered to your Inbox.

Join 81 other followers

%d bloggers like this: