演示Oracle 11G ACS（adaptiver cursor sharing)的效果【执行计划调优】

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;
alter system flush shared_pool;
col sql_text for a50
var id  number;
exec :id:=100;
select count(*) from t where object_id=:id;
exec :id:=0;
select count(*) from t where object_id=:id;
exec :id:=0;
select count(*) from t where object_id=:id;     --注意这里需要连续执行两次才能使用ACS产生新的子游标

这个时候查询游标信息：

SQL> select sql_id,sql_text,CHILD_NUMBER,PLAN_HASH_VALUE,EXECUTIONS,
  2      BUFFER_GETS,EXECUTIONS,
  3      -- BUFFER_GETS/EXECUTIONS BG_PER_EX,
  4       IS_BIND_SENSITIVE BS,IS_BIND_AWARE BA,IS_SHAREABLE S
  5       from v$sql where sql_text like ' select count(*) from t where object_id%';
 
SQL_ID        SQL_TEXT                                           CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BUFFER_GETS EXECUTIONS BS BA S
------------- -------------------------------------------------- ------------ --------------- ---------- ----------- ---------- -- -- -
6u3w0x5yw02t8  select count(*) from t where object_id=:id                   0      2863432630          2         203          2 Y  N  Y
6u3w0x5yw02t8  select count(*) from t where object_id=:id                   1      3689807224          1         147          1 Y  Y  Y

发现多了一行子游标为1的信息，其中BS=Y表示使用了变量窥探产生执行计划，BA=Y表示了使用ACS，而S=Y表示该子游标可被重用。

这个时候如果再执行其他变量

SQL> exec :id:=150;
 
PL/SQL procedure successfully completed

id
---------
150
SQL> select count(*) from t where object_id=:id;
 
  COUNT(*)
----------
         1
id
---------
150

再看游标信息：

SQL> select sql_id,sql_text,CHILD_NUMBER,PLAN_HASH_VALUE,EXECUTIONS,
  2      BUFFER_GETS,EXECUTIONS,
  3      -- BUFFER_GETS/EXECUTIONS BG_PER_EX,
  4       IS_BIND_SENSITIVE BS,IS_BIND_AWARE BA,IS_SHAREABLE S
  5       from v$sql where sql_text like ' select count(*) from t where object_id%';
 
SQL_ID        SQL_TEXT                                           CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BUFFER_GETS EXECUTIONS BS BA S
------------- -------------------------------------------------- ------------ --------------- ---------- ----------- ---------- -- -- -
6u3w0x5yw02t8  select count(*) from t where object_id=:id                   0      2863432630          2         203          2 Y  N  N
6u3w0x5yw02t8  select count(*) from t where object_id=:id                   1      3689807224          1         147          1 Y  Y  Y
6u3w0x5yw02t8  select count(*) from t where object_id=:id                   2      2863432630          2           4          2 Y  Y  Y

发现oracle 会重新生成一个新的子游标，该子游标的执行计划跟child_number为0的PLAN_HASH_VALUE是一样的，即用新的子游标来替代它，而这个时候这个child_number为0游标会age out出内存不被重用。

用以下方式可查看ACS使用后，执行计划的变化，注意：这里不能用set autotrace这种方式来查看子游标的执行计划，但可用下列explain for的方式查看：
SQL> explain plan for select count(*) from t where object_id=:id;

已解释。

SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('6u3w0x5yw02t8', NULL, 'TYPICAL LAST'));
 
PLAN_TABLE_OUTPUT
SQL_ID  6u3w0x5yw02t8, child number 0
-------------------------------------
 select count(*) from t where object_id=:id
 
Plan hash value: 2863432630
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |      |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| T_ID |     1 |     3 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=:ID)
 
SQL_ID  6u3w0x5yw02t8, child number 1
-------------------------------------
 select count(*) from t where object_id=:id
 
 
Plan hash value: 3689807224
 
------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |       |       |    41 (100)|          |
|   1 |  SORT AGGREGATE       |      |     1 |     3 |            |          |
|*  2 |   INDEX FAST FULL SCAN| T_ID | 76941 |   225K|    41   (3)| 00:00:01 |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("OBJECT_ID"=:ID)
 
SQL_ID  6u3w0x5yw02t8, child number 2
-------------------------------------
 select count(*) from t where object_id=:id
 
Plan hash value: 2863432630
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |      |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| T_ID |     1 |     3 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=:ID)
 
已选择57行。

可见，当变量传入0后，oracle使用了ACS，生成了新的子游标，产生了正确的执行计划，从而提高了执行效率，但付出的代价就是要浪费内存存放更多的子游标。