Mohamed Houri’s Oracle Notes

April 1, 2015

12c Adaptive Cursor Sharing

Filed under: Oracle — hourim @ 7:21 pm

This is neither a 12c new feature you are still not aware of nor an extension of the 11 g Adaptive Cursor Sharing I am going to have the scoop to announce it. It is rather something I have pointed out when writing a complete chapter on Adaptive Cursor Sharing and that I wanted to share with you. Here we go.

11g Release

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

I have a simple query executed against two bind variables values. The first one favors an index range scan path while the second one desires a full table scan. The bind variable against which this query is run is related to a column having a frequency histogram collected on it so that when executed several times its underlying cursor starts being bind sensitive before ending up being bind aware following a warmup period. Once the cursor is bind aware, the Extended Cursor Sharing (ECS) layer code kicks in by peeking at the bind variable (there might be several ones), checking its selectivity and deciding whether to share an existing child cursor or compile a new one and update the v$sql_cs_selectivity dynamic view accordingly.

SQL> select
        sql_id
       ,child_number
       ,is_bind_aware
       ,is_bind_sensitive
       ,to_char(exact_matching_signature) sig
       ,executions
       ,plan_hash_value
    from v$sql
    where sql_id = '6fbvysnhkvugw'
    and is_shareable = 'Y';

SQL_ID        CHILD_NUMBER I I SIG                  EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - -------------------- ---------- ---------------
6fbvysnhkvugw            3 Y Y 15340826253708983785    1       3625400295
6fbvysnhkvugw            4 Y Y 15340826253708983785    1       3724264953

In order to finish setting up the blog article scene, I have previously loaded the above two execution plans(plan_hash_value) from cursor cache into a SPM baseline so that I will pre-empt the CBO from using a plan I don’t accept.

SQL> declare
           rs pls_integer;
     begin
           rs := dbms_spm.load_plans_from_cursor_cache('6fbvysnhkvugw');
     end;
     /
PL/SQL procedure successfully completed.

The engineered model (ACS + SPM) is so that when I running the following query alternating between a ”full table scan” bind variable value and an ”index range scan”  one I got the following picture:

SQL> select count(*), max(col2) from t1 where flag = 'N1';-- full table scan

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

Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   273 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    54 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 50135 |  2643K|   273   (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("FLAG"=:SYS_B_0)
Note
-----
   - SQL plan baseline SQL_PLAN_d9tch6banyzg9616acf47 used for this statement

And

SQL> select count(*), max(col2) from t1 where flag = 'Y1'; -- index range scan

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

-------------------------------------------------------------------------------------
| 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"=:SYS_B_0)

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

After a short period of ACS warmup which depends on the number of executions we start observing a perfect harmony between ACS and SPM: an index range scan plan for an ‘’index range scan’’ bind variable and a full table scan plan for a ‘’full table scan’’ bind variable.

However, it suffices to create an extra index and this perfect harmony ceases to work as shown below:

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

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

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

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

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

SQL> select plan_name
    from dba_sql_plan_baselines
    where accepted = 'NO';

PLAN_NAME
------------------------------
SQL_PLAN_d9tch6banyzg9495f4ddb

The CBO has come up with a new execution plan(index fast full scan) which has been constrained by the full table scan SPM plan. This new CBO plan looks like:

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

--------------------------------------------------------------------------------
SQL handle: SQL_d4e59032d54f7de9
SQL text: select count(*), max(col2) from t1 where flag = :"SYS_B_0"
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_d9tch6banyzg9495f4ddb         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 |    54 |   249   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE       |      |     1 |    54 |            |          |
|*  2 |   INDEX FAST FULL SCAN| I2   | 25000 |  1318K|   249   (1)| 00:00:03 |
------------------------------------------------------------------------------

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

Let’s now execute the query with a bind variable favoring an index range scan

SQL> select count(*), max(col2) from t1 where flag = 'Y2'; -- index range scan
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   273 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    54 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 49846 |  2628K|   273   (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
   2 - filter("FLAG"=:SYS_B_0)

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

The index range scan plan has not been used. May be it still needs a warm up period?

SQL> select count(*), max(col2) from t1 where flag = 'Y2';
SQL> /
SQL> /
SQL> /
SQL> /
SQL> /

SQL> select * from table(dbms_xplan.display_cursor);
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   273 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    54 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 49846 |  2628K|   273   (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("FLAG"=:SYS_B_0)

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

SQL> select
          sql_id
         ,child_number
         ,is_bind_aware
         ,is_bind_sensitive
         ,to_char(exact_matching_signature) sig
         ,executions
         ,plan_hash_value
     from v$sql
     where sql_id = '6fbvysnhkvugw'
     and is_shareable = 'Y'
     ;

SQL_ID        CHILD_NUMBER I I SIG                  EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - -------------------- ---------- ---------------
6fbvysnhkvugw            3 N N 15340826253708983785  10      3724264953

10 executions later and the switch to the index range scan didn’t occured.

Bizarrely, it suffices to disable the use of sql_plan_baselines and the plan switch occurs immediately

SQL> alter session set optimizer_use_sql_plan_baselines = FALSE;

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

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"=:SYS_B_0)

Set back the use of sql_plan_baselines and the plan switch will cease to happen immediately as well

SQL> alter session set optimizer_use_sql_plan_baselines = TRUE;

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

Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   273 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    54 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 49846 |  2628K|   273   (1)| 00:00:04 |
---------------------------------------------------------------------------

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

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

12c Release

Repeat exactly the same experiment in 12c and you will realize that things have changed

SQL> select * from v$version where rownum =1;

BANNER                                                                       CON_ID
---------------------------------------------------------------------------- ------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production   0

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

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

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   273 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    54 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 49874 |  2630K|   273   (1)| 00:00:01 |
---------------------------------------------------------------------------

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

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

SQL> select count(*), max(col2) from t1 where flag = 'Y2';
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   273 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    54 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 49874 |  2630K|   273   (1)| 00:00:01 |
---------------------------------------------------------------------------

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

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

Let’s see now if a second execution of the same query with a bind variable favoring index range scan will switch to an index range scan plan


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

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

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

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

And the plan switch happens.

Something new has been introduced in Oracle 12c which has dramatically enhanced the collaboration between ACS and SPM.

In the 11g investigation I’ve executed the same query 10 times without provoking a plan switch. In 12c, I have executed the same query with the ”index range scan” bind variable as many times as the number of executions done with the “full table scan” bind variable (2 executions) to see the ACS kicking off as shown below:

<pre>SQL> select
      sql_id
     ,child_number
     ,is_bind_aware
     ,is_bind_sensitive
     ,to_char(exact_matching_signature) sig
     ,executions
     ,plan_hash_value
    from v$sql
    where sql_id = '6fbvysnhkvugw'
    and is_shareable = 'Y';

SQL_ID        CHILD_NUMBER I I SIG                  EXECUTIONS PLAN_HASH_VALUE
------------- ------------ - - -------------------- ---------- ---------------
6fbvysnhkvugw            1 Y Y 15340826253708983785     1      3724264953
6fbvysnhkvugw            2 Y Y 15340826253708983785     2       497086120
6fbvysnhkvugw            4 Y Y 15340826253708983785     1       497086120
6fbvysnhkvugw            6 Y Y 15340826253708983785     1      3724264953

Leave a Comment »

No comments yet.

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

Create a free website or blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

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

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

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

%d bloggers like this: