问题描述
11g为什么要推出自适应游标？并用示例验证该特性
专家解答
绑定变量窥视
在讲自适应游标之前，我们先来了解一下绑定变量的窥视这个特性，窥视是发生在硬解析的时候，Oracle会根据统计信息计算第一次传入的绑定变量的具
体值的选择率、返回的行数等信息并生成合适的执行计划，后续不管绑定变量传入什么值，都会沿用第一次的执行计划。
举个例子：select * from tab where id=:p1;这条sql，id列上有索引，tab表中id列数据分布如下：
select id,count(*) from tab group by id; id count(*) -- -------- 1 100000 2 1
如果第一次硬解析时候传入的绑定变量p1=1，那么优化器就会选择全表扫描的执行计划，后续即使p1传入2，优化器仍然会沿用之前的全表扫描的执行计划而不会去重新生成一个索引范围扫描的执行计划。
绑定变量窥视的副作用就在于使用了绑定变量的SQL只会沿用之前硬解析时候生成的执行计划，而不管这种执行计划对于后续的绑定变量值是否是合适的。
自适应游标共享
10g以后，Oracle会自动搜集直方图统计信息，这意味着Oracle会更精准的知道目标列实际的数据分布，因此绑定变量的副作用会更加明显。为了解决这个问题，Oracle 11g引入了一个新特性--自适应游标共享：自适应游标可以在开启窥视的前提下，让目标SQL在多个可能的执行计划间'自适应'的选择，而不会只是沿用硬解析生成的执行计划，也就是说，Oracle在它认为目标SQL执行计划可能发生变化时，触发该SQL重新硬解析。Oracle判断执行计划是否可能会发生改变主要是根据SQL执行时候对应的SQL执行花费的逻辑读、CPU时间、对应结果集的行数等变化，以及绑定变量传入值的可选择率来综合判断是否需要触发目标SQL进行硬解析。
下面先来介绍一下跟自适应游标相关的一些概念：
Bind Sensitive：指Oracle觉得目标SQL的执行计划可能会随着传入的绑定变量值不同而变化
被标记为绑定变量敏感。需要满足以下几个条件：
[X] 启用了窥视
[X] SQL使用了绑定变量
[X] SQL使用的是不安全谓词
Bind Aware：指Oracle已经确定了目标SQL的执行计划会随着绑定变量传入值不同而变化
子游标被标记为Bind Aware需要满足以下条件：
[X] 该SQL对应的子游标已经被标记为Bind Sensitive
[X] SQL在接下来连续两次执行时，所对应的逻辑读、CPU时间、对应结果集的行数与之前硬解析时候的统计信息存在较大差异
介绍完以上两个概念，下面我们来整理一下自适应游标共享的整个过程：
1、目标SQL第一次执行时使用硬解析
同时会根据是否有绑定变量、绑定变量的列是否有直方图、where条件是等值还是范围、cursor_sharing的值判断子游标是否Bind Sensitive
如果被标记为Bind Sensitive的子游标，Oracle会把SQL执行时候对饮的逻辑读、CPU时间、返回的结果集行数等信息额外的存在子游标中
2、第二次执行，使用软解析，重用第一次的执行计划
3、第三次执行时，如果子游标已经被标记为Bind Sensitive，同时第二次和第三次执行时所记录的逻辑读、CPU时间、返回的行数等信息和第一次硬解
析时候所记录的信息相差较大，则第三次执行会使用硬解析，并且会重新生成一个子游标，挂在父游标下面，并把这个子游标标记为Bind Aware。
4、对于被标记为Bind Aware的子游标所对应的SQL来说，当再次执行时，Oracle会根据当前传入的绑定变量所对应谓词的可选择率落在哪个范围内来
决定是使用硬解析还是软解析或者软软解析
以下是测试过程，测试版本12.1.0.2.0：
创建测试表
sys@TEST>create table bindadaptive as select * from dba_objects; Table created.
创建object_type列的索引
sys@TEST>create index IDX_bindadaptive_type on bindadaptive(object_type); Index created.
更新列object_type倾斜值
sys@TEST>update bindadaptive set object_type='TABLE' where rownum<60001; 60000 rows updated. sys@TEST>update bindadaptive set object_type='CLUSTER' where rownum<2; 1 row updated. sys@TEST>select count(*) from bindadaptive where object_type='CLUSTER'; COUNT(*) ---------- 1 sys@TEST>select count(*) from bindadaptive where object_type='TABLE'; COUNT(*) ---------- 60831
为表BINDADAPTIVE搜集直方图统计信息
sys@TEST>exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'BINDADAPTIVE',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size auto',no_invalidate=>true); PL/SQL procedure successfully completed.
列OBJECT_TYPE上已经有频率直方图信息了
sys@TEST>select column_name,num_buckets,histogram from dba_tab_col_statistics where table_name='BINDADAPTIVE' and column_name='OBJECT_TYPE'; COLUMN_NAME NUM_BUCKETS HISTOGRAM ------------------------------ ----------- --------------------------------------------- OBJECT_TYPE 30 FREQUENCY sys@TEST>select OBJECT_TYPE,count(*) from BINDADAPTIVE group by OBJECT_TYPE order by 2; OBJECT_TYPE COUNT(*) --------------------------------------------------------------------- ---------- UNDEFINED 1 EVALUATION CONTEXT 1 UNIFIED AUDIT POLICY 1 CLUSTER 1 JAVA SOURCE 2 JOB 4 CONTEXT 5 INDEXTYPE 5 LOB PARTITION 18 XML SCHEMA 25 OPERATOR 31 LIBRARY 43 PROCEDURE 56 TYPE BODY 70 SEQUENCE 78 TABLE PARTITION 172 FUNCTION 228 INDEX PARTITION 235 JAVA DATA 310 LOB 339 PACKAGE BODY 510 TRIGGER 529 PACKAGE 543 VIEW 653 TYPE 744 JAVA RESOURCE 1014 INDEX 2128 JAVA CLASS 10781 SYNONYM 11880 TABLE 60831 30 rows selected.
在测试自适应游标之前，保证绑定变量窥视打开、cursor_sharing参数是exact默认值
sys@TEST>show parameter cursor NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ cursor_bind_capture_destination string memory+disk cursor_sharing string EXACT cursor_space_for_time boolean FALSE open_cursors integer 300 session_cached_cursors integer 50 sys@TEST>@yinhan Enter value for 1: _optim_peek_user_binds old 2: where a.indx=b.indx and a.ksppinm = '&1' new 2: where a.indx=b.indx and a.ksppinm = '_optim_peek_user_binds' KSPPINM KSPPSTVL --------------------------------------------- -------- _optim_peek_user_binds FALSE sys@TEST>alter session set "_optim_peek_user_binds"=true; Session altered.
清空缓存
sys@TEST>alter system flush shared_pool; System altered.
第一次执行：
绑定变量传入值是非常少的object_type='CLUSTER'
sys@TEST>var x varchar2(30); sys@TEST>exec :x:='CLUSTER'; PL/SQL procedure successfully completed. sys@TEST>select count(*) from BINDADAPTIVE where object_type=:x; COUNT(*) ---------- 1
此时可以看到VERSION_COUNT=1，说明发生了硬解析
sys@TEST>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from BINDADAPTIVE where object_type=:x%'; SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS ----------------------------------------------------------------- -------------------- ------------- ---------- select count(*) from BINDADAPTIVE where object_type=:x 7u09hu1c0a1h7 1 1
由于绑定变量列上有直方图，Oracle认为执行计划可能会随着传入的绑定变量值不同而发生变化 所以该子游标被定义为BIND_SENSITIVE和SHAREABLE的
第一次执行的逻辑读是56
sys@TEST>select child_number,executions,buffer_gets,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value from v$sql where sql_id='7u09hu1c0a1h7'; CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE PLAN_HASH_VALUE ------------ ---------- ----------- -------------------- -------------------- -------------------- --------------- 0 1 56 Y N Y 1181777790
查看SQL绑定变量传入值，可以看到在窥视部分有【:X (VARCHAR2(30), CSID=852): 'CLUSTER'】，说明发生硬解析的时候，Oracle是根据object_type=cluster生成执行计划的
sys@TEST>select * from table(dbms_xplan.display_cursor('7u09hu1c0a1h7',0,'advanced'));
select count(*) from BINDADAPTIVE where object_type=:x
Plan hash value: 1181777790
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | INDEX RANGE SCAN| IDX_BINDADAPTIVE_TYPE | 1 | 8 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :X (VARCHAR2(30), CSID=852): 'CLUSTER'第二次执行：
改变绑定变量值为倾斜值非常多的object_type='TABLE'
sys@TEST>exec :x:='TABLE'; PL/SQL procedure successfully completed. sys@TEST>select count(*) from BINDADAPTIVE where object_type=:x; COUNT(*) ---------- 60831
可以看到VERSION_COUNT=1，而EXECUTIONS=2，说明这次执行使用的是软解析，可以看到在绑定变量窥视部分仍然使用的是【:X (VARCHAR2(30), CSID=852): 'CLUSTER'】
sys@TEST>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from BINDADAPTIVE where object_type=:x%'; SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS ----------------------------------------------------------------- -------------------- ------------- ---------- select count(*) from BINDADAPTIVE where object_type=:x 7u09hu1c0a1h7 1 2
第二次执行的逻辑读是308，和第一次的56相差较大，因为返回的行数差距很大
但是仍然不是BIND_AWARE的子游标，因为BIND_AWARE前提条件是硬解析以后连续两次传入同样的绑定变量相差较大，这里object_type='TABLE'只执行了一次
sys@TEST>select child_number,executions,buffer_gets,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value from v$sql where sql_id='7u09hu1c0a1h7'; CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE PLAN_HASH_VALUE ------------ ---------- ----------- -------------------- -------------------- -------------------- --------------- 0 2 308 Y N Y 1181777790
因为此时子游标还不是BIND_AWARE的，所以在v$SQL_CS_STATISTICS里面是没有相应的列的选择率的记录的
sys@TEST>select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_id='7u09hu1c0a1h7';
no rows selected
sys@TEST>select * from table(dbms_xplan.display_cursor('7u09hu1c0a1h7',0,'advanced'));
SQL_ID 7u09hu1c0a1h7, child number 0
-------------------------------------
select count(*) from BINDADAPTIVE where object_type=:x
Plan hash value: 1181777790
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | INDEX RANGE SCAN| IDX_BINDADAPTIVE_TYPE | 1 | 8 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :X (VARCHAR2(30), CSID=852): 'CLUSTER'第三次执行：
绑定变量值是倾斜值非常多的object_type='TABLE'
sys@TEST>exec :x:='TABLE'; sys@TEST>select count(*) from BINDADAPTIVE where object_type=:x; COUNT(*) ---------- 60831
这里可以看到VERSION_COUNT=2，说明第三次执行的时候执行的是硬解析
sys@TEST>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from BINDADAPTIVE where object_type=:x%'; SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS ----------------------------------------------------------------- -------------------- ------------- ---------- select count(*) from BINDADAPTIVE where object_type=:x 7u09hu1c0a1h7 2 3
查看该SQL多了一个child_number=1的子游标，而且该子游标还是BIND_AWARE的和SHAREABLE的
此时注意看child_number=0的子游标变为了不可SHAREABLE，说明child_number=0的子游标不可被共享了
sys@TEST>select child_number,executions,buffer_gets,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value from v$sql where sql_id='7u09hu1c0a1h7'; CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE PLAN_HASH_VALUE ------------ ---------- ----------- -------------------- -------------------- -------------------- --------------- 0 2 308 Y N N 1181777790 1 1 510 Y Y Y 663288296
而且当绑定变量传入的具体值是object_type='TABLE'时，该列谓词的选择率是【0.600056-0.733402】
sys@TEST>select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_id='7u09hu1c0a1h7'; CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH ------------ ---------- ---------- ------------------------------ ------------------------------ 1 =X 0 0.600056 0.733402
可以从此时执行计划的绑定变量窥视部分看到【:X (VARCHAR2(30), CSID=852): 'TABLE'】，说明发生了硬解析
sys@TEST>select * from table(dbms_xplan.display_cursor('7u09hu1c0a1h7',1,'advanced'));
SQL_ID 7u09hu1c0a1h7, child number 1
-------------------------------------
select count(*) from BINDADAPTIVE where object_type=:x
Plan hash value: 663288296
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 97 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_BINDADAPTIVE_TYPE | 60831 | 475K| 97 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :X (VARCHAR2(30), CSID=852): 'TABLE'第四次执行：
传入object_type='CLUSTER'时
sys@TEST>exec :x:='CLUSTER'; PL/SQL procedure successfully completed. sys@TEST>select count(*) from BINDADAPTIVE where object_type=:x; COUNT(*) ---------- 1
VERSION_COUNT=3，该次执行发生了硬解析
sys@TEST>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from BINDADAPTIVE where object_type=:x%'; SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS ------------------------------------------------------------ -------------------- ------------- ---------- select count(*) from BINDADAPTIVE where object_type=:x 7u09hu1c0a1h7 3 4
重新硬解析出一个child_nunber=2的子游标，该子游标是bind_aware和shareable的，也就是说，并没有使用上面child=0的子游标
sys@TEST>select child_number,executions,buffer_gets,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value from v$sql where sql_id='7u09hu1c0a1h7'; CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE PLAN_HASH_VALUE ------------ ---------- ----------- -------------------- ------------------ ---------------- --------------- 0 2 308 Y N N 1181777790 1 1 510 Y Y Y 663288296 2 1 3 Y Y Y 1181777790
此时查看传入的object_type='CLUSTER'谓词的可选择率是【0.000010-0.000012】，也就是说和之前传入的object_type='TABLE'的可选择率不在一个区间内，因此发生了硬解析
sys@TEST>select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_id='7u09hu1c0a1h7'; CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH ------------ ---------- ---------- ------------------------------ ------------------------------ 2 =X 0 0.000010 0.000012 1 =X 0 0.600056 0.733402
第五次执行：
如果此时我修改了优化器的某个参数，alter session set nls_sort = 'SCHINESE_RADICAL_M';
sys@TEST>alter session set nls_sort = 'SCHINESE_RADICAL_M'; Session altered. sys@TEST>exec :x:='CLUSTER'; sys@TEST>select count(*) from BINDADAPTIVE where object_type=:x; COUNT(*) ---------- 1
VERSION_COUNT=4，即使传入的绑定变量值和前一次传入的值一样，也发生了硬解析
sys@TEST>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from BINDADAPTIVE where object_type=:x%'; SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS ------------------------------------------------------------ -------------------- ------------- ---------- select count(*) from BINDADAPTIVE where object_type=:x 7u09hu1c0a1h7 4 5
多出了一个child_number=3的子游标
sys@TEST>select child_number,executions,buffer_gets,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value from v$sql where sql_id='7u09hu1c0a1h7'; CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE PLAN_HASH_VALUE ------------ ---------- ----------- -------------------- ------------------ ---------------- --------------- 0 2 308 Y N N 1181777790 1 1 510 Y Y Y 663288296 2 1 3 Y Y Y 1181777790 3 1 3 Y N Y 1181777790
即使他的谓词可选择率和上一个传入的绑定变量值落在同一个区间，仍然发生了硬解析
sys@TEST>select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_id='7u09hu1c0a1h7'; CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH ------------ ---------- ---------- ------------------------------ ------------------------------ 2 =X 0 0.000010 0.000012 1 =X 0 0.600056 0.733402
第六次执行：
再次修改优化器某个参数 alter session set optimizer_mode = first_rows;
sys@TEST>alter session set optimizer_mode = first_rows; Session altered. sys@TEST>exec :x:='CLUSTER'; PL/SQL procedure successfully completed. sys@TEST>select count(*) from BINDADAPTIVE where object_type=:x; COUNT(*) ---------- 1
VERSION_COUNT=5，即使传入的绑定变量值和前一次传入的值一样，仍然再次发生了硬解析
sys@TEST>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from BINDADAPTIVE where object_type=:x%'; SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS ------------------------------------------------------------ -------------------- ------------- ---------- select count(*) from BINDADAPTIVE where object_type=:x 7u09hu1c0a1h7 5 6
多出了一个chile_number=4的子游标
sys@TEST>select child_number,executions,buffer_gets,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value from v$sql where sql_id='7u09hu1c0a1h7'; CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE PLAN_HASH_VALUE ------------ ---------- ----------- -------------------- ------------------ ---------------- --------------- 0 2 308 Y N N 1181777790 1 1 510 Y Y Y 663288296 2 1 3 Y Y Y 1181777790 3 1 3 Y N Y 1181777790 4 1 3 Y N Y 1181777790
即使他的谓词可选择率和上一个传入的绑定变量值落在同一个区间，仍然发生了硬解析
sys@TEST>select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_id='7u09hu1c0a1h7'; CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH ------------ ---------- ---------- ------------------------------ ------------------------------ 2 =X 0 0.000010 0.000012 1 =X 0 0.600056 0.733402
第七次执行：
修改了绑定变量定义的长度不是同一级别范围内的，之前是var x varchar2(30);
sys@TEST>var x varchar2(3000); sys@TEST>exec :x:='CLUSTER'; sys@TEST>select count(*) from BINDADAPTIVE where object_type=:x; COUNT(*) ---------- 1
VERSION_COUNT=6，再次发生了硬解析
sys@TEST>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from BINDADAPTIVE where object_type=:x%'; SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS ------------------------------------------------------------ -------------------- ------------- ---------- select count(*) from BINDADAPTIVE where object_type=:x 7u09hu1c0a1h7 6 7
多了一个child_number=5的子游标，
sys@TEST>select child_number,executions,buffer_gets,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value from v$sql where sql_id='7u09hu1c0a1h7'; CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE PLAN_HASH_VALUE ------------ ---------- ----------- -------------------- ------------------ ---------------- --------------- 0 2 308 Y N N 1181777790 1 1 510 Y Y Y 663288296 2 1 3 Y Y Y 1181777790 3 1 3 Y N Y 1181777790 4 1 6 Y N Y 1181777790 5 1 3 Y N Y 1181777790 6 rows selected.
即使他的谓词可选择率和上一个传入的绑定变量值落在同一个区间，仍然发生了硬解析
sys@TEST>select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_id='7u09hu1c0a1h7'; CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH ------------ ---------- ---------- ------------------------------ ------------------------------ 2 =X 0 0.000010 0.000012 1 =X 0 0.600056 0.733402
Oracle数据库中执行的SQL，很多时候会因为种种原因产生多个不同的执行版本，一个游标的版本过多很容易引起数据库的性能问题，甚至故障，自适应子游标的缺点是：
如果传入的绑定变量的谓词可选择率并没有落在之前一个执行计划的传入的谓词的可选择区间内;
如果传入的绑定变量长度不在同一个级别内;
如果优化器参数发生了变化;
如果表上的统计信息发生了改变;
......
Oracle就会重新生成一个新的执行计划，不会共享之前的执行计划，这样会造成子游标version count过多，扫描子游标链表的时候有可能会造成library cache mutex x等待。
在版本10g中引入了Cursor Obsolescence游标废弃特性，子游标总数阀值达到1024父游标就会被重新硬解析。
但是这个阀值在11g中被移除了，这导致出现一个父游标下大量child cursor即high version count的发生，并引发了11.2.0.3之前的cursor sharing性能问题，主要症状是版本11.2.0.1和11.2.0.2上出现大量的Cursor: Mutex S 和 library cache lock等待事件。
11.2.0.3中默认就有”_cursor_obsolete_threshold”了，默认值为100。
11.2.0.4之后默认值是1024
