如何进行 Adaptive Cursor Sharing的研究

如何进行 Adaptive Cursor Sharing的研究，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。

    Adaptive Cursor Sharing（ACS）是又一个大胆而吸引人的11G新特性。

说它大胆是因为它试图解决一个CBO最令人头疼的问题：数据倾斜（data skew）和绑定变量窥视导致SQL PLAN太差。说它吸引人是因为想知道Oracle采用何种神秘的算法让Oracle变得更加智能。

    之所以在11GR2出来之后才开始研究，是因为这个new feture在11GR1时有各种各样的问题，首先映入我眼帘的就是这个bug：

Bug 7213010  Adaptive cursor sharing generates lots of child cursors

This issue is fixed in 11.2 (Future Release),11.1.0.7 (Server Patch Set)

    ACS其实就是根据不同绑定变量的值为同一个SQL生成更多更优的执行计划，来适应data skew的不同情况。正因为如此，才会有如上的bug出现。

    <一> 我们先用一个简单的例子来走近ACS。

（注意：以下实验的SQL PLAN的获取不能信任set autotrace，他不会显示各个child cursor的实际执行计划，我们可以通过SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR(:sqlid, NULL, 'TYPICAL LAST'))来得到真实的PLAN。）

新建一个两个字段的表，其中id这列十分倾斜，并在id这列上创建index，并使用SKEWONLY选项分析表，使其生成histogram。

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

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=SIMPLE

SQL> desc testbyhao
 Name  Type
 ----- --------
 ID    NUMBER
 NAME  VARCHAR2(128)

SQL> select id,count(*) from testbyhao
     group by id;  

        ID   COUNT(*)
---------- ----------
         1     104096
         2        498
SQL> create index testidx on testbyhao(id);

Index created.       
        
SQL> exec dbms_stats.gather_table_stats(user,'TESTBYHAO',
     method_opt=>'for all columns size skewonly');

PL/SQL procedure successfully completed.

SQL> select COLUMN_NAME,HISTOGRAM from user_tab_columns
     where TABLE_NAME='TESTBYHAO';

COLUMN_NAM HISTOGRAM
---------- ------------------------------
ID         FREQUENCY
NAME       HEIGHT BALANCED

先生成一个最简单的执行计划index range scan。

对于id=2来说，是相当合适的。

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

Plan hash value: 254123311

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TESTBYHAO |   387 |  8127 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TESTIDX   |   387 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   2 - access("ID"=:V)
   

从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。

  
SQL> select CHILD_NUMBER,PLAN_HASH_VALUE,EXECUTIONS,
     BUFFER_GETS/EXECUTIONS BG_PER_EX,
     IS_BIND_SENSITIVE BS,IS_BIND_AWARE BA,IS_SHAREABLE S
     from v$sql where hash_value=1659091011;                                                         

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS  BG_PER_EX BS  BA  S
------------ --------------- ---------- ---------- --- --- ---
           0       254123311          1        221 Y   N   Y
          
更换绑定变量，使用id=1执行同样的SQL.

SQL> exec :v := 1;  
SQL> select /*comments*/ * from TESTBYHAO
  2  where id = :v;
  
结果，使用绑定变量id=1的SQL使用了同样的index range scan的cursor。这其实不是我们希望的，因为id=1时明显走全表扫描cost更低。

v$SQL没怎么变，只是同样的cursor执行次数为2了。         

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS  BG_PER_EX BS  BA  S
------------ --------------- ---------- ---------- --- --- ---
           0       254123311          2     7321.5 Y   N   Y
          
再次执行同样的id=1的SQL。

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

Plan hash value: 325910803

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |   109 (100)|          |
|*  1 |  TABLE ACCESS FULL| TESTBYHAO |   104K|  2136K|   109   (4)| 00:00:02 |
-------------------------------------------------------------------------------

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

   1 - filter("ID"=:V)
          
终于，我们期望的事情发生了，新的全表扫描的执行计划产生了！（对应于CHILD_NUMBER=1,PLAN_HASH_VALUE=325910803）

v$SQL里，新的cursor的IS_BIND_AWARE=Y。

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS  BG_PER_EX BS  BA  S
------------ --------------- ---------- ---------- --- --- ---
           0       254123311          2     7321.5 Y   N   Y
           1       325910803          1       7296 Y   Y   Y              
  
再次执行id=1的SQL

SQL> exec :v := 1;          
SQL> select /*comments*/ * from TESTBYHAO
  2  where id = :v; 
 
CHILD 1执行次数增加为2

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS  BG_PER_EX BS  BA  S
------------ --------------- ---------- ---------- --- --- ---
           0       254123311          2     7321.5 Y   N   Y
           1       325910803          2       7296 Y   Y   Y   
  
再次执行id=2的SQL

SQL> exec :v := 2;          
SQL> select /*comments*/ * from TESTBYHAO
  2  where id = :v;    
  
奇怪的事情发生了，又新生成了一个index range scan的cursor（CHILD 2），并且CHILD 0的IS_SHAREABLE=N了，表明这个cursor不再被使用了。

我想这是因为Oracle会监控每个cursor的平均selectivity，当新进来的绑定变量的cursor跟现有的cursor都差得比较远时，就会新生成一个cursor，即使他们的执行计划是有可能一样的。

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS  BG_PER_EX BS  BA  S
------------ --------------- ---------- ---------- --- --- ---
           0       254123311          2     7321.5 Y   N   N
           1       325910803          2       7296 Y   Y   Y
           2       254123311          1         73 Y   Y   Y
          
再次执行id=2的SQL

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

CHILD 2 执行次数增加为2

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

更换绑定变量id=999，再次执行。

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

果然，新的cursor CHILD 3出生了，虽然他依然使用的是index range scan，但它的selectivity是0。

这时，CHILD 2又“死”了。(IS_SHAREABLE=N)

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS  BG_PER_EX BS  BA  S
------------ --------------- ---------- ---------- --- --- ---
           0       254123311          2     7321.5 Y   N   N
           1       325910803          2       7296 Y   Y   Y
           2       254123311          2         73 Y   Y   N
           3       254123311          1          2 Y   Y   Y  
 
使用id=2再来试试。

SQL> exec :v := 2;          
SQL> select /*comments*/ * from TESTBYHAO
  2  where id = :v;       
    
使用了新的CHILD 3的cursor。

SQL> /

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS  BG_PER_EX BS  BA  S
------------ --------------- ---------- ---------- --- --- ---
           0       254123311          2     7321.5 Y   N   N
           1       325910803          2       7296 Y   Y   Y
           2       254123311          2         73 Y   Y   N
           3       254123311          2       37.5 Y   Y   Y     
    
再换个绑定变量id=111试试。

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

依然使用了CHILD 3，看来现在执行计划基本处于一种稳定的状态了。

SQL> /

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS  BG_PER_EX BS  BA  S
------------ --------------- ---------- ---------- --- --- ---
           0       254123311          2     7321.5 Y   N   N
           1       325910803          2       7296 Y   Y   Y
           2       254123311          2         73 Y   Y   N
           3       254123311          3 25.6666667 Y   Y   Y
    
再使用id=1来试试

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

果然，CHILD 1被使用。

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

    <二> 接着，我们来关注一下三个ACS的视图。

1.v$sql_cs_histogram

SQL> SELECT CHILD_NUMBER,BUCKET_ID,COUNT FROM v$sql_cs_histogram
  2  WHERE HASH_VALUE = '1659091011' order by 1,2,3;

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

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

2.v$sql_cs_selectivity

SQL> l
  1  SELECT CHILD_NUMBER,PREDICATE,RANGE_ID,LOW,HIGH FROM
  2* v$sql_cs_selectivity WHERE HASH_VALUE = '1659091011'
SQL> /

CHILD_NUMBER PREDICATE    RANGE_ID LOW                            HIGH
------------ ---------- ---------- ------------------------------ ------------------------------
           3 =V                  0 0.001705                       0.004070
           2 =V                  0 0.003330                       0.004070
           1 =V                  0 0.896589                       1.095831

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

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

3.v$sql_cs_statistics

SQL> SELECT CHILD_NUMBER,BIND_SET_HASH_VALUE,PEEKED,EXECUTIONS,
  2  ROWS_PROCESSED,BUFFER_GETS,CPU_TIME
  3  FROM v$sql_cs_statistics  WHERE HASH_VALUE = '1659091011';

CHILD_NUMBER BIND_SET_HASH_VALUE PEE EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME
------------ ------------------- --- ---------- -------------- ----------- ----------
           3          3028748107 Y            1              0           2          0
           2          2064090006 Y            1            996          73          0
           1          2342552567 Y            1         104096        7296          0
           0          2064090006 Y            1            996         221          0

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

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

SQL> SELECT CHILD_NUMBER,VALUE_STRING,LAST_CAPTURED
  2  FROM v$sql_bind_capture WHERE HASH_VALUE = '1659091011' order by 1;

CHILD_NUMBER VALUE_STRI LAST_CAPTURED
------------ ---------- -----------------
           0 2          20091203 05:37:11
           1 1          20091203 05:39:23
           2 2          20091203 05:42:18
           3 999        20091203 05:43: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控制了！

结语：本来对于ACS这个有趣的特性还有无数的实验要做。比如我心里还有十万个为什么：SQL profile受不受ACS控制？那三个ACS的隐藏参数每个的具体作用是什么？ACS的选择率是如何计算出来的？为何有大于1的选择率。。。

看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注亿速云行业资讯频道，感谢您对亿速云的支持。