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_STATISTICSsummarizes 包括优化器是否将某个游标标记为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
posted on 2016-12-13 22:37 wangxingc 阅读(2656) 评论(0) 编辑 收藏 举报
