绑定变量 oracle好处,绑定变量的好处和坏处

传参数

绑定变量的目的

首先来说明绑定变量的目的，一般来说在OLTP系统中经常会出现同一条sql除了谓词不同之外会被经常反复的执行，而每次执行就由于谓词的不同CBO会认为是不同的sql所以每次都会重新生成执行计划，这样每次硬解析带来的资源的争用和消耗会非常大，也可能会产生阻塞。

这样我们就可以把相同的部分提取出来，而不同的部分则用变量来替代，这样一来同一条sql在被反复执行的情况下由于PGA中有library  cache的指针，sql在被重复执行时候就可以直接定位子游标(无需重复定位游标)，而且随后的执行就不会考虑sql的执行计划而直接执行了。

适用场景

OLTP系统：

特点：

用户并发高

表中一般有主键(由于小数据量的访问时，基于主键的操作定位相对是非常快的)

操作数据范围和数据量都叫小(一般可能都是单条sql)

执行计划基本相同(绑定变量后，后面执行的sql讲不会产生执行计划，直接软解析)

Sql的重复率高

OLAP系统：

首先用户少(olap系统一般受众面可能就是企业决策和业务人员)

执行计划不固定(因单条sql都是海量的数据，所以在不知确切数据量的情况下执行计划的差异是很大的，所以此时是否选择主键后索引就有待考证)

Sql的解析对系统影响几乎不计(olap的sql性能一般在IO和sql的执行效率，解析阶段的资源消耗相对执行上几乎可以忽略)

总结：

从以上总结完全可以看出此技术就非常适合oltp

好处和坏处

好处：需重复解析和定位游标，提高执行解析效率

坏处：CBO毕竟不智能，它无法知道具体的业务需求。绑定变量就是固话了执行计划，随后的sql完全按照第一条sql的执行计划解析，但是不同变量是否都适合同一个执行计划(比如分区索引当访问的数据在同一个分区，此时没有问题，但是如果跨分区了是否可能就是全局索引可能比分区索引更适合此时的执行计划呢)

好处：

不绑定变量时

创建表，索引以及收集相应统计信息

create table task02 as select * fromdba_objects;

create index idx_tk2 on task02(object_id);

exec

dbms_stats.gather_table_stats(user,'task02',cascade=>

true);

跑sql脚本

select count(*) from task02 where object_id=10;

select count(*) from task02 where object_id=20100;

查看sql的执行次数和解析次数

selectsql_id,parse_calls,executions,loads,plan_hash_value from

v$sql where sql_text='selectcount(*) from task02 where

object_id=10'

select  sql_id,parse_calls,executions,loads,plan_hash_value

from v$sql wheresql_text='select count(*) from task02 where

object_id=20100';

从截图看出这两条sql分别被执行一次和解析一次，切具有相同的执行计划编号

重复再执行两次再查看结果

selectsql_id,parse_calls,executions,loads,plan_hash_value from

v$sql where sql_textin('select count(*) from task02 where

object_id=10','select count(*) fromtask02 where

object_id=20100');

从图中可以看出两条sql的解析次数和执行次数分别变成3，但是loads硬解析次数依然为1没有改变，说明此时两条sql最后两次均为软解析，但这两条sql却被分别被硬解析一次。

如果此类sql 比较多，则大量的硬解析会造成latch的争用和访问阻塞等。

使用绑定变量：

定义变量并执行sql

variable cnt number;

exec :cnt:=100;

select count(*) from task02 whereobject_id=:cnt;

exec :cnt:=10100;

select count(*) from task02 whereobject_id=:cnt;

下面重新查看这两条sql的执行次数和解析次数

selectsql_id,parse_calls,executions,loads,plan_hash_value from

v$sql wheresql_text='select count(*) from task02 where

object_id=:cnt';

从截图中可以清楚的看到这两条sql均硬解析一次，其余为软解析。说明此时绑定变量已生效，oracle把它当作同一条sql来解析而不用硬解析两次。

结论：

绑定变量能大大减少硬解析的次数，所以在一般oltp系统中使用绑定变量即可以避免热快，也可以大大减少资源的争用和消耗。

坏处：

因绑定变量固定了执行计划，但CBO还并没有智能到可以知道我们想要的一切，所以加入不同的变量需要CBO选择最合适的执行计划时，此时由于绑定变量的存在导致CBO可能会选择错误的执行计划，这时不正确的执行计划只会消耗更多额外的资源。

先查看表的数据量

可以看出先前建的表中有71972条数据

执行第一条sql

variable cnt number;

exec :cnt:=100;

select count(*) from task02 where object_id=:cnt;

可以看出此时CBO选择认为该sql的范围数据量为3599条(这个跟我们需要的数据量相差有点远，可能未完全收集统计信息的缘故)

继续测试第二条sql

exec :cnt:=80000;

select count(*) from task02

whereobject_id<=:cnt;

10.png (24.22 KB, 下载次数:

0)

下载附件

保存到相册

2013-11-23 16:18 上传

可以看出此时CBO仍然认为只有3599条返回结果，且从开始查看表的数据量来说应该此条sql走全表扫描效率是最高的，可是它依然沿用了先前IRS的扫描方式，我们可以认为此执行计划大错特错，此时CBO已经不能给我们提供一个正确的执行计划。

不过oracle11g出了个新技术，先前跟群里同学也讨论过！！ACS变量窥探，使用前提是

1、绑定变量使用了bind peeking。

2、绑定变量的列上有直方图信息。

条件1一般绑定变量了都可能会满足(但是我目前不知道怎么验证)，条件2也肯定是满足的。结果是各种测试，执行计划就是不动，所以奇怪这11g这技术到底应该怎么才能生效，

绑定变量有这个东西应该问题就可以解决，还望测试过的同学出来指点指点！

总结：

所以好处坏处已经列出(只是测试了一种)，好处是减少了资源的重复消耗和避免资源的争用而出现热块等问题。

坏处就是可能随着变量的不同我们需要不同的执行计划的时候，CBO却无法给我们一个正确的执行计划。