ORACLE 11G利用ACS解决bind peeking带来的问题

一、什么是ACS(adaptiver cursor sharing)

      Oracle通过绑定变量技术解决了SQL语句硬解析过多的问题，降低了资源的争用。但是绑定变量在引入cursor sharing，增加了软解析的同时，也带来了CBO环境下的bind peeking问题。

      所谓bind peeking是指，oracle在第一次解析sql的时候，会“偷偷”地(peek)查看一下输入的绑定变量值，然后根据“偷看”到的数据值来确定执行计划，保存在liberary cache中，作为下一次的执行计划。这就带来一个问题，如果下次sql输入的变量恰好是和第一次取值分布差异很大的数据值，那么对该sql来讲就有可能使用低效甚至是错误的执行计划。

      从oracle11g开始，为了弥补bind peeking的缺陷，oracle引入了ACS(Adaptive Cursor Sharing)技术，该技术将绑定变量的执行计划变为一个基于统计量分析的自适应过程，会根据绑定变量的数值动态调整执行计划。



Oracle使用ACS有两个前提条件：

1、绑定变量使用了bind peeking。

2、绑定变量的列上有直方图信息。

下面对ACS（adaptiver cursor sharing)进行测试。





二、实例演示ACS

1、建立测试对象

SQL> select * from v$version;
BANNER
-----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production

SQL> create table tab_acs(id int,value int);

SQL> begin
  2     for i in 1 .. 20000
  3       loop
  4         execute immediate 'insert into tab_acs values(1,'||i||')';
  5       end loop;
  6     end;
  7  /
PL/SQL procedure successfully completed.

SQL> begin
  2     for i in 1 .. 10
  3       loop
  4         execute immediate 'insert into tab_acs values(2,'||i||')';
  5       end loop;
  6     end;
  7  /
PL/SQL procedure successfully completed.
SQL> commit;

SQL> select id,count(*) from tab_acs group by id;
        ID   COUNT(*)
---------- ----------
         1      20000
         2         10

SQL> create index idx_tab_acs on tab_acs(id);
SQL> exec dbms_stats.gather_table_stats(user,'TAB_ACS',cascade=>true);


2、查看直方图信息
SQL> select dbms_stats.get_param('method_opt') from dual;
DBMS_STATS.GET_PARAM('METHOD_OPT')
-----------------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO

SQL> select table_name,column_name,histogram from dba_tab_col_statistics where table_name='TAB_ACS';
TABLE_NAME                     COLUMN_NAME                    HISTOGRAM
------------------------------ ------------------------------ ---------------
TAB_ACS                        VALUE                          NONE
TAB_ACS                        ID                             FREQUENCY
结果表明测试表tab_acs列上有直方图统计信息。


3、查看未使用绑定变量时的执行计划
SQL> set autotrace trace exp;
SQL> select count(value) from tab_acs where id=1;
Execution Plan
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |     8 |   105   (3)| 00:00:02 |
|   1 |  SORT AGGREGATE    |         |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| TAB_ACS |   199K|  1561K|   105   (3)| 00:00:02 |
------------------------------------------------------------------------------

SQL> select count(value) from tab_acs where id=2;
Execution Plan
--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     1 |     8 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |             |     1 |     8 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TAB_ACS     |   265 |  2120 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_TAB_ACS |   265 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
可见谓词条件为1时走全表扫描，谓词条件为2时走index range scan。


4、查看使用绑定变量时的执行计划
SQL> alter session set optimizer_mode=all_rows;
SQL> alter system flush shared_pool;
SQL> variable x number;
SQL> exec :x:=1;
SQL> select count(value) from tab_acs where id=:x;
COUNT(VALUE)
------------
      200000

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 count(value) from tab_acs where id=:x';

SQL_ID        CHILD_NUMBER EXECUTIONS      LOADS BUFFER_GETS b b b
------------- ------------ ---------- ---------- ----------- - - -
5gy2wu883n8ac            0          1          1         426 Y N Y


设置绑定变量值为2后的第一次查询：
SQL> exec :x:=2;
SQL> select count(value) from tab_acs where id=:x;
COUNT(VALUE)
------------
         100

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 count(value) from tab_acs where id=:x';

SQL_ID        CHILD_NUMBER EXECUTIONS      LOADS BUFFER_GETS b b b
------------- ------------ ---------- ---------- ----------- - - -
5gy2wu883n8ac            0          2          1         800 Y N Y
结果表明，谓词条件为2时的第一次查询，沿用了谓词等于1时的执行计划。


设置绑定变量值为2后的第二次查询：
SQL> exec :x:=2;
SQL>  select count(value) from tab_acs where id=:x;
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 count(value) from tab_acs where id=:x';

SQL_ID        CHILD_NUMBER EXECUTIONS      LOADS BUFFER_GETS b b b
------------- ------------ ---------- ---------- ----------- - - -
5gy2wu883n8ac            0          2          1         800 Y N Y
5gy2wu883n8ac            1          1          1           4 Y Y Y
结果表明，谓词条件为2时的第二次查询，重新生成了新的执行计划。


设置绑定变量值为2后的第三次查询：
SQL> exec :x:=2;
SQL> select count(value) from tab_acs where id=:x;
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 count(value) from tab_acs where id=:x';

SQL_ID        CHILD_NUMBER EXECUTIONS      LOADS BUFFER_GETS b b b
------------- ------------ ---------- ---------- ----------- - - -
5gy2wu883n8ac            0          2          1         800 Y N Y
5gy2wu883n8ac            1          2          1           8 Y Y Y
结果表明，谓词条件为2时的第三次查询，沿用了新生成的执行计划。


设置绑定变量值为2后的第四次查询：
SQL> exec :x:=2;
SQL> select count(value) from tab_acs where id=:x;
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 count(value) from tab_acs where id=:x';

SQL_ID        CHILD_NUMBER EXECUTIONS      LOADS BUFFER_GETS b b b
------------- ------------ ---------- ---------- ----------- - - -
5gy2wu883n8ac            0          2          1         800 Y N Y
5gy2wu883n8ac            1          3          1          12 Y Y Y
结果表明，谓词条件为2时的第四次查询，继续沿用了新生成的执行计划。


Oracle从11g开始，在v$sql视图中增加了is_bind_sensitive、is_bind_aware和is_shareable三列。其中：
1、is_bind_sensitive
       表示游标是否对绑定变量敏感。数值如果为Y，表示当绑定变量的数值发生变化后，优化器有可能会产生一个不同的执行计划，简单说就是ACS生效了。
2、is_bind_aware
      表示该游标是否使用了extended cursor sharing技术，数值如果为Y，表示oracle认为此处cursor的值可能会改变执行计划。
3、is_shareable
      表示该游标能否重用，能否被下次共享。数值如果为Y表示能够共享，数值如果为N表示该子游标失去了共享价值，等待被Age Out出内存；


查看绑定变量为1时的执行计划：
SQL> select * from table(dbms_xplan.display_cursor('5gy2wu883n8ac',format => 'advanced'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID  5gy2wu883n8ac, child number 0
-------------------------------------
select count(value) from tab_acs where id=:x
Plan hash value: 3684903434
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |       |   105 (100)|          |
|   1 |  SORT AGGREGATE    |         |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| TAB_ACS |   199K|  1561K|   105   (3)| 00:00:02 |
------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / TAB_ACS@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "TAB_ACS"@"SEL$1")
      END_OUTLINE_DATA
  */
Peeked Binds (identified by position):
--------------------------------------
   1 - :X (NUMBER): 1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ID"=:X)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=0) COUNT("VALUE")[22]
   2 - "VALUE"[NUMBER,22]
50 rows selected.


查看绑定变量为2时，新生成的执行计划：
SQL> select * from table(dbms_xplan.display_cursor('5gy2wu883n8ac',1,format => 'advanced'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID  5gy2wu883n8ac, child number 1
-------------------------------------
select count(value) from tab_acs where id=:x
Plan hash value: 3029888215
--------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |             |     1 |     8 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TAB_ACS     |   265 |  2120 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_TAB_ACS |   265 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / TAB_ACS@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "TAB_ACS"@"SEL$1" ("TAB_ACS"."ID"))
      END_OUTLINE_DATA
  */
Peeked Binds (identified by position):
--------------------------------------
   1 - :X (NUMBER): 2
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ID"=:X)
Column Projection Information (identified by operation id):
-----------------------------------
   1 - (#keys=0) COUNT("VALUE")[22]
   2 - "VALUE"[NUMBER,22]
   3 - "TAB_ACS".ROWID[ROWID,10]
53 rows selected.

      由执行计划可知，设置绑定变量为2后，第二次以后的执行计划是正确的执行计划。由此可知，ACS技术弥补了bind peeking的不足，保证了绑定变量数值发生变化后，sql语句能够选择正确的执行计划。