ORACLE 綁定變量

Oracle 绑定变量 详解

分类： Oracle 高级知识 Oracle 性能调优 2010-09-01 19:52 896人阅读 评论(1) 收藏 举报

 

之前整理过一篇有关绑定变量的文章，不太详细，重新补充一下。

         Oracle 绑定变量

         http://blog.csdn.net/tianlesoftware/archive/2009/10/17/4678335.aspx

 

 

绑定变量

         bind variable： A variable in a SQL statement that must be replaced with a valid value, or the address of a value, in order for the statement to successfully execute.

 

变量绑定是OLTP系统中一个非常值得关注的技术。良好的变量绑定会使OLTP系统数据库中的SQL 执行速度飞快，内存效率极高；不使用绑定变量可能会使OLTP 数据库不堪重负，资源被SQL解析严重耗尽，系统运行缓慢。

 

         当一个用户与数据库建立连接后，会向数据库发出操作请求，即向数据库送过去SQL语句。 Oracle 在接收到这些SQL后，会先对这个SQL做一个hash 函数运算，得到一个Hash值，然后到共享池中寻找是否有和这个hash 值匹配的SQL存在。 如果找到了，Oracle将直接使用已经存在的SQL 的执行计划去执行当前的SQL，然后将结果返回给用户。 如果在共享池中没有找到相同Hash 值的SQL，oracle 会认为这是一条新的SQL。 会进行解析。

 

 

Oracle 解析的步骤如下：

（1）       语法解析

（2）       语义解析

（3）       生成执行计划，这里分软解析和硬解析。硬解析是非常耗资源的。

（4）       SQL的执行

 

关于SQL的解析，详见Blog：

         Oracle SQL的硬解析和软解析

         http://blog.csdn.net/tianlesoftware/archive/2010/04/08/5458896.aspx

 

了解了SQL 的执行过程，在来看一些绑定变量，绑定变量的本质就是本来需要做Oracle 硬解析的SQL 变成软解析，以减少ORACLE 花费在SQL解析上的时间和资源。

 

加入有两条SQL:    

         Select salary from user where name=’A’;

         Select salary from user where name=’B’;

 

如果没有用绑定变量，那么这2条SQL 会被解析2次，因为他们的谓词部分不一样。 如果我们用了绑定变量，如：

         Select salary from user where name=:X;

 

这时，之前的2条SQL就变成了一种SQL， Oracle 只需要对每一种SQL做一次硬解析，之后类似的SQL 都使用这条SQL产生的执行计划，这样就可以大大降低数据库花费在SQL解析上的资源开销。 这种效果当SQL执行的越多，就越明显。

 

         简单的说，绑定变量就是拿一个变量来代替谓词常量，让Oracle每次对用户发来的SQL做hash 运算时，运算出的结果都是同样的Hash值，于是将所有的用户发来的SQL看作是同一个SQL来对象。

 

 

（二）

 

在oracle 中，对于一个提交的sql语句,存在两种可选的解析过程, 一种叫做硬解析,一种叫做软解析.

一个硬解析需要经解析,制定执行路径,优化访问计划等许多的步骤.硬解释不仅仅耗费大量的cpu，更重要的是会占据重要的们闩（latch）资源，严重的影响系统的规模的扩大（即限制了系统的并发行），而且引起的问题不能通过增加内存条和cpu的数量来解决。之所以这样是因为门闩是为了顺序访问以及修改一些内存区域而设置的，这些内存区域是不能被同时修改。当一个sql语句提交后，oracle会首先检查一下共享缓冲池（shared pool）里有没有与之完全相同的语句，如果有的话只须执行软分析即可，否则就得进行硬分析。

 而唯一使得oracle 能够重复利用执行计划的方法就是采用绑定变量。绑定变量的实质就是用于替代sql语句中的常量的替代变量。绑定变量能够使得每次提交的sql语句都完全一样。

普通sql语句：

SELECT fname, lname, pcode FROM cust WHERE id = 674;

SELECT fname, lname, pcode FROM cust WHERE id = 234;

SELECT fname, lname, pcode FROM cust WHERE id = 332;

 含绑定变量的sql 语句：

SELECT fname, lname, pcode FROM cust WHERE id = :cust_no;

 

Sql*plus 中使用绑定变量：

sql> variable x number;

sql> exec :x := 123;

sql> SELECT fname, lname, pcode FROM cust WHERE id =:x;

 

pl/sql

 pl/sql很多时候都会自动绑定变量而无需编程人员操心，即很多你写得sql语句都会自动利用绑定变量，如下例所示：

create or replace procedure dsal(p_empno in number)

as

  begin

    update emp

    set sal=sal*2

    where empno = p_empno;

    commit;

  end;

/

也许此时你会想要利用绑定变量来替代p_empno,但是这是完全没有必要的，因为在pl/sql中，引用变量即是引用绑定变量。

但是在pl/sql中动态sql并不是这样。

在vb，java以及其他应用程序中都得显式地利用绑定变量。

对于绑定变量的支持不仅仅限于oracle,其他RDBMS向SQLＳＥＲＶＥＲ也支持这一特性。

但是并不是任何情况下都需要使用绑定变量，　下面是两种例外情况：

１．对于隔相当一段时间才执行一次的ｓｑｌ语句，这是利用绑定变量的好处会被不能有效利用优化器而抵消

２．数据仓库的情况下。