Adaptive Cursor Sharing in 11G

posted Aug 9, 2011, 6:00 AM by Sachchida Ojha

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.

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 := <value>;
:B2 := <value>;
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, is_bind_aware,plan_hash_value from v$sql where sql_id = '19sxt3v07nzm4';
CHILD_NUMBER EXECUTIONS BUFFER_GETS I I 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)

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

CHILD_NUMBER EXECUTIONS BUFFER_GETS I I 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:

SQL> select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware from v$sql where sql_id = '19sxt3v07nzm4';

CHILD_NUMBER EXECUTIONS BUFFER_GETS I I 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'):

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

CHILD_NUMBER EXECUTIONS BUFFER_GETS I I 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

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

CHILD_NUMBER EXECUTIONS BUFFER_GETS I I 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 where sql_id = '19sxt3v07nzm4';

CHILD_NUMBER PREDICATE                                  RANGE_ID LOW        HIGH
------------ ---------------------------------------- ---------- ---------- ----------
           3 =B2                                               0 0.879750   1.075250
           3 =B2                                               1 0.000900   0.001100
           3 =B1                                               0 0.018900   0.023100
           3 =B1                                               1 0.018900   0.023100
           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

 

Something, unexpected until now, happened.  There are 3 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.

CHILD_NUMBER EXECUTIONS BUFFER_GETS I I PLAN_HASH_VALUE
------------ ---------- ----------- - - ---------------
           0          2         163 Y N      2647430641
           1          1          68 Y Y      3194050402
           2          1           4 Y Y      2647430641  <-- this is the same
           3          2           6 Y Y      2647430641  <-- this has increased

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.

Comments