Oracle 变量绑定与变量窥视合集系列五-阿里云开发者社区

开发者社区> 数据库> 正文

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

简介:

 演示ACSadaptiver 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

我们添加了1ID列,值全为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文件中好分辨2sql语句,我给起了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,所以选择走索引更优一些,前面2sql都是第一次执行,所以都发生硬解析,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 peekingoracle不知道变量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 peekingoracle还是使用之前的执行计划来走,显然是错误的,这次绑定变量就是失败的。

小结:我们总结一下,之所以会导致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=171906

  COUNT(*)

----------

     71906

LEO1@LEO1> select count(*) from leo9 where object_id=2;       object_id=2100

  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表示ACS已经生效,会不间断探测变量值,已决定是否生成新的执行计划

IS_BIND_AWARE表示变量值的不同可能导致执行计划的改变

IS_SHAREABLE:是否允许游标重用,表示游标可供下次重用,表示游标不可重用,说明这条SQL哈希值已经被剔除shared_pool






 本文转自 leonarding151CTO博客,原文链接:http://blog.51cto.com/leonarding/1131512,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章