Oracle 变量绑定与变量窥视合集系列五

五 演示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 id,l.* from dba_objects l;   创建leo8表

Table created.

LEO1@LEO1> select id from leo8 where rownum<10;

        ID

------------------

         1

         1

         1

         1

         1

         1

         1

         1

         1

我们添加了1个ID列，值全为1，为了后面好对比测试效果

LEO1@LEO1> update leo8 set id=2 where rownum=1;        设置第一条记录的ID列值为2

1 row updated.

LEO1@LEO1> select id from leo8 where rownum<5;          除了第一条余下都为1

        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 size 254');

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> alter session set sql_trace=true;     启动SQL追踪功能

Session altered.

LEO1@LEO1> select * from leo8 var_i_1 where id=:i;     变量值等于1时的SQL语句

……

LEO1@LEO1> execute :i:=2;                               变量赋值i=2

PL/SQL procedure successfully completed.

LEO1@LEO1> select * from leo8 var_i_2 where id=:i;     变量值等于2时的SQL语句

……

说明：为了在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 id=:i   1            1       1

select * from leo8 var_i_2 where id=:i   1            1       1

由于表的别名不同，oracle认为这是2条不相同的SQL，因此都做了1次硬解析（hard parse），都执行了1次，oracle将会在硬解析时对绑定变量进行变量窥视bind peeking

LEO1@LEO1> select * from leo8 var_i_1 where id=:i;    变量值等于2时的结果集，应走索引

…..

我们第二次执行这条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 id=:i;   变量值等于1时的结果集，应走全表扫描

…..

我们重新赋予变量i=1，也是第二次执行这条SQL，它也刚硬解析完，这次也只是做软解析，不再进行变量窥视，由于i=1占了全表99.9%，应该走全表扫描，我们将会在后面trace文件中看看是不是这样

LEO1@LEO1> alter session set sql_trace=false;              关闭sql_trace功能

Session altered.

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: Release 11.2.0.1.0 - Development on Sun Feb 3 14:28:54 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

看一下trace文件内容

[oracle@leonarding1 trace]$ vim bind_peeking.txt

SQL ID: 9n5fa1q0nvczd

Plan Hash: 948063081

select * from leo8 var_i_1 where id=:I    第一条sql语句，变量i=1

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 ID: 9ux1nun51a030

Plan Hash: 948063081

select * from leo8 var_i_2 where id=:I     第二条sql语句，变量i=2

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 ID: 1sguay77pxxhj

Plan Hash: 2836784050

select * from leo8 var_i_1 where id=:I    第三条sql语句，变量i=2

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 ID: aam2chsgpj7mb

Plan Hash: 4156792434

select * from leo8 var_i_2 where id=:I     第四条sql语句，变量i=1

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<=72006;

72006 rows updated.

LEO1@LEO1> update leo9 set object_id=2 where rownum<=100;

100 rows updated.

LEO1@LEO1> 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 size 254');

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

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   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 id):

---------------------------------------------------

   1 - filter("OBJECT_ID"=1)

LEO1@LEO1> select * from leo9 where object_id=2;

Execution Plan

----------------------------------------------------------

Plan hash value: 3879848813

----------------------------------------------------------------------------------------

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   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 id):

---------------------------------------------------

   2 - access("OBJECT_ID"=2)

非常完美，各自都选择正确的执行计划了，该走全表扫描的走全表扫描，该走索引的走索引

绑定变量情况下看看如何选择执行计划的

LEO1@LEO1> alter system flush shared_pool;       清空shared_pool

System altered.

LEO1@LEO1> alter system flush shared_pool;

System altered.

LEO1@LEO1> alter system flush shared_pool;       多清空几遍比较保险

System altered.

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

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   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

----------------------------------------------------------------------------------------

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   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