Oracle 11g 新特性之 Adaptive Cursor Sharing
Posted by admin on 三月 17th, 2013
Oracle 11g 之前版本的数据库中，某些数据分布不均匀的查询会由于绑定变量窥视的问题，导致优化器选择并非最优的执行计划。11g 中引入自适应游标共享的新概念，可以很好的解决这个问题。
下面通过一个具体的例子，详细的介绍这个新特性。
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');
此时，acs_test 表数据分布如下：
SQL> select col1, count(*) from acs_test group by col1 order by col1;
C COUNT(*)
- ———-
A 21
B 21
C 958
SQL> select col2,count(*) from acs_test group by col2 order by col2;
C COUNT(*)
- ———-
A 1
B 1
C 20
D 978
各列的选择度分别如下：
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
首先我们以 col1='A' 和 col2='A' 为条件，进行测试。
SQL> var b1 varchar2(128);
SQL> var b2 varchar2(128);
SQL> exec :b1 := 'A';
SQL> exec :b2 := 'A';
PL/SQL procedure successfully completed.
SQL> select * from acs_test where col1=:B1 and col2=:B2;
C C C
- – -
A A X
SQL> 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 1 65 Y N 2647430641
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
——– ———- ————- ———— ——————- – ———- ————– ———– ———-
2C6AD734 3229253220 19sxt3v07nzm4 0 1498426793 Y 1 2 65 0
从上述的输出可以看出，该查询产生了一次硬解析，我们在测试中使用了绑定变量，因此 is_bind_sensitive 值为 Y。但是 is_bind_aware 值为 N。因为 SQL 才执行了一次，数据库中不存在可以与之想比较的谓词选择度。
接下来再以 col1='C' 和 col2='D' 为条件，进行测试。
SQL> exec :b1 := 'C';
SQL> exec :b2 := 'D';
PL/SQL procedure successfully completed.
SQL> select * from acs_test where col1=:B1 and col2=:B2;
由于该语句是首次执行，bind_aware 未生效，仍然使用的之前产生的执行计划，执行次数加一，buffer_gets 数量从之前的 65 上升到 198。
SQL> 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 198 Y N 2647430641
再次执行后，情况发生了变化，bind_aware 生效，生成了新的子执行计划。
CHILD_NUMBER EXECUTIONS BUFFER_GETS B B PLAN_HASH_VALUE
———— ———- ———– – - —————
0 2 198 Y N 2647430641
1 1 133 Y Y 2647430641
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
——– ———- ————- ———— ——————- – ———- ————– ———– ———-
2C6AD734 3229253220 19sxt3v07nzm4 1 3075073894 Y 1 1916 133 0
2C6AD734 3229253220 19sxt3v07nzm4 0 1498426793 Y 1 2 65 0
bind_aware 生效后，v$sql_cs_selectivity 视图也能够查询到相关的谓词统计信息。
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 =B1 0 0.861750 1.053250
1 =B2 0 0.879750 1.075250
再以 col1='A' 和 col2='A' 为条件，进行测试。
SQL> exec :b1 := 'A';
SQL> exec :b2 := 'A';
PL/SQL procedure successfully completed.
SQL> select * from acs_test where col1=:B1 and col2=:B2;
这次又产生了一个新的子执行计划，由于此时的游标处于 bind aware 状态，通过计算绑定变量选择度的取值范围来确定是否能够和 child 1 共享游标。
SQL> select child_number, executions, buffer_gets, is_bind_sensitive bs, is_bind_aware ba,plan_hash_value
from v$sql
where sql_id = '19sxt3v07nzm4'; 2 3
CHILD_NUMBER EXECUTIONS BUFFER_GETS B B PLAN_HASH_VALUE
———— ———- ———– – - —————
0 2 198 Y N 2647430641
1 1 133 Y Y 2647430641
2 1 4 Y Y 2647430641
当查询条件 col1='A' 和 col2='A' 时，其选择度分别为 COL1 A -> 21/1000 = 0.021 和 COL2 A -> 1/1000 = 0.001，上下各浮动 10%，取值分别为：
child1 low child1 high
col1(a) 0.0189 0.0231 0.861750 1.053250
col2(a) 0.0009 0.0011 0.879750 1.075250
可以看出，选择度并不在 child1 的取值范围区间，因此产生了新的执行计划。
SQL> select child_number,predicate,range_id,low,high
from v$sql_cs_selectivity
where sql_id = '19sxt3v07nzm4'
order by 1,3,2;
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
2 =B1 1 0.861750 1.053250
2 =B2 1 0.879750 1.075250
比较意外的是，child2 生成了两个 range，而 range1 的数据同 child1 的 range0 完全相同。其实从上面的执行计划 HASH 值可以看出，虽然产生了三个不同版本的子游标，但是其执行计划都是一样的 2647430641，所以 Oracle 在 child2 的子游标中加了两个范围，这样不论是 col1='A' 和 col2='A' 还是 col1='C' 和 col2='D'，都使用 child2 而不再使用其他的游标。
CHILD_NUMBER EXECUTIONS BUFFER_GETS B B PLAN_HASH_VALUE
———— ———- ———– – - —————
0 2 198 Y N 2647430641
1 1 133 Y Y 2647430641
2 4 274 Y Y 2647430641
Tags: 11g new feature, adaptive cursor sharing, performance
Posted in 概念解析 | No Comments »