Oracle读书摘录1---为什么要书写绑定变量的SQL语句

是否使用绑定变量这是导致Oracle性能问题的一个主要原因，也是阻碍可扩缩性的一个重要因素。

Oracle 将已解析、已编译的SQL 连同其他内容存储在共享池（shared pool）中，这是系统全局区（System

Global Area ，SGA）中一个非常重要的共享内存结构。这个结构能完成“平滑”操作，但有一个前提，要求开发人员

在大多数情况下都会使用绑定变量。如果你确实想让Oracle 缓慢地运行，甚至几近停顿，只要根本不使用绑定变量就

可以办到。

绑定变量（bind variable）是查询中的一个占位符。例如，要获取员工123 的相应记录，可以使用

以下查询：

select * from emp where empno = 123;

或者，也可以将绑定变量:empno 设置为123，并执行以下查询：

select * from emp where empno = :empno;

在典型的系统中，你可能只查询一次员工123，然后不再查询这个员工。之后，你可能会查询员工456，

然后是员工789，如此等等。如果在查询中使用直接量（常量），那么每个查询都将是一个全新的查询，在

数据库看来以前从未见过，必须对查询进行解析、限定（命名解析）、安全性检查、优化等。简单地讲，就

是你执行的每条不同的语句都要在执行时进行编译。

第二个查询使用了一个绑定变量:empno，变量值在查询执行时提供。这个查询只编译一次，随后会把

查询计划存储在一个共享池（库缓存）中，以便以后获取和重用这个查询计划。以上两个查询在性能和可

扩缩性方面有很大差别，甚至可以说有天壤之别。

从前面的描述应该能清楚地看到，与重用已解析的查询计划（称为软解析，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

as

begin

for i in 1 .. 10000

loop

execute immediate

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

end loop;

end;

/

Procedure created.

 

第二个过程则分别为要插入的每一行构造一条独特的SQL 语句：

ops$tkyte@ORA9IR2> create or replace procedure proc2

as

begin

for i in 1 .. 10000

loop

execute immediate

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

end loop;

end;

/

Procedure created

现在看来，二者之间惟一的差别，是一个过程使用了绑定变量，而另一个没有使用。它们都使用了动

态SQL（所谓动态SQL 是指直到运行时才确定的SQL），而且过程中的逻辑也是相同的。不同之处只在于是

否使用了绑定变量。

 

可以看到，如果使用了绑定变量（后面称为绑定变量方法），则只有4 次硬解析；没有使用绑定变量

时（后面称为无绑定变量方法），却有不下10 000 次的硬解析（每次插入都会带来一次硬解析）。还可以看

到，无绑定变量方法所用的闩数是绑定变量方法的两倍之多。这是因为，要想修改这个共享结构，Oracle

必须当心，一次只能让一个进程处理（如果两个进程或线程试图同时更新同一个内存中的数据结构，将非

常糟糕，可能会导致大量破坏）。因此，Oracle 采用了一种闩定（latching）机制来完成串行化访问，闩

（latch） 是一种轻量级锁定设备。不要被“轻量级”这个词蒙住了，作为一种串行化设备，闩一次只允

许一个进程短期地访问数据结构。闩往往被硬解析实现滥用，而遗憾的是，这正是闩最常见的用法之一。

共享池的闩和库缓存的闩就是不折不扣的闩；它们成为人们频繁争抢的目标。这说明，想要同时硬解析语

句的用户越多，性能问题就会变得越来越严重。人们执行的解析越多，对共享池的闩竞争就越厉害，队列

会排得越长，等待的时间也越久。

注意如果机器的处理器不止一个，在9i 和以上版本中，共享池还可以划分为多个子池，每个子池都

由其自己的闩保护。这样即使应用没有使用绑定变量，也可以提高可扩缩性，但是这并没有从根

本上克服闩定问题。

执行无绑定变量的SQL 语句，很像是在每个方法调用前都要编译子例程。假设把Java 源代码交付给

客户，在调用类中的方法之前，客户必须调用Java 编译器，编译这个类，再运行方法，然后丢掉字节码。

下一次想要执行同样的方法时，他们还要把这个过程再来一遍：先编译，再运行，然后丢掉字节码。你肯

定不希望在应用中这样做。数据库里也应该一样，绝对不要这样做。

对于这个特定的项目，可以把现有的代码改写为使用绑定变量，这是最好的做法。改写后的代码与原

先比起来，速度上有呈数量级的增长，而且系统能支持的并发用户数也增加了几倍。不过，在时间和精力

投入方面却要付出很大的代价。并不是说使用绑定变量有多难，也不是说使用绑定变量容易出错，而只是

因为开发人员最初没有使用绑定变量的意识，所以必须回过头去，几乎把所有代码都检查和修改一遍。如

果他们从第一天起就很清楚在应用中使用绑定变量至关重要，就不用费这么大的功夫了。