使用绑定变量

如果我要写一本书谈谈如何构建不可扩缩的Oracle应用，肯定会把“不要使用绑定变量”作为第一章和最后一章的标题重点强调。这是导致性能问题的一个主要原因，也是阻碍可扩缩性的一个重要因素。Oracle将已解析、已编译的SQL连同其他内容存储在共享池（shared pool）中，这是系统全局区（System Global Area ，SGA）中一个非常重要的共享内存结构。第4章将详细讨论共享池。这个结构能完成“平滑”操作，但有一个前提，要求开发人员在大多数情况下都会使用绑定变量。如果你确实想让Oracle缓慢地运行，甚至几近停顿，只要根本不使用绑定变量就可以办到。
绑定变量（bind variable）是查询中的一个占位符。例如，要获取员工123的相应记录，可以使用以下查询：
select * from emp where empno = 123;
或者，也可以将绑定变量:empno设置为123，并执行以下查询：
select * from emp where empno = :empno;
在典型的系统中，你可能只查询一次员工123，然后不再查询这个员工。之后，你可能会查询员工456，然后是员工789，如此等等。如果在查询中使用直接量（常量），那么每个查询都将是一个全新的查询，在数据库看来以前从未见过，必须对查询进行解析、限定（命名解析）、安全性检查、优化等。简单地讲，就是你执行的每条不同的语句都要在执行时进行编译。
第二个查询使用了一个绑定变量:empno，变量值在查询执行时提供。这个查询只编译一次，随后会把查询计划存储在一个共享池（库缓存）中，以便以后获取和重用这个查询计划。以上两个查询在性能和可扩缩性方面有很大差别，甚至可以说有天壤之别。
62 / 860
从前面的描述应该能清楚地看到，与重用已解析的查询计划（称为软解析，soft parse）相比，解析包含有硬编码变量的语句（称为硬解析，hard parse）需要的时间更长，而且要消耗更多的资源。硬解析会减少系统能支持的用户数，但程度如何不太明显。这部分取决于多耗费了多少资源，但更重要的因素是库缓存所用的闩定（latching）机制。硬解析一个查询时，数据库会更长时间地占用一种低级串行化设备，这称为闩（latch），有关的详细内容请参见第6章。这些闩能保护Oracle共享内存中的数据结构不会同时被两个进程修改（否则，Oracle最 后会得到遭到破坏的数据结构），而且如果有人正在修改数据结构，则不允许另外的人再来读取。对这些数据结构加闩的时间越长、越频繁，排队等待闩的进程就越 多，等待队列也越长。你可能开始独占珍贵的资源。有时你的计算机显然利用不足，但是数据库中的所有应用都运行得非常慢。造成这种现象的原因可能是有人占据 着某种串行化设备，而其他等待串行化设备的人开始排队，因此你无法全速运行。数据库中只要有一个应用表现不佳，就会严重地影响所有其他应用的性能。如果只 有一个小应用没有使用绑定变量，那么即使其他应用原本设计得很好，能适当地将已解析的SQL放在共享池中以备重用，但因为这个小应用的存在，过一段时间就会从共享池中删除已存储的SQL。这就使得这些设计得当的应用也必须再次硬解析SQL。真是一粒老鼠屎就能毁了一锅汤。
如果使用绑定变量，无论是谁，只要提交引用同一对象的同一个查询，都会使用共享池中已编译的查询计划。这样你的子例程只编译一次就可以反复使用。这样做效率很高，这也正是数据库期望你采用的做法。你使用的资源会更少（软解析耗费的资源相当少），不仅如此，占用闩的时间也更短，而且不再那么频繁地需要闩。这些 都会改善应用的性能和可扩缩性。
要想知道使用绑定变量在性能方面会带来多大的差别，只需要运行一个非常小的测试来看看。在这个测试中，将在一个表中插入一些记录行。我使用如下所示的一个简单的表：
ops$tkyte@ORA9IR2> drop table t;
Table dropped.
ops$tkyte@ORA9IR2> create table t ( x int );
Table created.
下面再创建两个非常简单的存储过程。它们都向这个表中插入数字1到10 000；不过，第一个过程使用了一条带绑定变量的SQL语句：
ops$tkyte@ORA9IR2> create or replace procedure proc1
2 as
3 begin
4 for i in 1 .. 10000
5 loop
6 execute immediate
7 'insert into t values ( :x )' using i;
63 / 860
8 end loop;
9 end;
10 /
Procedure created.
第二个过程则分别为要插入的每一行构造一条独特的SQL语句：
ops$tkyte@ORA9IR2> create or replace procedure proc2
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;
10 /
Procedure created.
现在看来，二者之间惟一的差别，是一个过程使用了绑定变量，而另一个没有使用。它们都使用了动态SQL（所谓动态SQL是指直到运行时才确定的SQL），而且过程中的逻辑也是相同的。不同之处只在于是否使用了绑定变量。
下面用我开发的一个简单工具runstats对这两个方法详细地进行比较：
注意 关于安装runstats和其他工具的有关细节，请参见本书开头的“配置环境”一节。
ops$tkyte@ORA9IR2> exec runstats_pkg.rs_start
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> exec proc1
PL/SQL procedure successfully completed.
64 / 860
ops$tkyte@ORA9IR2> exec runstats_pkg.rs_middle
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> exec proc2
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> exec runstats_pkg.rs_stop(1000)
Run1 ran in 159 hsecs
Run2 ran in 516 hsecs
run 1 ran in 30.81% of the time
结果清楚地显示出，从墙上时钟来看，proc2（没有使用绑定变量）插入10 000行记录的时间比proc1（使用了绑定变量）要多出很多。实际上，proc2需要的时间是proc1的3倍多，这说明，在这种情况下，对于每个“无绑定变量”的INSERT，执行语句所需时间中有2/3仅用于解析语句！
注意 如果愿意，也可以不用runstats，而是在SQL*Plus中执行命令SET TIMING ON，然后运行proc1和proc2，这样也能进行比较。
不过，对于proc2，还有更糟糕的呢！runstats工具生成了一个报告，显示出这两种方法在闩利用率方面的差别，另外还提供了诸如解析次数之类的统计结果。这里我要求runstats打印出差距在1 000以上的比较结果（这正是rs_stop调用中1000的含义）。查看这个信息时，可以看到各方法使用的资源存在显著的差别：
Name Run1 Run2 Diff
STAT...parse count (hard) 4 10,003 9,999
LATCH.library cache pin 80,222 110,221 29,999
LATCH.library cache pin alloca 40,161 80,153 39,992
LATCH.row cache enqueue latch 78 40,082 40,004
LATCH.row cache objects 98 40,102 40,004
LATCH.child cursor hash table 35 80,023 79,988
65 / 860
LATCH.shared pool 50,455 162,577 112,122
LATCH.library cache 110,524 250,510 139,986
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
407,973 889,287 481,314 45.88%
PL/SQL procedure successfully completed.
注意 你自己测试时可能会得到稍微不同的值。如果你得到的数值和上面的一样，特别是如果闩数都与我的测试结果完全相同，那倒是很奇怪。不过，假设你也像我一样，也是在Linux平台上使用Oracle9i Release 2，应该能看到类似的结果。不论哪个版本，可以想见，硬解析处理每个插入所用的闩数总是要高于软解析（对于软解析，更确切的说法应该是，只解析一次插入，然后反复执行）。还在同一台机器上，但是如果使用 Oracle 10g Release 1执行前面的测试，会得到以下结果：与未使用绑定变量的方法相比，绑定变量方法执行的耗用时间是前者的1/10，而所用的闩总数是前者的17%。这有两个原因，首先，10g 是一个新的版本，有一些内部算法有所调整；另一个原因是在10g中，PL/SQL采用了一种改进的方法来处理动态SQL。
可以看到，如果使用了绑定变量（后面称为绑定变量方法），则只有4次硬解析；没有使用绑定变量时（后面称为无绑定变量方法），却有不下10 000次的硬解析（每次插入都会带来一次硬解析）。还可以看到，无绑定变量方法所用的闩数是绑定变量方法的两倍之多。这是因为，要想修改这个共享结构，Oracle必须当心，一次只能让一个进程处理（如果两个进程或线程试图同时更新同一个内存中的数据结构，将非常糟糕，可能会导致大量破坏）。因此，Oracle采用了一种闩定（latching）机制来完成串行化访问，闩（latch） 是一种轻量级锁定设备。不要被“轻量级”这个词蒙住了，作为一种串行化设备，闩一次只允许一个进程短期地访问数据结构。闩往往被硬解析实现滥用，而遗憾的 是，这正是闩最常见的用法之一。共享池的闩和库缓存的闩就是不折不扣的闩；它们成为人们频繁争抢的目标。这说明，想要同时硬解析语句的用户越多，性能问题 就会变得越来越严重。人们执行的解析越多，对共享池的闩竞争就越厉害，队列会排得越长，等待的时间也越久。
注意 如果机器的处理器不止一个，在9i 和以上版本中，共享池还可以划分为多个子池，每个子池都由其自己的闩保护。这样即使应用没有使用绑定变量，也可以提高可扩缩性，但是这并没有从根本上克服闩定问题。
执行无绑定变量的SQL语句，很像是在每个方法调用前都要编译子例程。假设把Java源代码交付给客户，在调用类中的方法之前，客户必须调用Java编译器，编译这个类，再运行方法，然后丢掉字节码。下一次想要执行同样的方法时，他们还要把这个过程再来一遍：先编译，再运行，然后丢掉字节码。你肯定不希望在应用中这样做。数据库里也应该一样，绝对不要这样做。
对于这个特定的项目，可以把现有的代码改写为使用绑定变量，这是最好的做法。改写后的代码与原先比起来，速度上有呈数量级的增长，而且系统能支持的并发用户 数也增加了几倍。不过，在时间和精力投入方面却要付出很大的代价。并不是说使用绑定变量有多难，也不是说使用绑定变量容易出错，而只是因为开发人员最初没 有使用绑定变量的意识，所以必须回过头去，几乎把所有代码都检查和修改一遍。如果他们从第一天起就很清楚在应用中使用绑定变量至关重要，就不用费这么大的 功夫了。