Oracle使用ACS有两个前提条件：
* 绑定变量使用了bind peeking。
* 绑定变量的列上有直方图信息。




1.构造测试表


SQL> create table system.wh(num1 number(10),num2 number(10));


Table created.


SQL> begin
  2          for i in 1 .. 10000 loop        
  3                 if i < 9997 then
  4                   insert into system.wh values(100,i);
  5                   commit;
  6            else
  7                   insert into system.wh values(1000,i);   
  8                   commit; 
  9               end if;         
 10         end loop;
 11      end;
 12      /


PL/SQL procedure successfully completed.


SQL> create index system.ll on system.wh(num1);


Index created.


SQL> execute dbms_stats.gather_table_stats('SYSTEM','WH');


PL/SQL procedure successfully completed.




2.进行测试


第一次执行：
SQL> variable n number;  
SQL> exec :n := 100;  


PL/SQL procedure successfully completed.


SQL> select /*+ gather_plan_statistics */  count(num2) from system.wh n100 where num1=:n;


COUNT(NUM2)
-----------
       9996




SQL> select sql_id,child_number,executions,loads,buffer_gets,is_bind_sensitive as "bind_sensi",is_bind_aware as "bind_aware",is_shareable as "bind_share" from v$sql where sql_text like 'select /*+ gather_plan_statistics */%';


SQL_ID        CHILD_NUMBER EXECUTIONS      LOADS BUFFER_GETS b b b
------------- ------------ ---------- ---------- ----------- - - -
bch86dxzkn3rz            0          1          1          73 Y N Y




SQL>  select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); 






PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  bch86dxzkn3rz, child number 0
-------------------------------------
select /*+ gather_plan_statistics */  count(num2) from system.wh n100
where num1=:n


Plan hash value: 1206455612


--------------------------------------------------------------------------------
--------------


| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buff


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
ers | Reads  |


--------------------------------------------------------------------------------
--------------


|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |
 22 |     21 |


|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |
 22 |     21 |




PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| WH   |      1 |   9996 |   9996 |00:00:00.01 |
 22 |     21 |


--------------------------------------------------------------------------------
--------------




Predicate Information (identified by operation id):
---------------------------------------------------


   2 - filter("NUM1"=:N)


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------




20 rows selected.




第二次执行


SQL> variable n number;  
SQL> exec :n := 1000;  


PL/SQL procedure successfully completed.


SQL> select /*+ gather_plan_statistics */  count(num2) from system.wh n100 where num1=:n;


COUNT(NUM2)
-----------
          4


SQL> select sql_id,child_number,executions,loads,buffer_gets,is_bind_sensitive as "bind_sensi",is_bind_aware as "bind_aware",is_shareable as "bind_share" from v$sql where sql_text like 'select /*+ gather_plan_statistics */%';


SQL_ID        CHILD_NUMBER EXECUTIONS      LOADS BUFFER_GETS b b b
------------- ------------ ---------- ---------- ----------- - - -
bch86dxzkn3rz            0          2          1          95 Y N Y


SQL>  select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); 






PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  bch86dxzkn3rz, child number 0
-------------------------------------
select /*+ gather_plan_statistics */  count(num2) from system.wh n100
where num1=:n


Plan hash value: 1206455612


--------------------------------------------------------------------------------
-----


| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buff


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
ers |


--------------------------------------------------------------------------------
-----


|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |
 22 |


|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |
 22 |




PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| WH   |      1 |   9996 |      4 |00:00:00.01 |
 22 |


--------------------------------------------------------------------------------
-----




Predicate Information (identified by operation id):
---------------------------------------------------


   2 - filter("NUM1"=:N)


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------




20 rows selected.


看吧,n=1000,应该走索引效率高,但走了全表.这就是绑定变量窥视的缺点


第三次执行


SQL> variable n number;  
exec :n := 1000;  
SQL> 
PL/SQL procedure successfully completed.


SQL> select /*+ gather_plan_statistics */  count(num2) from system.wh n100 where num1=:n;


COUNT(NUM2)
-----------
          4


SQL> select sql_id,child_number,executions,loads,buffer_gets,is_bind_sensitive as "bind_sensi",is_bind_aware as "bind_aware",is_shareable as "bind_share" from v$sql where sql_text like 'select /*+ gather_plan_statistics */%';


SQL_ID        CHILD_NUMBER EXECUTIONS      LOADS BUFFER_GETS b b b
------------- ------------ ---------- ---------- ----------- - - -
bch86dxzkn3rz            0          2          1          95 Y N N
bch86dxzkn3rz            1          1          1           3 Y Y Y


SQL>  select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); 




PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  bch86dxzkn3rz, child number 1
-------------------------------------
select /*+ gather_plan_statistics */  count(num2) from system.wh n100
where num1=:n


Plan hash value: 756347146


--------------------------------------------------------------------------------
------------------------


| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Tim


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
e   | Buffers | Reads  |


--------------------------------------------------------------------------------
------------------------


|   0 | SELECT STATEMENT             |      |      1 |        |      1 |00:00:00
.01 |       3 |      2 |


|   1 |  SORT AGGREGATE              |      |      1 |      1 |      1 |00:00:00
.01 |       3 |      2 |




PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   2 |   TABLE ACCESS BY INDEX ROWID| WH   |      1 |      4 |      4 |00:00:00
.01 |       3 |      2 |


|*  3 |    INDEX RANGE SCAN          | LL   |      1 |      4 |      4 |00:00:00
.01 |       2 |      2 |


--------------------------------------------------------------------------------
------------------------




Predicate Information (identified by operation id):


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------


   3 - access("NUM1"=:N)




21 rows selected.


看吧,这次执行计划是正常的了.
咱们回头来分析一下吧.
EXECUTIONS 
这个列不用说了，就是执行的次数
is_bind_sensitive
表示游标是否对绑定变量敏感。数值如果为Y，表示当绑定变量的数值发生变化后，优化器有可能会产生一个不同的执行计划，简单说就是ACS生效了。
is_bind_aware 
表示该游标是否使用了extended cursor sharing技术，数值如果为Y，表示oracle认为此处cursor的值可能会改变执行计划。
表示这条带绑定变量的SQL对应的cursor执行计划已经作为后续相同SQL语句生成执行计划时的候选对象，即后续完全有可能使用软解析沿用此执行计划。
回头看了三次的值,分别为 N N Y,也就是说前两次都不可以沿用,最后一次可沿用.
is_shareable 
表示该游标能否重用，能否被下次共享。数值如果为Y表示能够共享，数值如果为N表示该子游标失去了共享价值，等待被Age Out出内存；
回头看了三次的值,分别为 Y Y Y 
看来三个游标,都是有效的