本文详细探讨了Oracle 11g中引入的自适应游标共享功能，通过一个简单的测试案例展示了该特性如何工作。文章解释了自适应游标共享的基本概念，并通过一系列SQL执行实例说明了不同绑定变量值如何影响查询计划的选择。
In this Document
| Purpose |
| Scope |
| Details |
| References |
APPLIES TO:
Oracle Server - Enterprise Edition - Version 11.1.0.6 and laterInformation 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
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 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 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: OverviewNOTE:1115994.1 - Introduction to Adaptive Cursor Sharing Concepts and Multimedia Demo [Video]
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
