acs oracle dba,实例演示11g的ACS(adaptiver cursor sharing)技术

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语句能够选择正确的执行计划。