绑定变量及其优缺点

变量绑定（bind variable）

是指在sql语句的条件中使用变量而不是常量。比如shared pool里有两条sql语句，

select * from tab1 where col1=1;

select * from tab1 where col1=2;

对oracle数据库来说，这是两条完全不同的SQL，对这两条语句都需要进行hard parse。因为oracle会根据sql语句的文本去计算每个字符在内存里的hash值，因此虽然上述两条SQL只有一个字符不一样，oracle根据hash算法在内存中得到的hash地址就不一样，所以oracle就会认为这是两条完全不同的语句。而如果将上述SQL改写成select * from tab1 where col1=:var1;,然后通过对变量var1的赋值去查询，那么oracle对这条语句第一次会进行hard parse，以后就只进行soft parse。假设某条语句被重复执行了几十万次，那么使用bind var带来的好处是巨大的。一个应用程序如果bind var使用不充分，那么几乎一定会伴随着严重的性能问题。

绑定变量是相对文本变量来讲的，所谓文本变量是指在SQL直接书写查询条件，这样的SQL在不同条件下需要反复解析，绑定变量是指使用变量来代替直接书写条件，查询bind value在运行时传递，然后绑定执行。优点是减少硬解析，降低CPU的争用，节省shared_pool ;缺点是不能使用histogram,sql优化比较困难

对于每个程序开发人员来说，数据库的知识都是或多或少的了解些，都能编写一些SQL语句，即使不会也可以使用一些工具来生成SQL语句，因此数据库在很多时候被认为是没有必要研究的。随着系统使用人数增加，系统也遇到了瓶颈，于是开发人员高呼：“给我内存与CPU，系统将会faster!”可是作为盈利性企业，投入与回报是不能成等价比的，必须做到投入少回报多！所以，数据库的开发在系统的前期工作当中非常重要，良好的数据库设计将会提升系统的可扩展性。

数据库在执行SQL语句时会首先解析SQL语句，解析又分为硬解析与软解析。说到硬解析和软解析，就不能不说一下Oracle对sql的处理过程。当你发出一条sql语句交付Oracle，在执行和获取结果前，Oracle对此sql将进行几个步骤的处理过程：

1、语法检查(syntax check)

　 检查此sql的拼写是否语法。

2、语义检查(semantic check)

   诸如检查sql语句中的访问对象是否存在及该用户是否具备相应的权限。

3、对sql语句进行解析(prase)

   利用内部算法对sql进行解析，生成解析树及执行计划。

4、执行sql，返回结果(execute and return)。

其中，软、硬解析就发生在第三个过程里，Oracle利用内部的hash算法来取得该sql的hash值，然后在librarycache里查找是否存在该hash值。假设存在，则将此sql与cache中的进行比较（注意此处的比较哪怕是一个字母的大小写、空格个数不一致都会认为不同）。假设“相同”，就将利用已有的解析树与执行计划，而省略了优化器的相关工作。这也就是软解析的过程；如果上面的2个假设中任有一个不成立，那么优化器都将进行创建解析树、生成执行计划的动作。这个过程就叫硬解析。由此可以看出应该极力避免硬解析，尽量使用软解析。

有什么方法能尽量使用软解析呢？答曰：“绑定变量，效果好”。如果使用绑定变量，只要提交引用同一对象的同一个查询，都会使用共享池中已编译的查询计划。这样你只需编译一次就能反复使用，效率当然会高。这也是数据库希望你采用的方法。

下面来看一个简单的例子，首先创建一个简单表

SQL> create table t (x int);

然后创建一个使用绑定变量往T表插入数据的存储过程：

SQL> create or replace procedure pro1

  2 as

  3  begin

  4 for i in 1..10000

  5     loop

  6      execute immediate

  7     'insert into t values(:x)' using i;

  8   end loop;

  9 end;

再创建一个不适用绑定变量往T表查数据的存储过程：

SQL> create or replace procedure pro2

  2 as

  3 begin

  4 for i in 1..10000

  5     loop

  6      execute immediate

  7     'insert into t values('||i||')';

  8   end loop;

  9 end;

下面开始执行这两个存储过程，查看下运行时间：

SQL> set timing on

SQL> exec pro1;

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.42

SQL> truncate table t; --此目的只是与执行存储过程pro1前T表都为空

表被截断。

已用时间:  00: 00: 00.20

SQL> exec pro2;

PL/SQL 过程已成功完成。

已用时间:  00: 00: 11.53

SQL> set timing off

结果非常明显，使用绑定变量节省的时间可不是一点。这只是个简单的例子，如果在实际应用中那节省的时间将会更多，可能就会造成2个不同性能级别的应用系统。在一个已成型的应用系统中再重新使用绑定变量时费时费力的，你要寻找每个SQL语句，查看是否能使用绑定变量，最重要的是你可能已经提交给客户，这直接影响到客户的满意度。

绑定变量的好处是减少了硬解析，降低了CPU的竞争，节省了shared_pool，但缺点是不能使用柱状图（histogram），SQL优化比较困难。

在系统的研发阶段，应该为数据库设计投入更多的时间，因为所有的数据都是在后台运行的，后台的性能在很大程度上决定了一个系统的性能。

不要以为数据库只有SELECT、INSERT、UPDATE、DELETE，这些只是数据库的基础操作，只是这些简单的操作就需要开发人员增加一些HINT来加快数据的操作，由这些操作还能延伸到数据库的redo日志、undo段的管理等等。数据库并不是一门简单的“语言”，数据库调优与维护更是数据库的精华所在，需要我们学而时习之！