|
五演示ACS（adaptiver cursor sharing)的效果
变量窥视bind peeking：当一条SQL语句在硬解析时如果有绑定变量，会查看这个变量值，有了变量值便于得出正确的执行计划，在后续软解析过程中将不再查看这个变量值了。
变量窥视目的：就是为了得出正确的执行计划，第一条sql的执行计划的准确性是至关重要的，后面反复执行即可
变量窥视发生时刻：只发生在SQL硬解析的时候，不发生在SQL软解析的时候
变量窥视适合场景：
1.SQL执行计划不会因为变量值的不同而产生不同的执行计划，即执行计划几乎不改变
2.有大量用户并发
3.大量的除谓词外几乎相同的SQL
4.适合于OLTP场景
变量窥视不适合场景：
1. SQL执行计划会因为变量值的不同而产生不同的执行计划，就是oracle会因为不同谓词导致选择不同执行计划
2.SQL语句批量加载批量检索
3.不适合OLAP场景，因为OLAP执行计划与数据量的关系非常紧密，因此不适合绑定变量也就谈不上变量窥视问题
4.SQL解析对系统性能影响很小，所以绑定没有意义
实验
LEO1@LEO1> drop table leo8 purge; 清空环境
Table dropped.
LEO1@LEO1> create table leo8 as select 1>Table created.
LEO1@LEO1> select> ID
------------------
1
1
1
1
1
1
1
1
1
我们添加了1个ID列，值全为1，为了后面好对比测试效果
LEO1@LEO1> update leo8 set>1 row updated.
LEO1@LEO1> select> ID
-------------------
2
1
1
1
LEO1@LEO1> create index leo8_idx on leo8(id); 在ID列上创建一个B-tree索引
Index created.
LEO1@LEO1> execute
dbms_stats.gather_table_stats(ownname=>'LEO1',tabname=>'LEO8',method_opt=>'for all indexed columns>PL/SQL procedure successfully completed.
对leo8做一个表分析，分析一下占用了多少个块，表上有多少条记录等统计信息，还做了直方图分析，了解数据分布情况，数据的倾斜度
小结：上面创建了leo8表，ID列只有2个数值，id=2只有一条记录，剩下的全是id=1，目的就是构造一个数据严重倾斜的表，用来放大执行计划的效果。
绑定变量
LEO1@LEO1> variable i number; 首先定义变量i
LEO1@LEO1> execute :i:=1; 变量赋值i=1
PL/SQL procedure successfully completed.
LEO1@LEO1>>
Session>
LEO1@LEO1> select * from leo8 var_i_1 where>……
LEO1@LEO1> execute :i:=2; 变量赋值i=2
PL/SQL procedure successfully completed.
LEO1@LEO1> select * from leo8 var_i_2 where>……
说明：为了在trace文件中好分辨2个sql语句，我给起了2个别名，var_i_1表示变量值等于1时的SQL语句，var_i_2表示变量值等于2时的SQL语句。
LEO1@LEO1> select sql_text,parse_calls,loads,executions from v$sql where sql_text like '%select count(*) from leo8%' order by 1;
SQL_TEXT PARSE_CALLS LOADS EXECUTIONS
-------------------------------------------------- ----------- -------------------------- ----------- ---------- ------------------
select * from leo8 var_i_1 where>
select * from leo8 var_i_2 where>由于表的别名不同，oracle认为这是2条不相同的SQL，因此都做了1次硬解析（hard parse），都执行了1次，oracle将会在硬解析时对绑定变量进行变量窥视bind peeking
LEO1@LEO1> select * from leo8 var_i_1 where>…..
我们第二次执行这条SQL，由于刚刚硬解析完，所以这次只做了1次软解析，而软解析时不会发生变量窥视。但我们要注意的是，此时变量i=2，不是之前的1了，从结果集上也可看出，按理说这条SQL应该走索引执行计划，i=2在列中只有1条，走索引是效率最高的，我们将会在后面trace文件中看看是不是这样
LEO1@LEO1> execute :i:=1; 重新赋予变量i=1
PL/SQL procedure successfully completed.
LEO1@LEO1> select * from leo8 var_i_2 where>…..
我们重新赋予变量i=1，也是第二次执行这条SQL，它也刚硬解析完，这次也只是做软解析，不再进行变量窥视，由于i=1占了全表99.9%，应该走全表扫描，我们将会在后面trace文件中看看是不是这样
LEO1@LEO1>>
Session>LEO1@LEO1> select value from v$diag_info where name='Default Trace File'; 写入的trace文件
VALUE
-------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/leo1/LEO1/trace/LEO1_ora_2494.trc
[oracle@leonarding1 trace]$ tkprof LEO1_ora_2494.trc bind_peeking.txt sys=no 过滤trace文件
TKPROF:>Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
看一下trace文件内容
[oracle@leonarding1 trace]$ vim bind_peeking.txt
SQL>Plan Hash: 948063081
select * from leo8 var_i_1 where>Misses in library cache during parse: 1 表示这是一次硬解析
Misses in library cache during execute: 1 表示执行了一次
Optimizer mode: ALL_ROWS 优化器模式：ALL_ROWS
Rows Row Source Operation
------- ---------------------------------------------------
71959 TABLE ACCESS FULL
在进行硬解析时发生了bind peeking，因此oracle知道谓词i=1，在和表分析的结果进行对比，知道i=1的记录占了全表的99.9%，所以采用全表扫描更优一些。
SQL>Plan Hash: 948063081
select * from leo8 var_i_2 where>Misses in library cache during parse: 1 这也是一次硬解析
Misses in library cache during execute: 1 也执行了一次
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
1 INDEX FAST FULL SCAN LEO8_IDX
在进行硬解析时发生了bind peeking，因此oracle知道谓词i=2，因为只有一条记录i=2，所以选择走索引更优一些，前面2条sql都是第一次执行，所以都发生硬解析，bind peeking只发生在硬解析阶段。
SQL>Plan Hash: 2836784050
select * from leo8 var_i_1 where>Misses in library cache during parse: 0 表示这是一次软解析，因为之前已经硬解析过了
Misses in library cache during execute: 1 表示执行了一次
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL
尽管i=2只有一条，oracle应然选择了之前的执行计划走的是全表扫描，这显然是错误的，应该走索引更优，之所以会选错，是因为SQL软解析时不会发生bind peeking，oracle不知道变量i的值是多少，没有窥视变量。
SQL>Plan Hash: 4156792434
select * from leo8 var_i_2 where>Misses in library cache during parse: 0 表示这是一次软解析，因为之前已经硬解析过了
Misses in library cache during execute: 1 表示执行了一次
Rows Row Source Operation
------- ---------------------------------------------------
71959 SORT AGGREGATE
71959 INDEX FAST FULL SCAN LEO8_IDX
执行这条sql时，oracle还是不知道变量i的值是多少，因为没有进行硬解析，只有软解析，不会发生bind peeking，oracle还是使用之前的执行计划来走，显然是错误的，这次绑定变量就是失败的。
小结：我们总结一下，之所以会导致oracle选择错误的执行计划，是因为bind peeking只能保证在第一次硬解析的时候选择正确的执行计划，后续软解析时不发生bind peeking，如果因为变量值的改变导致执行计划的改变，而我们还是一如既往绑定之前的执行计划，那么就会发生执行计划选择错误的严重后果。
Adaptive cursor sharing (ACS) 自适应游标共享
Adaptive cursor sharing：此技术就是用于改善上述不能及时bind peeking而导致错误执行计划的问题。
Oracle 11G新特性，它可以通过不间断观察绑定变量值，来决定新的SQL是否要继续使用之前的执行计划，解决bind peeking导致后续执行计划不变的问题。
缺点：
1.更多的硬解析，要不间断的bind peeking获取新变量值，增加了一些系统负荷
2.产生更多的子游标，需要更多的内存，凡是它认为值不能在共享前面的游标时就会产生一个子游标
3.消耗更多的CPU资源
4.消耗更多的LATCH资源
5.Oracle 11.1版本上ACS技术存在bug，会无限制的产生子游标，到11.2时这个bug就被修复了。
Oracle使用ACS的前提条件
1.绑定变量使用bind peeking
2.绑定变量的列上使用直方图，了解数据分布情况
实验
LEO1@LEO1> create table leo9 as select * from dba_objects; 创建表
Table created.
LEO1@LEO1> create index leo9_idx on leo9(object_id); 创建索引
Index created.
LEO1@LEO1> update leo9 set object_id=1 where rownum update leo9 set object_id=2 where rownum select count(*) from leo9 where object_id=1; object_id=1有71906
COUNT(*)
----------
71906
LEO1@LEO1> select count(*) from leo9 where object_id=2; object_id=2有100
COUNT(*)
----------
100
LEO1@LEO1> execute dbms_stats.gather_table_stats('LEO1','LEO9',method_opt=>'for all columns>PL/SQL procedure successfully completed.
做直方图分析，让oracle了解表数据分布情况
在没有绑定变量情况下看看如何选择执行计划的
LEO1@LEO1> set autotrace trace explain
LEO1@LEO1> select * from leo9 where object_id=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 226982352
--------------------------------------------------------------------------
|>--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 71900 | 6670K| 288 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| LEO9 | 71900 | 6670K| 288 (1)| 00:00:04 |
--------------------------------------------------------------------------
object_id=1走全表扫描
Predicate Information (identified by operation>---------------------------------------------------
1 - filter("OBJECT_ID"=1)
LEO1@LEO1> select * from leo9 where object_id=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 3879848813
----------------------------------------------------------------------------------------
|>----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 9500 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LEO9 | 100 | 9500 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LEO9_IDX | 100 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
object_id=2走索引
Predicate Information (identified by operation>---------------------------------------------------
2 - access("OBJECT_ID"=2)
非常完美，各自都选择正确的执行计划了，该走全表扫描的走全表扫描，该走索引的走索引
绑定变量情况下看看如何选择执行计划的
LEO1@LEO1>>
System>
LEO1@LEO1>>
System>
LEO1@LEO1>>
System>LEO1@LEO1> select object_id,count(*) from leo9 group by object_id;
OBJECT_ID COUNT(*)
---------- ----------------- ------
1 71906
2 100
LEO1@LEO1> set autotrace traceonly
LEO1@LEO1> variable i number; 定义变量
LEO1@LEO1> execute :i:=1; 变量赋值object_id=1
PL/SQL procedure successfully completed.
LEO1@LEO1> select * from leo9 where object_id=:i; 引用变量where object_id=1
71906 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 226982352
--------------------------------------------------------------------------
|>--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36003 | 3340K| 288 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| LEO9 | 36003 | 3340K| 288 (1)| 00:00:04 |
--------------------------------------------------------------------------
走全表扫描，正确的
LEO1@LEO1> execute :i:=2; 变量赋值object_id=2
PL/SQL procedure successfully completed.
LEO1@LEO1> select * from leo9 where object_id=:i; 引用变量where object_id=2
100 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3879848813
----------------------------------------------------------------------------------------
|>----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 111 | 10545 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LEO9 | 111 | 10545 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LEO9_IDX | 111 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
走索引，正确的
LEO1@LEO1> select
sql_id,sql_text,plan_hash_value,child_number,executions,loads,buffer_gets,is_bind_sensitive,is_bind_aware,is_shareable from v$sql where sql_text like '%select * from leo9%' order by 1;
SQL_ID SQL_TEXT
PLAN_HASH_VALUE
CHILD_NUMBER
EXECUTIONS
LOADS
BUFFER_GETS
I I I
------------- -------------------------------------------------- --------------- ------------ ---------- ---------- ----------- - - -
252ghbzy0ynwd select * from leo9 where object_id=:i
3879848813
0
1
1
19
Y N Y
63937xfmcv3d2 EXPLAIN PLAN SET STATEMENT_ID='PLUS221275' FOR sel ect * from leo9 where object_id=:i
226982352
0
1
1
17
N N Y
1.从上面的执行计划可看出，变量值改变执行计划也跟着改变，oracle两次选择的都是正确的执行计划，说明ACS起作用了，每次都探测变量值，来决定执行计划是否重用。
2.从v$sql视图上看，
PLAN_HASH_VALUE：执行计划哈希值变化了，没用使用同一个执行计划
LOADS：每次都进行了硬解析，并在硬解析的时候bind peeking
IS_BIND_SENSITIVE：Y 表示ACS已经生效，会不间断探测变量值，已决定是否生成新的执行计划
IS_BIND_AWARE：Y 表示变量值的不同可能导致执行计划的改变
IS_SHAREABLE：是否允许游标重用，Y 表示游标可供下次重用，N 表示游标不可重用，说明这条SQL哈希值已经被剔除shared_pool
Leonarding
2013.2.3
天津&winter
分享技术~成就梦想
Blog：www.leonarding.com
| 
QQ群⑧：
窥视卡
雷达卡
发表于 2018-9-24 11:51:35
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡