In this Document

Purpose

Scope

Details

References

Applies to:

Oracle Server - Enterprise Edition - Version 11.1.0.6 and later
Information in this document applies to any platform.
***Checked for relevance on 01-Aug-2011***

Purpose

To discuss the Adaptive Cursor Sharing in feature in 11G using a simple testcase

Scope

Support Engineers

DBAs

Details

Introduction

It is a common problem on previous releases of the database that , when bind variables are used, the initial plan can be suboptimal due to the fact that (a) future values used in future executions share the initial plan and (b) the first set of binds used may not be representative of the majority of executions and therefore that initial plan could be considered "bad" in the context of what is best for most executions.

11G introduces the concept of Adaptive Cursor Sharing. The idea now is to no longer blindly share plans but do so only if it is believed the bind values will not degrade a currently stored plan

Testcase

The following testcase is needed to explain the behaviour

create table acs_test (col1 varchar2(1),col2 varchar2(1),col3 varchar2(1));
insert into acs_test values ('A','A','X');
insert into acs_test values ('B','B','X');
begin
for i in 1..20 loop
insert into acs_test  values ('A','C','X');
end loop;
end;
/

begin
for i in 1..20 loop
insert into acs_test  values ('B','D','X');
end loop;
end;
/

begin
for i in 1..958 loop
insert into acs_test  values ('C','D','X');
end loop;
end;
/

create index acs_test_idx on acs_test(col1,col2);

execute dbms_stats.gather_table_stats(NULL,'acs_test',method_opt=>'FOR ALL COLUMNS SIZE 254');

...there are now 1000 rows with the following counts

select col1, count(*) from acs_test group by col1;

C   COUNT(*)
- ----------
A         21
B         21
C        958

SQL> select col2,count(*) from acs_test group by col2;

C   COUNT(*)
- ----------
D        978
A          1
B          1
C         20

The selectivity of each column value is:

COL1

A -> 21/1000 = 0.021
B -> 21/1000 = 0.021
C -> 958/1000 = 0.958

COL2

A -> 1/1000 = 0.001
B -> 1/1000= 0.001
C -> 20/1000 = 0.020
D -> 978/1000 = 0.978

Discussion

The following testcase will be used for the purpose of this discussion:

var b1 varchar2(128);
var b2 varchar2(128);

begin
:B1 := ;
:B2 := ;
end;
/
select * from acs_test where col1=:B1 and col2=:B2;

The selectivities are central to adaptive cursor sharing. The basic concept is that , in the testcase example, a cube is stored centered around an x/y axis where x=sel of col1 and y=sel of col2. If future bind values are such that the x/y for those fall outside that cube it won't share it and will create a new cursor and possibly a new plan so there is now 2 such cubes. If the 3rd execution has bind values that fall inside either of the cubes then they share that child (and therefore it's plan) , otherwise it too will generate a new child/new cube/new plan

Run the cursor for the first time and use :B1 = 'A' and :B2 = 'A'

The first execution will obviously cause a hard parse. If there is a histogram, bind peeking is enabled and the cursor uses binds (which it does) then the sql will be marked as bind sensitive. This can be seen here:

select child_number, executions, buffer_gets,
       is_bind_sensitive BS, is_bind_aware BA,plan_hash_value
from v$sql
where sql_id = '19sxt3v07nzm4';

CHILD_NUMBER EXECUTIONS BUFFER_GETS BS BA PLAN_HASH_VALUE
------------ ---------- ----------- -- -- ---------------
           0          1          30 Y  N       2647430641

Note that the cursor is not currently bind aware. This is because, presently there has been 1 execution and therefore it is not yet known if the bind values are such that they will cause different selectivities for different values when executed. What has happened in this first execution is that information related to the bind values and the execution stats are stored in the cursor.

The current execution statistics for the cursor can be seen using:

SQL> select * from v$sql_cs_statistics where sql_id='19sxt3v07nzm4';

ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS
-------- ---------- ------------- ------------ ------------------- - ----------
ROWS_PROCESSED BUFFER_GETS CPU_TIME
-------------- ----------- ----------
324A9D84 3229253220 19sxt3v07nzm4            0          1498426793 Y          1
             2          30          0

The first execution returned a single row - the selectivity of 'A'/'A' was 0.21/0.01

The plan it uses is

--------------------------------------------------------------------------------------------
| Id | Operation                    | Name         | Rows | Bytes | Cost (%CPU) | Time     |
--------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |              |      |       |     2 (100) |          |
|  1 |  TABLE ACCESS BY INDEX ROWID | ACS_TEST     |    1 |     6 |     2 (0)   | 00:00:01 | 
|  2 |   INDEX RANGE SCAN           | ACS_TEST_IDX |    1 |       |     1 (0)   | 00:00:01 |
--------------------------------------------------------------------------------------------

If it is run again using :B1='C' and :B2='D' this will return 958 rows the selectivity is now 0.958/0.978

It uses the same plan. The buffer gets rises significantly (From 30 to 163)

select child_number, executions, buffer_gets,
       is_bind_sensitive BS, is_bind_aware BA,plan_hash_value
from v$sql
where sql_id = '19sxt3v07nzm4';

CHILD_NUMBER EXECUTIONS BUFFER_GETS BS BA PLAN_HASH_VALUE
------------ ---------- ----------- -- -- ---------------
           0          2         163 Y  N       2647430641

The cursor is still not bind aware because this high buffer get run may be a one-off. Run it once more to force a change in behaviour:

select child_number, executions, buffer_gets,
       is_bind_sensitive BS, is_bind_aware BA,plan_hash_value
from v$sql
where sql_id = '19sxt3v07nzm4';

CHILD_NUMBER EXECUTIONS BUFFER_GETS BS BA PLAN_HASH_VALUE
------------ ---------- ----------- -- -- ---------------
           0          2         163 Y  N       2647430641
           1          1          68 Y  Y       3194050402

Note there is now a new child (1) and this is now marked as bind_aware. The plan for the child has changed to hash_value 3194050402 which is :

------------------------------------------------------------------------------
| Id | Operation          | Name     | Rows | Bytes | Cost (%CPU) | Time     |
------------------------------------------------------------------------------
|  0 | SELECT STATEMENT   |          |      |       |     3 (100) |          |
|  1 |  TABLE ACCESS FULL | ACS_TEST |  936 |  5616 |     3 (0)   | 00:00:01 |
------------------------------------------------------------------------------

The information about each bind value used for each child is stored in V$SQL_CS_SELECTIVITY. This view is only populated when the cursor becomes bind aware

SQL> select CHILD_NUMBER,PREDICATE,RANGE_ID,LOW,HIGH 
     from v$sql_cs_selectivity 
     where sql_id = '19sxt3v07nzm4';

CHILD_NUMBER PREDICATE                                  RANGE_ID LOW        HIGH
------------ ---------------------------------------- ---------- ---------- ----------
           1 =B2                                               0 0.879750   1.075250
           1 =B1                                               0 0.861750   1.053250

Rerun the first SQL again (binds 'A','A'):

SELECT child_number,  executions,  buffer_gets,
  is_bind_sensitive BS,  is_bind_aware BA,  plan_hash_value
FROM v$sql
WHERE sql_id = '19sxt3v07nzm4';

CHILD_NUMBER EXECUTIONS BUFFER_GETS BS BA PLAN_HASH_VALUE
------------ ---------- ----------- -- -- ---------------
           0          2         163 Y  N       2647430641
           1          1          68 Y  Y       3194050402
           2          1           4 Y  Y       2647430641

There is now yet another child and another plan . This is because the cursor is now bind aware and would look to see if it could share child 1, see the ranges for the binds are outside of those for the stored ranges (B1 is 'A' which is a selectivity of 0.021 and not between 0.861750 and 1.05320 ). The range of values required to meet the criteria for sharing is stored with each new child:

SQL> SELECT child_number,  predicate,  range_id,  low,  high
FROM v$sql_cs_selectivity
WHERE sql_id = '19sxt3v07nzm4'; 

CHILD_NUMBER PREDICATE                                  RANGE_ID LOW        HIGH
------------ ---------------------------------------- ---------- ---------- ----------
           2 =B2                                               0 0.000900   0.001100
           2 =B1                                               0 0.018900   0.023100
           1 =B2                                               0 0.879750   1.075250
           1 =B1                                               0 0.861750   1.053250

Child 2 B1 was 0.021 - the range is evenly spread around it (+/- 0.0021) ie 10%

Likewise Child 1 B1 was 0.958 and the range is +/- 10% too

So - any future bind pairs will need to be within BOTH ranges. If any are outside the range then a new child will be created. Running a combination of Child1 and Child2. ie, set B1 to 'A' and B2 to 'D' should create a new child as it cannot share any current one. This returns no rows and creates a new child

SELECT child_number,  executions,  buffer_gets,
  is_bind_sensitive BS,  is_bind_aware BA,  plan_hash_value
FROM v$sql
WHERE sql_id = '19sxt3v07nzm4';

CHILD_NUMBER EXECUTIONS BUFFER_GETS BS BA PLAN_HASH_VALUE
------------ ---------- ----------- -- -- ---------------
           0          2         163 Y  N       2647430641
           1          1          68 Y  Y       3194050402
           2          1           4 Y  Y       2647430641
           3          1           2 Y  Y       2647430641

The plan for the Child is the same as that of Child2.

SQL> SELECT child_number,  predicate,  range_id,  low,  high
FROM v$sql_cs_selectivity order by child_number,range_id,predicate
WHERE sql_id = '19sxt3v07nzm4'; 

CHILD_NUMBER PREDICATE                                  RANGE_ID LOW        HIGH
------------ ---------------------------------------- ---------- ---------- ----------

           1 =B1                                               0 0.861750   1.053250
           1 =B2                                               0 0.879750   1.075250
           2 =B1                                               0 0.018900   0.023100
           2 =B2                                               0 0.000900   0.001100
           3 =B1                                               0 0.018900   0.023100
           3 =B2                                               0 0.879750   1.075250
           3 =B1                                               1 0.018900   0.023100
           3 =B2                                               1 0.000900   0.001100

Something, unexpected until now, happened. There are 4 entries for Child 3. There is a pair of RANGE_IDs for each bind. Note that the LOW/HIGH range for RANGE_ID 1on B1/B2 is the same as that of Child 2 (where we ran with 'A'/'A'). If the execution with 'A'/'A' is run again now it can be seen that it no longer uses Child 2 but , instead uses Child 3.

SELECT child_number,  executions,  buffer_gets,
  is_bind_sensitive BS,  is_bind_aware BA,  plan_hash_value
FROM v$sql
WHERE sql_id = '19sxt3v07nzm4';

CHILD_NUMBER EXECUTIONS BUFFER_GETS B B PLAN_HASH_VALUE
------------ ---------- ----------- - - ---------------
           0          2         137 Y N      2647430641
           1          1          67 Y Y      3194050402
           2          1           4 Y Y      2647430641
           3          2           6 Y Y      2647430641

So - what has happened is that Child3 , because it has used the same plan as Child2 has made that child redundant and the range_ids for Child3 are :

RANGE_ID 0 -- Original Ranges as it would have been if the plan was new.
RANGE_ID 1 -- The incorporation of Child 2s ranges so anything found in that range can use this new Child.

References

NOTE:740052.1 - Adaptive Cursor Sharing: Overview
NOTE:1115994.1 - Introduction to Adaptive Cursor Sharing Concepts and Multimedia Demo [Video]

Adaptive Cursor Sharing: Worked Example (Doc ID 836256.1)

Applies to:

Purpose

Scope

Details

References

Related

Products

Keywords

相關文章