绑定变量之自适应游标共享(Adaptive Cursor Sharing)
Oracle通过引入绑定变量解决了硬解析过多的问题，从一定程度上降低了对资源(共享池)的争用。对于使用绑定变量的SQL语句，在第一次硬解析的时候会通过绑定窥探技术（Bind Peeking）确认绑定变量的输入值，然后根据该值来确定执行计划，并将执行计划保存在共享池，以便后续共享。此方案一般情况运行良好，但对于一些特殊情况比如某列值分布十分倾斜，会造成比较严重的性能问题，因为SQL的最佳执行计划已经改变了，再采用第一次硬解析的执行计划明显是不明智的或者错误的。
为了弥补Bind Peeking的缺陷，从11g开始Oracle引入了自适应游标共享机制（ACS），该技术将绑定变量的执行计划变为一个基于统计量分析的自适应过程，根据绑定变量的具体输入值动态的生成执行计划。
自适应游标整体执行流程如下：
1.当SQL第一次被执行时，Oracle会用硬解析，同时Oracle会根据情况来判断是否将SQL所对应的Child Cursor标记为Bind Sensitive.标记为Sensitive说明后续该SQL的执行计划可能会改变。
什么情况下,SQL所对应的Child Cursor会比较为Bind Sensitive呢？满足如下条件
1)启用了绑定变量窥探
2)该SQL使用了绑定变量
3)该SQL使用的是不安全的谓词条件(范围查询、目标列上有直方图统计信息的等值查询等)
2.当SQL第二次被执行时，Oracle会用软解析，重用该SQL第一次执行时所产生的Child Cursor中存储的解析树和执行计划。
3.当SQL第三次被执行，若该SQL所产生的Child Cursor已经被标记为Bind Sensitive，同时Oracle在第二次和第三次执行该SQL记录的runtime统计信息和第一次硬解析所记录的runtime统计信息存在较大差异，则该SQL在第三次被执行会使用硬解析，Oracle此时会产生一个新的Child Cursor，并且把这个新的Child Cursor标记为Bind Aware.
什么叫"Bind Aware"?通俗来讲，Bind Aware指Oracle已经非常明确的确定这个包含绑定变量的SQL，其执行计划会随着绑定变量输入值的变化而变化。
4. 对于标记为Bind Aware的Child Cursor所对应的SQL，当该SQL再次被执行时，Oracle会根据当前传入的绑定变量值所对应的谓词条件的选择率，来决定此时是硬解析还是软解析(软软解析)。
具体原则为当传入的绑定变量值所在的谓词条件的选择率处于v$SQL_CS_STATISTICS中记录的选择率范围内，此时Oracle会使用软解析或者软软解析，否则会硬解析。如果为硬解析，则Oracle会重新生成一个Child Cursor，另外还会把原有的Child Cursor标记为非共享，以便在共享池资源紧张的时候，第一时间将非共享游标刷出并覆盖。另外如果此次硬解析所产生的执行计划和原有Child Cursor中存储的执行计划相同，Oracle会将原Child Cursor同新的Child Cursor进行合并（主要指选择率的合并）。
注：上面内容为了便于理解简化了部分细节，其实只要后续runtime统计信息差异较大的SQL执行次数大于之前的SQL执行次数，下一次该SQL的执行就会使用硬解析。
下面通过具体的实验进行说明：
好了，上面已完成测试环境的准备工作。下面进行ACS的测试，首先先看一下未使用绑定变量的执行计划情况：
为了弥补Bind Peeking的缺陷，从11g开始Oracle引入了自适应游标共享机制（ACS），该技术将绑定变量的执行计划变为一个基于统计量分析的自适应过程，根据绑定变量的具体输入值动态的生成执行计划。
自适应游标整体执行流程如下：
1.当SQL第一次被执行时，Oracle会用硬解析，同时Oracle会根据情况来判断是否将SQL所对应的Child Cursor标记为Bind Sensitive.标记为Sensitive说明后续该SQL的执行计划可能会改变。
什么情况下,SQL所对应的Child Cursor会比较为Bind Sensitive呢？满足如下条件
1)启用了绑定变量窥探
2)该SQL使用了绑定变量
3)该SQL使用的是不安全的谓词条件(范围查询、目标列上有直方图统计信息的等值查询等)
2.当SQL第二次被执行时，Oracle会用软解析，重用该SQL第一次执行时所产生的Child Cursor中存储的解析树和执行计划。
3.当SQL第三次被执行，若该SQL所产生的Child Cursor已经被标记为Bind Sensitive，同时Oracle在第二次和第三次执行该SQL记录的runtime统计信息和第一次硬解析所记录的runtime统计信息存在较大差异，则该SQL在第三次被执行会使用硬解析，Oracle此时会产生一个新的Child Cursor，并且把这个新的Child Cursor标记为Bind Aware.
什么叫"Bind Aware"?通俗来讲，Bind Aware指Oracle已经非常明确的确定这个包含绑定变量的SQL，其执行计划会随着绑定变量输入值的变化而变化。
4. 对于标记为Bind Aware的Child Cursor所对应的SQL，当该SQL再次被执行时，Oracle会根据当前传入的绑定变量值所对应的谓词条件的选择率，来决定此时是硬解析还是软解析(软软解析)。
具体原则为当传入的绑定变量值所在的谓词条件的选择率处于v$SQL_CS_STATISTICS中记录的选择率范围内，此时Oracle会使用软解析或者软软解析，否则会硬解析。如果为硬解析，则Oracle会重新生成一个Child Cursor，另外还会把原有的Child Cursor标记为非共享，以便在共享池资源紧张的时候，第一时间将非共享游标刷出并覆盖。另外如果此次硬解析所产生的执行计划和原有Child Cursor中存储的执行计划相同，Oracle会将原Child Cursor同新的Child Cursor进行合并（主要指选择率的合并）。
注：上面内容为了便于理解简化了部分细节，其实只要后续runtime统计信息差异较大的SQL执行次数大于之前的SQL执行次数，下一次该SQL的执行就会使用硬解析。
下面通过具体的实验进行说明：
点击(此处)折叠或打开
- //数据库版本信息
- SELECT * FROM v$version;
-
BANNER
-
--------------------------------------------------------------------------------
-
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
-
PL/SQL Release 11.2.0.3.0 - Production
-
CORE 11.2.0.3.0 Production
-
TNS for Linux: Version 11.2.0.3.0 - Production
- NLSRTL Version 11.2.0.3.0 - Production
//创建表
create table tab_acs(id int,value int);
//插入测试数据
begin
for i in 1 .. 20000
loop
execute immediate 'insert into tab_acs values(1,'||i||')';
end loop;
end;
/
begin
for i in 1 .. 10
loop
execute immediate 'insert into tab_acs values(2,:1)' using i;
end loop;
end;
/
commit;
//确认数据的分布情况
select id,count(*) from tab_acs group by id;
ID COUNT(*)
---------- ----------
1 20000
2 10
create table tab_acs(id int,value int);
//插入测试数据
begin
for i in 1 .. 20000
loop
execute immediate 'insert into tab_acs values(1,'||i||')';
end loop;
end;
/
begin
for i in 1 .. 10
loop
execute immediate 'insert into tab_acs values(2,:1)' using i;
end loop;
end;
/
commit;
//确认数据的分布情况
select id,count(*) from tab_acs group by id;
ID COUNT(*)
---------- ----------
1 20000
2 10
//创建索引
create index idx_tab_acs on tab_acs(id);
//收集统计信息
exec dbms_stats.gather_table_stats(user,'TAB_ACS',cascade=>true,METHOD_OPT=>'FOR ALL COLUMNS');
//查看统计信息情况
SQL> select table_name,column_name,histogram from dba_tab_col_statistics where table_name='TAB_ACS';
TABLE_NAME COLUMN_NAME HISTOGRAM
------------------------------ ------------------------------ ---------------
TAB_ACS VALUE HEIGHT BALANCED
TAB_ACS ID FREQUENCY
create index idx_tab_acs on tab_acs(id);
//收集统计信息
exec dbms_stats.gather_table_stats(user,'TAB_ACS',cascade=>true,METHOD_OPT=>'FOR ALL COLUMNS');
//查看统计信息情况
SQL> select table_name,column_name,histogram from dba_tab_col_statistics where table_name='TAB_ACS';
TABLE_NAME COLUMN_NAME HISTOGRAM
------------------------------ ------------------------------ ---------------
TAB_ACS VALUE HEIGHT BALANCED
TAB_ACS ID FREQUENCY
点击(此处)折叠或打开
-
select count(value) from tab_acs where id=1;
-
COUNT(VALUE)
-
------------
- 20000
-
-
select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(value)%';
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
------------------------------------------------------------ ------------- ------------- ----------
select count(value) from tab_acs where id=1 f4jwtn3360ppk 1 1
下面看一下使用绑定变量的执行计划：
select * from table(dbms_xplan.display_cursor('f4jwtn3360ppk',0,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID f4jwtn3360ppk, child number 0
-------------------------------------
select count(value) from tab_acs where id=1
Plan hash value: 3684903434
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 11 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | TABLE ACCESS FULL| TAB_ACS | 20000 | 136K| 11 (0)| 00:00:01 |
------------------------------------------------------------------------------
select count(value) from tab_acs where id=2;
COUNT(VALUE)
------------
10
SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(value)%';
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
------------------------------------------------------------ ------------- ------------- ----------
select count(value) from tab_acs where id=1 f4jwtn3360ppk 1 1
select count(value) from tab_acs where id=2 9vx4syymyzkb7 1 1
select * from table(dbms_xplan.display_cursor('9vx4syymyzkb7',0,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 9vx4syymyzkb7, child number 0
-------------------------------------
select count(value) from tab_acs where id=2
Plan hash value: 3029888215
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TAB_ACS | 10 | 70 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_TAB_ACS | 10 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
可以看到谓词条件为1时走全表扫描，谓词条件为2的情况下走索引范围扫描。
-
从上述对ACS自适应游标共享的整个测试过程来看，自适应游标在一定程度上缓解了绑定变量窥探所带来的副作用，可以让SQL在其可能的多个执行计划之间自适应的做出选择，而不再像
点击(此处)折叠或打开
- var x number;
- exec :x:=1;
-
-
select count(value) from tab_acs where id=:x;
COUNT(VALUE)
------------
20000 -
- select sql_text,sql_id,child_number,executions,buffer_gets,is_bind_sensitive as "BS",is_bind_aware as "BA",is_shareable as "BSH",plan_hash_value from v$sql where sql_text
-
like 'select count(value)%';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS B B B PLAN_HASH_VALUE
------------------------------------------------------------ ------------- ------------ ---------- ----------- - - - ---------------
select count(value) from tab_acs where id=:x 5gy2wu883n8ac 0 1 96 Y N Y 3684903434
exec :x:=2;
select count(value) from tab_acs where id=:x;
COUNT(VALUE)
------------
10
//根据前面的结论，本次肯定为软解析select sql_text,sql_id,child_number,executions,buffer_gets,is_bind_sensitive as "BS",is_bind_aware as "BA",is_shareable as "BSH",plan_hash_value from v$sql where sql_text
like 'select count(value)%';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS B B B PLAN_HASH_VALUE
------------------------------------------------------------ ------------- ------------ ---------- ----------- - - - ---------------
select count(value) from tab_acs where id=:x 5gy2wu883n8ac 0 2 133 Y N Y 3684903434
:x变量设置为2进行第二次查询
select count(value) from tab_acs where id=:x;
COUNT(VALUE)
------------
10
select sql_text,sql_id,child_number,executions,buffer_gets,is_bind_sensitive as "BS",is_bind_aware as "BA",is_shareable as "BSH",plan_hash_value from v$sql where sql_text
like 'select count(value)%';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS B B B PLAN_HASH_VALUE
------------------------------------------------------------ ------------- ------------ ---------- ----------- - - - ---------------
select count(value) from tab_acs where id=:x 5gy2wu883n8ac 0 2 133 Y N N 3684903434
select count(value) from tab_acs where id=:x 5gy2wu883n8ac 1 1 3 Y Y Y 3029888215
select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_id='5gy2wu883n8ac';
既然CHILD_NUMBER=1标记为了BIND_AWARE,那么v$sql_cs_selectivity肯定有相关数值
CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
------------ ---------------------------------------- ---------- ---------- ----------
1 =X 0 0.000427 0.000522
:x变量设置为3进行查询
exec :x:=3;
select count(value) from tab_acs where id=:x;
COUNT(VALUE)
------------
0
select sql_text,sql_id,child_number,executions,buffer_gets,is_bind_sensitive as "BS",is_bind_aware as "BA",is_shareable as "BSH",plan_hash_value from v$sql where sql_text
like 'select count(value)%';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS B B B PLAN_HASH_VALUE
------------------------------------------------------------ ------------- ------------ ---------- ----------- - - - ---------------
select count(value) from tab_acs where id=:x 5gy2wu883n8ac 0 2 133 Y N N 3684903434
select count(value) from tab_acs where id=:x 5gy2wu883n8ac 1 1 3 Y Y N 3029888215
select count(value) from tab_acs where id=:x 5gy2wu883n8ac 2 1 2 Y Y Y 3029888215
可以判断:x:=3肯定走的索引范围扫描，同:x:=2情况一样（从查询结果CHILD_NUMBER=1,2的PLAN_HASH_VALUE值相同也可以看出这一点），但由于选择率不在v$sql_cs_selectivity范围内，还会进行硬解析，因此产生
了CHILD_NUMBER=2
的CHILD CURSOR.
select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_id='5gy2wu883n8ac';
CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
------------ ---------------------------------------- ---------- ---------- ----------
2 =X 0 0.000225 0.000522
1 =X 0 0.000427 0.000522
可以看到LOW,HIGH的范围由[0.000427,0.000522]扩大到了[0.000225,0.000522].既然选择率已经扩大到了[0.000225,0.000522]，可以断定谓词条件修改为:x:=10，肯定会重用CHILD_NUMBER=2的执行计划，下面进行验证：
exec :x:=10;
select count(value) from tab_acs where id=:x;
COUNT(VALUE)
------------
0
select sql_text,sql_id,child_number,executions,buffer_gets,is_bind_sensitive as "BS",is_bind_aware as "BA",is_shareable as "BSH",plan_hash_value from v$sql where sql_text
like 'select count(value)%';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS B B B PLAN_HASH_VALUE
------------------------------------------------------------ ------------- ------------ ---------- ----------- - - - ---------------
select count(value) from tab_acs where id=:x 5gy2wu883n8ac 0 2 133 Y N N 3684903434
select count(value) from tab_acs where id=:x 5gy2wu883n8ac 1 1 3 Y Y N 3029888215
select count(value) from tab_acs where id=:x 5gy2wu883n8ac 2 2 4 Y Y Y 3029888215
因为谓词条件:x:=1对应的CHILD CURSOR对应的共享标志已经标记为N，那么如果现在在对:x:=1进行查询，因此其对应的选择率不在CHILD_NUMBER=2的范围内，因此肯定还会硬解析。
exec :x:=1;
select count(value) from tab_acs where id=:x;
COUNT(VALUE)
------------
20000
select sql_text,sql_id,child_number,executions,buffer_gets,is_bind_sensitive as "BS",is_bind_aware as "BA",is_shareable as "BSH",plan_hash_value from v$sql where sql_text like
'select count(value)%';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS B B B PLAN_HASH_VALUE
------------------------------------------------------------ ------------- ------------ ---------- ----------- - - - ---------------
select count(value) from tab_acs where id=:x 5gy2wu883n8ac 0 2 133 Y N N 3684903434
select count(value) from tab_acs where id=:x 5gy2wu883n8ac 1 1 3 Y Y N 3029888215
select count(value) from tab_acs where id=:x 5gy2wu883n8ac 2 2 4 Y Y Y 3029888215
select count(value) from tab_acs where id=:x 5gy2wu883n8ac 3 1 37 Y Y Y 3684903434
CHILD_NUMBER=3便是新生成的游标，下面看一下v$sql_cs_selectivity肯定也有相关变化。
select child_number,predicate,range_id,low,high from v$sql_cs_selectivity where sql_id='5gy2wu883n8ac';
CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
------------ ---------------------------------------- ---------- ---------- ----------
3 =X 0 0.899550 1.099450
2 =X 0 0.000225 0.000522
1 =X 0 0.000427 0.000522
经过以上语句的训练，共享池中生成了两条比较稳定的存储执行计划的CHILD CURSOR，分别为CHILD_NUMBER 2和3.下次绑定变量有新值输入，会根据选择率的范围决定走软解析或者硬解析。
-
之前那样必须得刻板得沿用SQL硬解析时所产生的解析树和执行计划。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29827284/viewspace-2138346/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
