11G ACS特性探究实验

关于sql的绑定变量，在11G之前，如果谓词列中有了直方图，不可避免的会遇到一个问题，绑定变量窥探(bind peeking)

11G出现了ACS，可以对每个不同的值生成一个相应的子cursor，保证执行计划的合理性：

今天我探究的主要目的是，ACS是否只有在有直方图的情况下才会生成子cursor，有没有直方图会不会影响ACS的应用

结论是：不是的。无论有没有直方图，值变化以后 ACS都会生效，会生成别的子cursor

                不过因为没有直方图，所以任何值返回的rows都是一致的，则执行计划也是完全一致的。

实验过程

---------------------------------确认版本--------------------
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE	11.2.0.2.0	Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

-----------------------------------数据初始化--------------------
SQL> create table t (id1 varchar2(10),id2 varchar2(10),id3 varchar2(10));
SQL> insert into t select 'K','K','K' from dba_objects where rownum<10001;
SQL> insert into t select 'A','K','K' from dba_objects where rownum<10001;
SQL> insert into t select 'M','K','K' from dba_objects where rownum<21;
SQL>COMMIT;
--------------------------------索引---------------------------
SQL> create index idx_t_id1 on t(id1);

-----------------------------------确认数据倾泻很严重-------------------------
SQL> select id1,count(*) from t group by id1;----表初始化完成

ID1	     COUNT(*)
---------- ----------
K		10000
M		   20
A		10000

----------------------------------刷shared_pool，初始化环境----------------------------
SQL> alter system flush shared_pool;

System altered.

---------------------------------------收集统计信息，不收集直方图---------------------------------------
SQL> exec dbms_stats.gather_table_stats('SYS','T',cascade=>true,method_opt=>'for columns size 1 id1',estimate_percent=>100);

-------------------------------------------------实验开始-----------------------------
-----------------------绑定变量a，赋值K。
SQL> variable a varchar2(20)
SQL> exec :a :='K';

PL/SQL procedure successfully completed.

SQL> select * from t where id1=:a;  ---根据绑定变量来查
10000 rows selected.

查看父游标
SQL> col sql_text for a50
SQL> set linesize 150
SQL>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select * from t%';
SQL_TEXT					       SQL_ID	   VERSION_COUNT    EXECUTIONS
---------------------------     -------------  -------------    ----------
select * from t where id1=:a	 dkatjhft2bfmv	     1		     1

第一次查询，有一个版本的cursor。这时候查看 执行计划
SQL> select * from table(dbms_xplan.display_cursor('dkatjhft2bfmv',0,'advanced'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	dkatjhft2bfmv, child number 0
-------------------------------------
select * from t where id1=:a

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |	 |	 |    10 (100)| 	 |
|*  1 |  TABLE ACCESS FULL| T	 |  6673 | 40038 |    10   (0)| 00:00:01 |

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

……

……

Peeked Binds (identified by position):

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

   1 - :A (VARCHAR2(30), CSID=854): 'K'

从执行计划来看 这里发生了PEEK
值是'K'被识别了，但是从Rows可以看出是 总行数/基数 算出来的6673

SQL> exec :a :='M';

PL/SQL procedure successfully completed.

这时候把绑定变量设为M，从上面的比例可以看出，M只有20条，

SQL> select * from t where id1=:a;----执行

20 rows selected.
SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select * from t%';
SQL_TEXT					               SQL_ID	        VERSION_COUNT EXECUTIONS
-----------------------------             ------------      ------------  ---------
select * from t where id1=:a			   dkatjhft2bfmv	     1		   2
看到了version_count没有增加。

SQL> select SQL_ID,CHILD_NUMBER,IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE from v$sql where sql_id='dkatjhft2bfmv';

SQL_ID	      CHILD_NUMBER I I I
------------- ------------ - - -
dkatjhft2bfmv		 0     Y N Y

这里看到了 子cursor只有1个。 child_number=0

再次查看执行计划

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |	 |	 |    10 (100)| 	 |
|*  1 |  TABLE ACCESS FULL| T	 |  6673 | 40038 |    10   (0)| 00:00:01 |

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

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / T@SEL$1


Peeked Binds (identified by position):

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

   1 - :A (VARCHAR2(30), CSID=854): 'K'

 依然还是K。
 
 SQL> select * from t where id1=:a;----再次执行
 20 rows selected.
 
 select sql_id,child_number from v$sql where sql_id='dkatjhft2bfmv';
 SQL_ID	      CHILD_NUMBER
------------- ------------
dkatjhft2bfmv		 0
dkatjhft2bfmv		 1
发现多出了一个子游标。两个子游标的rows返回的都是6673，不过peek那里值不同的。
执行计划完全一致
 
 
 --------------------------------------------------------------------------第一档实验结束，11G 如果没有直方图，会发生Adaptive Cursor Sharing，但执行计划完全一直-----------
 
 
 ----------------------------------------------------------------------现在开始收集直方图-----------------------

 SQL> alter system flush shared_pool;

System altered.

SQL> exec dbms_stats.gather_table_stats('SYS','T',CASCADE=>TRUE,ESTIMATE_PERCENT=>100,METHOD_opt=>'for columns size 10 id1');

PL/SQL procedure successfully completed.

---------------看统计信息。FREQUENCY的直方图  NUM_BUCKETS 3个----- 
COLUMN_NAME			 NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM	      NUM_BUCKETS
------------------ ---------- ----------- ----------- --------------- -----------
ID1				    20020	    3	      .01          FREQUENCY		    3


SQL> exec :a:='K';             ----10000行的

PL/SQL procedure successfully completed.

SQL> select * from t where id1=:a;
10000 rows selected.

执行了一遍。
SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select * from t%';

SQL_TEXT					   SQL_ID	 VERSION_COUNT EXECUTIONS
-------------------------------------------------- ------------- ------------- ----------
select * from t where id1=:a			   dkatjhft2bfmv	     1		1

再看执行计划
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |	 |	 |    10 (100)| 	 |
|*  1 |  TABLE ACCESS FULL| T	 | 10000 | 60000 |    10   (0)| 00:00:01 |

Peeked Binds (identified by position):

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

   1 - :A (VARCHAR2(30), CSID=854): 'K'
   
返回的行数是10000 精确的。

------------------现在开始更改绑定变量的值
SQL> exec :a:='M';   ---20行

PL/SQL procedure successfully completed.

SQL> select * from t where id1=:a;
20 rows selected.


SQL> select sql_id,child_number from v$sql where sql_id='dkatjhft2bfmv';

SQL_ID	      CHILD_NUMBER
------------- ------------
dkatjhft2bfmv		 0

还是走了原来的老计划

再来执行一次
SQL> select * from t where id1=:a;
20 rows selected.

出现了一个新计划……
SQL> select sql_id,child_number from v$sql where sql_id='dkatjhft2bfmv';

SQL_ID	      CHILD_NUMBER
------------- ------------
dkatjhft2bfmv		 0
dkatjhft2bfmv		 1

查看新计划的执行计划
SQL> select * from table(dbms_xplan.display_cursor('dkatjhft2bfmv',1,'advanced'));
-----------------------------------------------------------------------------------------
| Id  | Operation		    | Name	| Rows	| Bytes | Cost (%CPU)| Time	|
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |		|	|	|     2 (100)|		|
|   1 |  TABLE ACCESS BY INDEX ROWID| T 	|    20 |   120 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN	    | IDX_T_ID1 |    20 |	|     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

偷窥到了M
Peeked Binds (identified by position):
--------------------------------------

   1 - :A (VARCHAR2(30), CSID=854): 'M'
   

 -------再次更改绑定变量的值，
SQL> exec :a:='A';
SQL> select * from t where id1=:a;  

10000 rows selected.

SQL> select sql_id,child_number from v$sql where sql_id='dkatjhft2bfmv';

SQL_ID	      CHILD_NUMBER
------------- ------------
dkatjhft2bfmv		 0
dkatjhft2bfmv		 1
dkatjhft2bfmv		 2
又多了一个子cursor
查看执行计划
SQL> select * from table(dbms_xplan.display_cursor('dkatjhft2bfmv',2,'advanced'));
--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |	 |	 |    10 (100)| 	 |
|*  1 |  TABLE ACCESS FULL| T	 | 10000 | 60000 |    10   (0)| 00:00:01 |
--------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------

   1 - :A (VARCHAR2(30), CSID=854): 'A'
  又偷窥出了A
  
  
  结论：再有柱状图的时候，会发生ACS
  在统计信息准确的时候，返回的结果是准确的（如果estimate_percent不够可能会有问题）

==================以上是之前简陋的理解，在经过崔华老师的隔空指点后对其有如下理解=============================

什么是自适应？那就是Oracle只需要在它认为目标SQL的执行计划可能发生变化时，触发该SQL再做一次硬解析就好了。因为一点触发了硬解析这个动作，Oracle就会将目标SQL重新再解析一遍，其中就包括对该SQL再做一次绑定变量窥探。显然，再做一次绑定变量窥探后所对应的执行计划就是当前秦兴之下CBO认为的最优执行计划，这个执行计划很可能和该SQL硬解析时候所产生的执行计划 不一样了。也就是说，一个简单的适时触发目标SQL再做一次硬解析的动作就在一定程度上缓解了绑定变量窥探所带来的副作用（副作用就是在第一次传入绑定变量生成执行计划的值，并不具有代表性，11G以前 后面的其他值也只能按照这个执行计划跑，这就是不恰当的）

那么Oracle会在什么时候触发上述硬解析动作？

总的来说，Oracle会根据执行目标SQL时所对应的runtime统计信息（比如所消耗的逻辑读和CPU时间，对应结果集的行数）的变化，以及当前传入的绑定变量输入值所在的谓词条件的可选择率，来综合判断是否需要触发目标SQL的硬解析动作。

介绍自适应游标共享之前，先介绍与之相关的一些基本概念。

自适应游标共享要做的

第一件事就是所谓的扩展游标共享（Extended Cursor Sharing），而扩展游标共享所做的主要事情就是将目标SQL所对应的Child Cursor标记为Bind Sensitive。所谓的“Bind Sensitive”，就是指Oracle觉得某个含绑定变量的目标SQL的执行计划可能会随着所传入的绑定变量输入值的变化而变化。

当满足三个条件时，目标SQL所对应的Child Cursor就会被Oracle标记为Bind Sensitive。

1、启用了绑定变量窥探

2、该SQL使用了绑定变量（不管是自带的还是 设置了Cursor_sharing）

3、该SQL使用的是不安全的谓词条件（如范围查询，目标列上有直方图的等值查询等）

第二件事则是将目标SQL所对应的Child Cursor标记为Bind Aware。所谓的“Bind Aware”，就是指Oracle已经确定某个含绑定变量的目标SQL的执行计划会随着所传入的绑定变量输入值的变化而变化。

满足下面两个条件时，目标SQL所对应的Child Cursor会被标记为Bind Aware。

1、该SQL所对应的子cursor在之前已经被标记为Bind Sensitive。

2、该SQL在接下来连续两次执行时，所对应的runtime统计信息与该SQL之前硬解析时所对应的runtime统计信息有较大差异。

v$sq.的is_bind_sensitive,is_bind_aware 和is_shareable分别用来表示，是否是Bind Sensitive、Bind Aware 、共享的（是否能被重用，如果为N，不被重用，第一时间age out）。

v$sql_cs_statistics 和 v$sql_cs_selectivity 

v$sql_cs_statistics用于显示指定Child Cursor中存储的runtime统计信息

v$sql_cs_selectivity 用于显示Bind Aware的Child Cursor中存储含绑定变量的谓词条件所对应的可选择率的范围

                            标记为Bind Aware的child Cursor所对应的目标SQL再次执行时，Oracle就会比较 当前谓词条件的 和v$sql_cs_selectivity中对应的 可选择率

                             用来决定是硬解析，还是软解析，还是软软解析。

介绍自适应游标共享的整体执行流程。

1、SQL第一次执行，Oracle会硬解析，同时判断是否将该SQL所对应的Child Cursor标记为Bind Sensitive

      标记为Bind Sensitive的Child Cursor，Oracle会把执行该SQL时候的runtime统计信息额外的存储在这个Child Cursor中。

2、该SQL第二次执行，Oracle会软解析，并且重用第一次的执行计划

3、该SQL第三次执行，如果该SQL之前已经被标记为sensitive，而且 第二次和第三次执行该SQL时，runtime统计信息和该SQL第一次硬解析时候记录的runtime统计信息有较大差异，那么该SQL在第三次被执行时候使用硬解析（产生一个新的Child Cursor，并且这个新的cursor会标记为Bind Aware）

4、标记为Bind Aware的Child cursor所对应的SQL， 当该SQL再次执行时，Oracle就会根据当前传入的谓词信息对应的可选择率 来决定是使用硬解析 还是软/软软解析。

      如果可选择率 在v$sql_cs_statistics中记录的可选择率的范围以内，则Oracle会软/软软解析，重用相关Child Cursor中存储的解析树和执行计划，反之则硬解析

      如果硬解析后，执行计划与原有Child Cursor中存储的执行计划相同，那么Oracle除了会产生一个新的child cursor以外，还会把存储相同执行计划的原child Cursor 

     标记为非共享（is_shareable变成N），则标记为非共享的同时，Oacle还会对新生成的Child Cursor执行一个 Cursor 合并的过程