Adaptive Cursor Sharing

Adaptive cursor sharing is a an interesting feature that came in version 11g, this is an example that shows how it works.

Let’s create a table to use

create table spm as select * from dba_objects;
 create index spm_idx1 on spm (owner);
 exec dbms_stats.gather_table_stats(ownname=>NULL,tabname=>'SPM',
      method_opt=>'for all indexed columns size 254')

Okay we now have some data to work with

select owner,count(owner) from spm group by owner order by 2;
OWNER                           COUNT(OWNER)
 ------------------------------ ------------
 OWBSYS                                    2
 APPQOSSYS                                 5
 ..
 SYS                                   37786

If we search for owner=OWBSYS we will have 2 rows returned and if we search for owner=SYS we will have 37786.

-- declare the bind variable
 variable v1 varchar2(10);
-- assign the value we search for
 exec :v1 := 'OWBSYS';
-- Run the query (2 rows are returned)
 select *
 from spm
 where owner=:v1;
-- Get the plan from the execution
 SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST +PEEKED_BINDS +ALIAS +PREDICATE +COST +BYTES'))
SQL_ID    c2wk2bw8ng6up, child number 0
-------------------------------------
select * from spm where owner=:v1

Plan hash value: 3624814530
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |
 -----------------------------------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT            |          |      1 |        |       |     2 (100)|      2 |00:00:00.01 |        5 |
 |   1 |  TABLE ACCESS BY INDEX ROWID| SPM      |      1 |      8 |   784 |     2   (0)|      2 |00:00:00.01 |        5 |
 |*  2 |   INDEX RANGE SCAN          | SPM_IDX1 |      1 |      8 |       |     1   (0)|      2 |00:00:00.01 |        3 |
 -----------------------------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
 --------------------------------------
1 - (VARCHAR2(30), CSID=873): 'OWBSYS'

Predicate Information (identified by operation id):
 ---------------------------------------------------
2 - access("OWNER"=:V1)

As you see we use the index SPM_IDX1 to retrieve the rows.

Now lets check what happens with the other value SYS which retrieves 37786 rows

* Flush the shared_pool so we will hard parse the query again
* Hard parse means that the optimizer builds the execution path from scratch
* When we hard parse and use bind variables (:V1) the optimizer peeks inside the
variable and use that value to build the plan.

* Flush the shared_pool
alter system flush shared_pool;
* declare the bind variable
variable v1 varchar2(10);
* assign the value we search for
 exec :v1 := 'SYS';
* Run the query
 select *
 from spm
 where owner=:v1;
* Get the plan from the execution
 SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST +PEEKED_BINDS +ALIAS +PREDICATE +COST +BYTES'))
---------------------------------------
 SQL_ID    c2wk2bw8ng6up, child number 0
 -------------------------------------
 select * from spm where owner=:v1

Plan hash value: 2579179811
---------------------------------------------------------------------------------------------------------
 | Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |
 ---------------------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT  |      |    1 |        |       |   347 (100)|  37786 |00:00:00.11 |    3740 |
 |*  1 |  TABLE ACCESS FULL| SPM  |    1 |  37458 |  3584K|   347   (1)|  37786 |00:00:00.11 |    3740 |
 ---------------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
 --------------------------------------
1 - (VARCHAR2(30), CSID=873): 'SYS'

Predicate Information (identified by operation id):
 ---------------------------------------------------
1 - filter("OWNER"=:V1)

As you see we now do a full table scan to retrieve the rows.

This is more or less how it works and should work, less than ~8% rows returned use the index (if it exists one) more than ~8% rows returned, use full table scan.

Flushing the shared_pool is nothing that is done regularly, it was just something I did to show that we got different plans with different values. We use bind variables :v1 to improve performance in the database. We want to avoid hard parsing since that is resource consuming (cpu, locking etc). Read here if you want more information about parsing.

So when we execute a sql statement and build a plan (parsing) the plan is stored in shared_pool to be shared by other users running the same sql.

The sql_id (c2wk2bw8ng6up) is the key, all sql will have a unique sql_id as you see in my example above. If you want to read more how sql_id is generated, see section 7.3.1.3 SQL Sharing Criteria in t he SQL performance Guide.
When the sql_id exists in the shared_pool it will be reused (most of the times)

This can be a problem…

If I run me example again, but without flushing the shared_pool between.
We get the following.

-- assign the value we search for
 exec :v1 := 'OWBSYS';
SQL_ID    c2wk2bw8ng6up, child number 0
 -------------------------------------
 select * from spm where owner=:v1
Plan hash value: 3624814530

-----------------------------------------------------------------------------------------------------------------------
 | Id  | Operation                   | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |
 -----------------------------------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT            |          |      1 |        |       |     2 (100)|      2 |00:00:00.01 |        5 |
 |   1 |  TABLE ACCESS BY INDEX ROWID| SPM      |      1 |      8 |   784 |     2   (0)|      2 |00:00:00.01 |        5 |
 |*  2 |   INDEX RANGE SCAN          | SPM_IDX1 |      1 |      8 |       |     1   (0)|      2 |00:00:00.01 |        3 |
 -----------------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
 --------------------------------------
1 - (VARCHAR2(30), CSID=873): 'OWBSYS'

Predicate Information (identified by operation id):
 --------------------------------------------------
2 - access("OWNER"=:V1)

We get the plan that we want, index is used and it is very efficient. What if we change the search value ?

-- assign the value we search for
 exec :v1 := 'SYS';
SQL_ID    c2wk2bw8ng6up, child number 0
 -------------------------------------
 select * from spm where owner=:v1

Plan hash value: 3624814530

-----------------------------------------------------------------------------------------------------------------------
 | Id  | Operation                   | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |
 -----------------------------------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT            |          |      1 |        |       |     2 (100)|  37786 |00:00:00.17 |     6048 |
 |   1 |  TABLE ACCESS BY INDEX ROWID| SPM      |      1 |      8 |   784 |     2   (0)|  37786 |00:00:00.17 |     6048 |
 |*  2 |   INDEX RANGE SCAN          | SPM_IDX1 |      1 |      8 |       |     1   (0)|  37786 |00:00:00.08 |     2596 |
 -----------------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
 --------------------------------------
1 - (VARCHAR2(30), CSID=873): 'OWBSYS'

Predicate Information (identified by operation id):
 ---------------------------------------------------
2 - access("OWNER"=:V1)

We got the same plan as for the previous execution and that is expected, since the sql_id is the same for both execution the plan was found in shared_pool and it is used. This is not what we want, we know that if we use the plan with full table scan it is more efficient (see buffers in the last column of the plan).
If we switched the order of execution we would get the plan with full table scans for both plans.

Now if we run the sql again, what will we get ?

SQL_ID    c2wk2bw8ng6up, child number 2
 -------------------------------------
 select * from spm where owner=:v1

Plan hash value: 2579179811

---------------------------------------------------------------------------------------------------------
 | Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |
 ---------------------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT  |      |    1 |        |       |   347 (100)|  37786 |00:00:00.11 |    3740 |
 |*  1 |  TABLE ACCESS FULL| SPM  |    1 |  37458 |  3584K|   347   (1)|  37786 |00:00:00.11 |    3740 |
 ---------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
 --------------------------------------
1 - (VARCHAR2(30), CSID=873): 'SYS'

Predicate Information (identified by operation id):
 ---------------------------------------------------
1 - filter("OWNER"=:V1)

Now suddenly we got the better full table scan plan for the same amount of rows.
You can see that child number above has changed from 0 to 2. Oracle has decided to
build a new second plan even if we already have a plan.

Now why does it choose to do that ?
In the view v$sql_shared_cursor we can see why oracle didn’t choose to use the existing plan, if you look at the description of the view you see many columns that can be either
Y/N. If it is Y that is  the reason for not share the existing plan.
There are numerous scripts that help you list the reasons, search MyOracle Support or google it.

In my case we have

SQL_ID                 : c2wk2bw8ng6up
 ADDRESS               : 00000000F4778870
 CHILD_ADDRESS         : 00000000F42C1030
 CHILD_NUMBER          : 2
 BIND_EQUIV_FAILURE    : Y
 REASON                :

So the reason for not sharing is BIND_EQUIV_FAILURE, if we read Oracle reference guide about the view v$sql_shared_cursor it says:
The bind value’s selectivity does not match that used to optimize the existing child cursor.

So the selectivity of the values supplied differs to much and that is correct since one value
returned 2 rows and the other returned 37458 rows. This makes the optimizer to build a new plan.

This is a new feature from 11g and it is called adaptive cursor sharing.
if we read the Oracle Performance Tuning Guide, the info says:

“The adaptive cursor sharing feature enables a single statement that contains bind variables to use multiple execution plans. Cursor sharing is “adaptive” because the cursor adapts its behaviour so that the database does not always use the same plan for each execution or bind variable value.

For appropriate queries, the database monitors data accessed over time for different bind values, ensuring the optimal choice of cursor for a specific bind value. For example, the optimizer might choose one plan for bind value 9 and a different plan for bind value 10. Cursor sharing is “adaptive” because the cursor adapts its behaviour so that the same plan is not always used for each execution or bind variable value.

Adaptive cursor sharing is enabled for the database by default and cannot be disabled. Note that adaptive cursor sharing does not apply to SQL statements containing more than 14 bind variables.”

The documentation says adaptive cursor sharing cannot be disabled, but according to My Oracle Support Doc ID 11657468.8 it can be disabled by setting:

 

Disable adaptive cursor sharing by setting:
_optimizer_adaptive_cursor_sharing = false
AND
_optimizer_extended_cursor_sharing_rel = "none"

 

Advertisements

3 thoughts on “Adaptive Cursor Sharing

  1. Pingback: New and old histograms an overview (11g and 12c) | Oracle Tuning

  2. This is really a great feature, and it can adjust the plan per different case.

    But is it going to add more computing seconds for judging whether it should use old plan or create new plan?

    • Hi Kent
      I don’t know exactly how it works under the hood, but some time is added, not in the range of seconds.
      I don’t expect that it will have any significant impact on the total time.
      And it is worth that extra time, avoiding a bad plan can save you a lot of time.

      Br
      Magnus

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