ACS整理-CSDN博客

本文链接：https://blog.csdn.net/ashic/article/details/43271505

整理自http://www.itpub.net/thread-1779225-1-1.html

http://blog.itpub.net/15415488/viewspace-621535

ACS是指Oracle在不同cursor sharing技术之间进行自主选择

Oracle支持两种cursor sharing技术：

传统的PCST（Previous Cursor Sharing Technique），在硬解析时生成cursor，后续的semantically equivalent sql总是共享这个cursor，不会重复硬解析。

Oracle11g新引入的ECST（Extended Cursor Sharing Technique），根据不同semantically equivalent sql的selectivity等因素，Oracle可能会硬解析和创建新的cursor。

采用ECST后，Oracle在解析sql语句时就根据绑定变量值来评估谓词的selectivity。如果在软解析阶段找到可用的child cursor，并且这个child cursor的selectivity范围涵盖了先前评估得到的selectivity，那就共享这个cursor，否则就新建执行计划，也就是硬解析。（child cursor的selectivity范围保存在V$SQL_CS_SELECTIVITY视图中）。硬解析得到新的执行计划后再和原来的child cursor的执行计划做比较，如果两者相差很大，就产生新的child cursor，并记录selectivity范围（例如selectivity是0.01，那么范围就是0.009~0.011。如果是0.2，那么范围可能就是0.15~0.25）。如果执行计划相同，那就共享原来的cursor，并且调整这个cursor的selectivity范围。

Oracle缺省采用PCST，因为它代价低，并且也足够好。ECST只对数据倾斜得很厉害的表有效。而ACS就是判断在什么情况下由PCST转为ECST。在用相同的child cursor执行多次semantically equivalent sql时，Oracle记录了每次执行时的性能，例如cpu等。这些信息被组织成buckets，每个bucket中包括一个范围和相应的执行次数，例如cpu时间在0.1s~0.2s的次数是1次，在10s~50s的执行次数也是1次。（bucket信息保存在V$SQL_CS_HISTOGRAM中）如果这些统计值相差很大，并且执行次数又差不多，那说明数据倾斜的很厉害，就转为ECST。

初始采用PCST时cursor处于monitored state，使用ECST时就是aware state。也就是v$sql的is_bind_aware

下面我通过做实验来学习ACS：

为了不让其他因素干扰我的实验并且让读者能够重现，我设置如下参数:

optimizer_mode=CHOOSE（使用CBO）

optimizer_features_enable=11.2.0.1（使用最新的优化参数）

optimizer_capture_sql_plan_baselines=false（关闭SPM）

cursor_sharing=EXACT（使用真正的绑定变量）

_optim_peek_user_binds=true（一定要开启绑定变量窥视）

_optimizer_adaptive_cursor_sharing=TRUE（以下三个参数默认开启ACS）

_optimizer_extended_cursor_sharing=UDO

_optimizer_extended_cursor_sharing_rel=SIMPL

查询隐含参数的方法

sys@PROD>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

创建测试表

scott@PROD> create table t as select 1 object_id,object_name from dba_objects;

scott@PROD>select count(*) from t;

COUNT(*)
----------
17689

scott@PROD>select count(*) from t where object_id=1;

COUNT(*)
----------
17689

scott@PROD>update t set object_id=99 where rownum=1;

scott@PROD>commit;

scott@PROD>create index i_t_id on t(object_id);

scott@PROD>exec dbms_stat.gather_table_stats('scott','t',cascade=>true);

scott@PROD>select column_name,histogram from user_tab_columns where table_name='T';

COLUMN_NAME                 HISTOGRAM
------------------------------ ---------------
OBJECT_ID                 FREQUENCY
OBJECT_NAME                 NONE

现在我们看一下分别以object_id=1和object_id=1为谓词条件做查询时的执行计划

先执行这两个查询

scott@PROD>select object_id from t where object_id=1;

scott@PROD>select object_id from t where object_id=99;

sys@PROD>select sql_id,hash_value from v$sql where sql_text like 'select object_id from t where object_id=1';

SQL_ID HASH_VALUE
------------- ----------
366bf1pphk8vp 1795761013

sys@PROD>select sql_id,hash_value from v$sql where sql_text like 'select object_id from t where object_id=99';

SQL_ID HASH_VALUE
------------- ----------
7bracwmz4k9bh 4266206576

scott@PROD>select * from table(dbms_xplan.display_cursor('&sqlid',null,'TYPICAL LAST'));
Enter value for sqlid: 366bf1pphk8vp
old   1: select * from table(dbms_xplan.display_cursor('&sqlid',null,'TYPICAL LAST'))
new   1: select * from table(dbms_xplan.display_cursor('366bf1pphk8vp',null,'TYPICAL LAST'))

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID     366bf1pphk8vp, child number 0
-------------------------------------
select object_id from t where object_id=1

Plan hash value: 521550358

-------------------------------------------------------------------------------
| Id | Operation          | Name   | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |       |       |    11 (100)|           |
|* 1 | INDEX FAST FULL SCAN| I_T_ID | 17688 | 53064 |    11     (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=1)

18 rows selected.

scott@PROD>select * from table(dbms_xplan.display_cursor('&sqlid',null,'TYPICAL LAST'));
Enter value for sqlid: 7bracwmz4k9bh
old   1: select * from table(dbms_xplan.display_cursor('&sqlid',null,'TYPICAL LAST'))
new   1: select * from table(dbms_xplan.display_cursor('7bracwmz4k9bh',null,'TYPICAL LAST'))

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID     7bracwmz4k9bh, child number 0
-------------------------------------
select object_id from t where object_id=99

Plan hash value: 1420114084

---------------------------------------------------------------------------
| Id | Operation     | Name   | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |     |     |     |     1 (100)|     |
|* 1 | INDEX RANGE SCAN| I_T_ID |     1 |     3 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("OBJECT_ID"=99)

18 rows selected.

当没有绑定变量时,两个查询分别走了全表扫描和索引

绑定变量

var a number

exec :a := 1

select object_id from t where object_id=:a;

查一下

scott@PROD>select hash_value from v$sql where sql_text like 'select object_id from t where object_id=:a';

HASH_VALUE
----------
956031419

select plan_hash_value,executions,child_number, IS_BIND_SENSITIVE BS,IS_BIND_AWARE BA,IS_SHAREABLE S
2 from v$sql where hash_value=956031419
3 ;

PLAN_HASH_VALUE EXECUTIONS CHILD_NUMBER B B S
------------

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

521550358 1 0 Y N Y

从v$SQL中，可以看到这个cursor的数据，其中IS_BIND_SENSITIVE=Y，表明使用绑定变量窥视来生成这次执行计划，这次执行计划是取决于这个绑定变量的，如果Oracle发现有其他的绑定变量出现，是可能生成其他的执行计划的。

IS_BIND_AWARE=N，表明Oracle还没有使用extended cursor sharing。

IS_SHAREABLE=Y，表明这个cursor可以被再次使用，即能够共享；反之，设为N代表着这个cursor已经过时了，不会被再用了，这个cursor将会等待被age out出shared pool。

更换绑定变量

scott@PROD>exec :a:=99

PL/SQL procedure successfully completed.

scott@PROD>select object_id from t where object_id=:a;

再次查询v$sql：

sys@PROD>/

PLAN_HASH_VALUE EXECUTIONS CHILD_NUMBER B B S
--------------- ---------- ------------ - - -
521550358 2 0 Y N Y

好像跟我想的不太一样啊，按说这次应该生成新的执行计划啊，用的还是之前的,没走索引啊

在执行一次查询

scott@PROD>select object_id from t where object_id=:a;

OBJECT_ID
----------
99

sys@PROD>/

PLAN_HASH_VALUE EXECUTIONS CHILD_NUMBER B B S
--------------- ---------- ------------ - - -
521550358 2 0 Y N Y
1420114084 1 1 Y Y Y

这次生成了新的子游标 child_number 1 , plan_hash_value 1420114084 可以看到 is_bind_aware=Y说明使用了 Extended Cursor Sharing Technique

再次执行

scott@PROD>select object_id from t where object_id=:a;

OBJECT_ID
----------
99

sys@PROD>/

PLAN_HASH_VALUE EXECUTIONS CHILD_NUMBER B B S
--------------- ---------- ------------ - - -
521550358 2 0 Y N Y
1420114084 2 1 Y Y Y

这次:a:=1再次执行

scott@PROD>exec :a:=1

PL/SQL procedure successfully completed.

scott@PROD>select object_id from t where object_id=:a;

sys@PROD>/

PLAN_HASH_VALUE EXECUTIONS CHILD_NUMBER B B S
--------------- ---------- ------------ - - -
      521550358      2           0 Y N N
     1420114084      2           1 Y Y Y
      521550358      1           2 Y Y Y

！！！居然又生成了一个全表扫描的cursor （child_number=2） child_number 0的cursor

继续执行

scott@PROD>select object_id from t where object_id=:a;

sys@PROD>/

PLAN_HASH_VALUE EXECUTIONS CHILD_NUMBER B B S
--------------- ---------- ------------ - - -
      521550358      2           0 Y N N
     1420114084      2           1 Y Y Y
      521550358 2 2 Y Y Y

继续执行

scott@PROD>select object_id from t where object_id=:a;

sys@PROD>/

PLAN_HASH_VALUE EXECUTIONS CHILD_NUMBER B B S
--------------- ---------- ------------ - - -
      521550358      2           0 Y N N
     1420114084      2           1 Y Y Y
      521550358 3 2 Y Y Y

变量再换回99

scott@PROD>exec :a:=99

PL/SQL procedure successfully completed.

scott@PROD>select object_id from t where object_id=:a;

OBJECT_ID
----------
99

sys@PROD>/

PLAN_HASH_VALUE EXECUTIONS CHILD_NUMBER B B S
--------------- ---------- ------------ - - -
      521550358      2           0 Y N N
     1420114084      3           1 Y Y Y
      521550358      3           2 Y Y Y

v$sql_cs_histogram

sys@PROD>SELECT CHILD_NUMBER,BUCKET_ID,COUNT FROM v$sql_cs_histogram WHERE HASH_VALUE = '956031419' order by 1,2,3;

CHILD_NUMBER BUCKET_ID      COUNT
------------ ---------- ----------
        0           0      1
        0           1      1
        0           2      0
        1           0      3
        1           1      0
        1           2      0
        2           0      0

2 1 3
2 2 0

这个视图对于每个Child Cursor有三个buckets，用来计算每个cursor被执行的次数。

v$sql_cs_selectivity

sys@PROD>SELECT CHILD_NUMBER,PREDICATE,RANGE_ID,LOW,HIGH FROM v$sql_cs_selectivity WHERE HASH_VALUE =956031419;

CHILD_NUMBER PREDICATE RANGE_ID LOW         HIGH
------------ ---------------------------------------- ---------- ---------- ----------
        2 =A 0 0.899949   1.099938
        1 =A 0 0.000051   0.000062

这个视图显示对于每种Child Cursor，它最高和最低的selectivity是多少。

这是因为Oracle不会也不可能对每个绑定变量都产生一个Child Cursor，那么不同绑定变量就得根据自身的selectivity来在已有的Child Cursor中寻找，是否有比较接近的选择率，如果有，那么就重用这个cursor；否则，就如前面我的实验一样，新的Child Cursor就会孕育而生。

v$sql_cs_statistics

SELECT CHILD_NUMBER,BIND_SET_HASH_VALUE,PEEKED,EXECUTIONS,
    ROWS_PROCESSED,BUFFER_GETS,CPU_TIME
3      FROM v$sql_cs_statistics WHERE HASH_VALUE = '956031419';

CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME
------------ ------------------- - ---------- -------------- ----------- ----------
        2           2342552567 Y         1            17688         1218     0
        1           1937997561 Y         1             1 2     0
        0           2342552567 Y         1            17688         1264     0

这个视图故名思意，显示的是各个Child Cursor的统计信息，例如是不是使用了绑定变量窥视，返回行数有多少，逻辑IO有多少等等。

如果需要查看到底是什么绑定变量产生的这些cursor，可以使用如下SQL查询v$sql_bind_capture：

sys@PROD>col VALUE_STRING for a15
sys@PROD> SELECT CHILD_NUMBER,VALUE_STRING,LAST_CAPTURED FROM v$sql_bind_capture WHERE HASH_VALUE =956031419 order by 1;

CHILD_NUMBER VALUE_STRING    LAST_CAPT
------------ --------------- ---------
        0 1               29-JAN-15
        1 99 29-JAN-15
        2 1               29-JAN-15

如何关闭ACS？

alter system set "_optimizer_extended_cursor_sharing_rel"=none;
alter system set "_optimizer_extended_cursor_sharing"=none;
alter system set "_optimizer_adaptive_cursor_sharing"=false;

证明：

SQL> alter session set "_optimizer_extended_cursor_sharing_rel"=none;

SQL> alter session set "_optimizer_extended_cursor_sharing"=none;

SQL> alter session set "_optimizer_adaptive_cursor_sharing"=false;

SQL> alter system flush shared_pool;

SQL> var v number;
SQL> exec :v :=2;
SQL> select /*comments*/ * from TESTBYHAO
2 where id = :v;

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 254123311 1 286 N N Y

SQL> exec :v := 1;
SQL> select /*comments*/ * from TESTBYHAO
2 where id = :v;

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 254123311 2 7354 N N Y

SQL> exec :v := 1;
SQL> select /*comments*/ * from TESTBYHAO
2 where id = :v;

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 254123311 3 9710 N N Y

可见，当我在session级别关闭这三个隐藏参数后，IS_BIND_SENSITIVE始终为N，更换绑定变量后也不会产生新的cursor。

所以，当我们再做11GR2升级时，可以先考虑关闭这三个参数谋求SQL PLAN的稳定的同时，使用其他11G的new feature。

毕竟对于高并发的OLTP数据库，稳定重于一切。

<四>使用hint强制BIND_AWARE

在我研究11G所有新hint时，发现了这个hint：BIND_AWARE。

于是，就有了研究ACS的冲动，才有了这篇文章。

这个hint故名思意，会强制SQL产生BIND_AWARE的cursor。

更加强悍的是，即使你如上面第三点所示关闭了这三个ACS的参数，但hint依旧生效！

我先如上在session级别关闭这三个ACS的参数，然后进行了如下实验。

SQL> exec :v := 1;

SQL> select /*comments*/ * from TESTBYHAO
2 where id = :v;

我们先发现，IS_BIND_AWARE=N。

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 325910803 1 7515 N N Y

接着加上BIND_AWARE这个hint：

SQL> select /*+BIND_AWARE*/ * from TESTBYHAO
2 where id = :v;

可以看见区别了吧，IS_BIND_SENSITIVE=Y，IS_BIND_AWARE=Y。

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 325910803 1 7296 Y Y Y

接下来更换绑定变量再run几次，结果就是我们所熟悉的了。

再重申下，这是在我关闭session级别的ACS参数后进行的测试。

可见，BIND_AWARE这个hint很强悍。

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
           0       325910803          2       7296 Y   Y   Y
           1       254123311          2         73 Y   Y   N
           2       254123311          1          2 Y   Y   Y

于是，我想到了这样的假设的情况，当我们升级到11GR2后，由于对ACS不了解，不敢用，于是再系统级别关闭了ACS的三个参数。但是突然某一天，我发现了一个由于data skew并且采用绑定变量的SQL PLAN不好调整时，我可以让开发人员对这个特定的SQL加上这个hint，让其突破关闭ACS的限制，使用ACS。于是，这似乎可以成为新的SQL tunning的好方法。

<五>万能的outline强于一切

本来写完前四点就想结束了，但突然想到我们现有的系统上使用了无数的outline来固定SQL PLAN。那么如果升级到11GR2后，在ACS的强大统治力下，outline会不会失效呢？

带着这个疑问，我做了如下的实验，结论是：outline强于一切！甚至可以突破BIND_AWARE这个强有力的hint的限制！

实验一：不使用BIND_AWARE这个hint

SQL> select /*comment*/ * from TESTBYHAO
2 where id = :v;

先如愿产生一个ACS会生效的cursor：
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 325910803 1 7519 Y N Y

使用outline固定这个SQL。

alter session set current_schema=HAOZHU_USER;
create outline ol_4107335673 for category temp_plan on
select /*comment*/ * from TESTBYHAO
where id = :v ;

alter session set current_schema=HAOZHU_USER;
create outline ol_temp4107335673 for category temp_plan on
select /*+full(TESTBYHAO)*/ /*comment*/ * from TESTBYHAO
where id = :v;

再exchange这两个outline。

接着换id=2再次执行同样的SQL：

SQL> exec :v:=2

SQL> select /*comment*/ * from TESTBYHAO
2 where id = :v ;

结果v$SQL里产生了一个新的cursor（新的HASH_VALUE），并不是先前的cursor了，也不是先前的Child Cursor。

再多次执行上面的id=2的同样的SQL后，我们可以看到：

SQL> /

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 325910803 4 461.75 N N Y

可见，使用outline后，即使你开启了ACS，ACS也不生效！

实验二：使用BIND_AWARE hint

接着有人会问，你第四点提到的BIND_AWARE这个hint这么强大，能够突破关闭ACS的限制，那么能否突破outline的限制呢？

带着这个疑问，我做了如下实验：

先使用BIND_AWARE hint，走index range scan：

SQL> select /*+BIND_AWARE*/ /*comment*/ * from TESTBYHAO
2 where id = :v;

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 254123311 1 73 Y Y Y

如上，可见三个“Y”看着十分地舒服。

我接着创建outline固定使用全表扫描而不走index。

alter session set current_schema=HAOZHU_USER;
create outline ol_new for category temp_plan on
select /*+BIND_AWARE*/ /*comment*/ * from TESTBYHAO
where id = :v;

alter session set current_schema=HAOZHU_USER;
create outline ol_tempnew for category temp_plan on
select /*+FULL(TESTBYHAO)*/ /*+BIND_AWARE*/ /*comment*/ * from TESTBYHAO
where id = :v;

exchange这两个outline。

然后再run一模一样的这个SQL：

SQL> /

498 rows selected.

Note
-----
- outline "OL_NEW" used for this statement

接着看v$SQL里：

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S
------------ --------------- ---------- ---------- --- --- ---
0 325910803 1 584 N N Y

果然，新的执行计划出现了，代替了原来的那个执行计划。

执行次数还是1，意味者前面的cursor被flush出去了，这是一个崭新的cursor。

IS_BIND_AWARE=N，IS_BIND_SENSITIVE=N意味着这个SQL不受ACS控制了！