11g新特性之自适应游标共享（Adaptive Cursor Sharing）

自适应游标共享会使包含绑定变量的单个语句拥有多个执行计划，所谓自适应是指执行计划会依据绑定变量的具体值而适配一个最适合该值的执行计划。

 隐藏参数_optimizer_adaptive_cursor_sharing=TRUE 开启或关闭此特性。

首先：游标是否可以被ACS使用，首先游标必须是绑定变量敏感的游标，也就是说最优的执行计划会依赖于绑定变量具体的值。数据库会监控绑定变量敏感的游标，观察是否不同的执行计划会对不同的绑定变量值有好处。

游标在以下两个条件满足的时候会被标记为绑定变量敏感的游标：

1.优化器通过绑定变量窥测去做选择性评估。

2.在绑定变量的列上存在直方图信息。

等等

对于传入的每个新的游标的具体值，数据库会记录语句执行时的统计信息，语句执行完后，数据库会对比这次执行的统计信息与之前执行的统计信息，如果两者差距很大，则数据库会将该游标标记为 bind-aware 的游标。

当一个游标被标记为 bind-aware的游标后，只要绑定变量的值落入之前收集的绑定变量值与选择率的直方图内，优化器就会重用一个已经存在并且对该绑定变量来说最优的执行计划，如果绑定变量的值没有落入上述直方图内，则会进行硬解析，由此可见，自适应游标共享的好处是既减少了硬解析的次数，对于不同的绑定变量值来说又能找到一个合适它的执行计划。

游标合并：

当优化器创建新的执行计划为 bind-aware的游标后，如果该执行计划和一个已经存在的游标相同的时候，在这种情况下，优化器会合并游标去节省内存空间，数据库会扩大选择性范围去包括新的绑定变量值的选择性。

游标共享相关的性能视图：

• V$SQL 去检查一个游标是否是 bind-sensitive 以及bind-aware的游标。

• V$SQL_CS_HISTOGRAM 有绑定变量，选择性，执行次数的直方图。

• V$SQL_CS_SELECTIVITY 包括绑定变量对选择性范围。

• V$SQL_CS_STATISTICS summarizes 包括优化器是否将某个游标标记为bind_aware 的统计信息。

SQL> var v_own varchar2(100);
SQL> exec :v_own:='sys';
PL/SQL procedure successfully completed
v_own
---------
sys

SQL>select count(object_name) from testtab where owner=:v_own;
COUNT(OBJECT_NAME)
------------------
                 0
v_own
---------
sys

SQL> select count(object_name) from testtab where owner=:v_own;
COUNT(OBJECT_NAME)
------------------
                 0

　　检查执行计划

L_ID  85man3fnzwka5, child number 0
-------------------------------------
select count(object_name) from testtab where owner=:v_own
 
Plan hash value: 1556530801
 
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |      1 |        |       |     2 (100)|          |      1 |00:00:00.01 |       3 |
|   1 |  SORT AGGREGATE              |          |      1 |      1 |    83 |            |          |      1 |00:00:00.01 |       3 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TESTTAB  |      1 |      1 |    83 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       3 |
|*  3 |    INDEX RANGE SCAN          | IND_TEST |      1 |      1 |       |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1
   2 - SEL$1 / TESTTAB@SEL$1
   3 - SEL$1 / TESTTAB@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "TESTTAB"@"SEL$1" ("TESTTAB"."OWNER"))
      END_OUTLINE_DATA
  */
 
Peeked Binds (identified by position):
--------------------------------------
 
   1 - (VARCHAR2(30), CSID=178): 'sys'
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("OWNER"=:V_OWN)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=0) COUNT("OBJECT_NAME")[22]
   2 - "OBJECT_NAME"[VARCHAR2,128]
   3 - "TESTTAB".ROWID[ROWID,10]
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 

　　此时自适应游标共享还没起作用

select IS_BIND_SENSITIVE,IS_BIND_AWARE,IS_SHAREABLE from v$sql where sql_id='85man3fnzwka5'

       IS_BIND_SENSITIVE    IS_BIND_AWARE    IS_SHAREABLE
       N                    N                Y

列上收集直方图信息

exec dbms_stats.gather_table_stats(ownname => 'WXC',tabname => 'TESTTAB',method_opt => 'for all indexed columns size auto');

SQL> exec :v_own:='SYS';
PL/SQL procedure successfully completed
v_own
---------
SYS

SQL> select count(object_name) from testtab where owner=:v_own;
COUNT(OBJECT_NAME)
------------------
           2021120
v_own
---------
SYS

　　再次执行，由于SYS用户占表中半数以上，所以执行计划应该选择走全表扫描。

SQL_ID  85man3fnzwka5, child number 1
-------------------------------------
select count(object_name) from testtab where owner=:v_own
 
Plan hash value: 269898743
 
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |       | 18664 (100)|          |      1 |00:00:00.46 |   68720 |
|   1 |  SORT AGGREGATE    |         |      1 |      1 |    72 |            |          |      1 |00:00:00.46 |   68720 |
|*  2 |   TABLE ACCESS FULL| TESTTAB |      1 |   2009K|   137M| 18664   (1)| 00:03:44 |   2021K|00:00:00.36 |   68720 |
------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1
   2 - SEL$1 / TESTTAB@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "TESTTAB"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Peeked Binds (identified by position):
--------------------------------------
 
   1 - (VARCHAR2(30), CSID=178): 'SYS'
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("OWNER"=:V_OWN)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=0) COUNT("OBJECT_NAME")[22]
   2 - "OBJECT_NAME"[VARCHAR2,128]
 

　　自适应游标共享已经生效如下

select IS_BIND_SENSITIVE,IS_BIND_AWARE,IS_SHAREABLE from v$sql where sql_id='85man3fnzwka5'

SQL_TEXT	                                       IS_BIND_SENSITIVE	IS_BIND_AWARE	IS_SHAREABLE
select count(object_name) from testtab where owner=:v_own 	N	N	Y
select count(object_name) from testtab where owner=:v_own 	Y	N	N
select count(object_name) from testtab where owner=:v_own 	Y	Y	Y

　　继续测试，使绑定变量值为‘WXC’继续执行，由于wxc只在表中有几行数据，所以走索引扫描是合适的，执行计划如下 

SQL_ID  85man3fnzwka5, child number 2
-------------------------------------
select count(object_name) from testtab where owner=:v_own
 
Plan hash value: 1556530801
 
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |      1 |        |       |    20 (100)|          |      1 |00:00:00.01 |      67 |
|   1 |  SORT AGGREGATE              |          |      1 |      1 |    72 |            |          |      1 |00:00:00.01 |      67 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TESTTAB  |      1 |    446 | 32112 |    20   (0)| 00:00:01 |     64 |00:00:00.01 |      67 |
|*  3 |    INDEX RANGE SCAN          | IND_TEST |      1 |    450 |       |     4   (0)| 00:00:01 |     64 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1
   2 - SEL$1 / TESTTAB@SEL$1
   3 - SEL$1 / TESTTAB@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "TESTTAB"@"SEL$1" ("TESTTAB"."OWNER"))
      END_OUTLINE_DATA
  */
 
Peeked Binds (identified by position):
--------------------------------------
 
   1 - (VARCHAR2(30), CSID=178): 'WXC'
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("OWNER"=:V_OWN)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=0) COUNT("OBJECT_NAME")[22]
   2 - "OBJECT_NAME"[VARCHAR2,128]
   3 - "TESTTAB".ROWID[ROWID,10]
 

可见自适应游标共享确实能根据绑定变量的具体值选择合适的执行计划。

检查如下三个视图，相关信息如下。　　

SELECT *  FROM v$sql_cs_histogram where sql_id='85man3fnzwka5'

ADDRESS	HASH_VALUE	SQL_ID	CHILD_NUMBER	BUCKET_ID	COUNT
00000000A84CDFE0	2852014405	85man3fnzwka5	2	0	6
00000000A84CDFE0	2852014405	85man3fnzwka5	2	1	0
00000000A84CDFE0	2852014405	85man3fnzwka5	2	2	0
00000000A84CDFE0	2852014405	85man3fnzwka5	1	0	2
00000000A84CDFE0	2852014405	85man3fnzwka5	1	1	0
00000000A84CDFE0	2852014405	85man3fnzwka5	1	2	6

　　SELECT  * from  v$sql_cs_selectivity  where sql_id='85man3fnzwka5'

   	ADDRESS	HASH_VALUE	        SQL_ID	      CHILD_NUMBER	PREDICATE	RANGE_ID	LOW	        HIGH
	00000000A84CDFE0		85man3fnzwka5	2	         =V_OWN   	0	        0.000083	0.000102

　　

SELECT * from v$sql_cs_statistics where sql_id = '85man3fnzwka5'

   	ADDRESS	HASH_VALUE	SQL_ID	CHILD_NUMBER	BIND_SET_HASH_VALUE	PEEKED	EXECUTIONS	ROWS_PROCESSED	BUFFER_GETS	CPU_TIME
1	00000000A84CDFE0	2852014405	85man3fnzwka5	2	1699580835	Y	1	258	67	0
2	00000000A84CDFE0	2852014405	85man3fnzwka5	1	3596483694	Y	1	4042242	68720	0

　绑定变量捕获信息如下

SELECT * from v$sql_bind_capture where sql_id = '85man3fnzwka5'

   	ADDRESS	HASH_VALUE	SQL_ID	CHILD_ADDRESS	CHILD_NUMBER	NAME	POSITION	DUP_POSITION	DATATYPE	DATATYPE_STRING	CHARACTER_SID	PRECISION	SCALE	MAX_LENGTH	WAS_CAPTURED	LAST_CAPTURED	VALUE_STRING
1	00000000A84CDFE0	2852014405	85man3fnzwka5	000000009DA0D950	2	:V_OWN	1		1	VARCHAR2(4000)	178			4000	YES	2016/10/9 7:14:02	WXC
2	00000000A84CDFE0	2852014405	85man3fnzwka5	000000009DA120B8	1	:V_OWN	1		1	VARCHAR2(4000)	178			4000	YES	2016/10/9 7:06:52	SYS
3	00000000A84CDFE0	2852014405	85man3fnzwka5	00000000A84CDB80	0	:V_OWN	1		1	VARCHAR2(4000)	178			4000	YES	2016/10/9 6:55:05	SYS