AdaptiveCursor Sharing 概述
       在Oracle 10g 和11g中对绑定变量的处理,已经有所不同, 在Oracle 10g中,绑定变量相对比较简单,当使用绑定变量的SQL 第一次执行时,会进行硬解析,生成plan 和cursor。 在这个过程中,Oracle 会使用bind peeking,即将绑定变量的值带入,从而选择最优的一个plan。 以后每次执行都使用这个plan。

       在以后的执行时,如果因为其他原因导致cursor 不可重用,那么就会生成一个child_cursor. 这个cursor 不可重用的原因可以查看:“sql_shared_cursor” 视图。那么这就有一个问题。如果列上有列上有严重的数据倾斜,某个字段中99%是值1,1%是值0. 当我们用0 来进行peeking的时候,这时候会走索引,并且以后的所有plan 都是使用这个。 如果我们的绑定值变成了1. 这个时候,明显走全表扫描比索引划算。

       但是Oracle 10g 下还是会使用第一次的plan,即使这个plan 不是最优的。所以在Oracle 10g下,如果数据存在数据倾斜,那么最好不要使用绑定变量。在Oracle 11g 以后在绑定变量这块有所以改变,会生成一个范围值的执行计划。 然后每次传变量进去就对比范围,选择最优的执行计划。与这个功能相关的参数保存在vsql视图中:is_bind_sensitive,is_bind_aware,is_shareable。 这几个字段,在Oracle 10g的vsql视图中:isb​inds​ensitive,isb​inda​ware,iss​hareable。这几个字段,在Oracle10g的vsql 视图里是没有的。我们这里要说明的Adaptive Cursor Sharing 特性,其允许一个使用绑定变量的SQL语句使用多个执行计划。对于同一个SQL, 为了得到合适的查询,oracle 会监控使用不同绑定变量的情况,已确保对不同绑定变量值的cursor(执行计划)都是最优的。比如因为数据倾斜的原因对绑定变量值A 使用执行计划A,对绑定变量值B 使用执行计划B。 虽然他们的SQL 是相同的,但执行计划不同。Adaptive Cursor Sharing 默认启动的。 不过要注意的是,该特性只有在绑定变量的参数个数不超过14个的情况才有效。

–演示ACS(adaptiver cursor sharing)的效果。

首先创建测试表并让object_id列倾斜,并采用直方图收集统计信息:

create table t as select * from dba_objects;
Update t set object_id =0 where object_id>200;
Commit;
create index t_id on t(object_id);
 
SQL> select object_id,count(*) from t  group by object_id having object_id in(100,150,0);
 
 OBJECT_ID   COUNT(*)
---------- ----------
       100          1
       150          1
         0      76941
 

采用直方图收集统计信息:

SQL> execute dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'T',cascade => true,estimate_percent => 100,method_opt=>'for all columns  size 254');
PL/SQL procedure successfully completed

然后分别执行下列语句模拟绑定变量传入不平衡值产生的效果:

alter system flush shared_pool;