#星空云诊所#:云诊所-开发中如何使用绑定变量的写法

在开发过程中，ORACLE数据库的运维人员会经常提出说从后台发现有很多未使用绑定变量，存在很多硬解析的问题，这里就跟大家分析一下什么叫未绑定变量，什么叫硬解析，分析一下原因，指导大家后续开发时让功能具有很好的性能

一、曾有项目爆出数据库共享池内存空间被耗尽，无法再解析SQL语句，也就是无法响应SQL请求

   分析：数据库的共享池，会有部分内存空间，用来保存客户端发送过来的要求执行的SQL语句，以及优化器对SQL语句解析后，生成的执行计划等数据；这样的好处就是：下次再有相同（相同的SQL哈希值）的SQL发送给数据库时，优化器经过计算识别后，会从共享池中找出已经执行过的执行计划来运行（响应该SQL语句的服务请求），不用再次计算，再次生成执行计划，这样可以减少对数据库服务器CPU及共享池内存空间的消耗，同时提高语句的执行效率；  反之，当客户端的发送过来的SQL无法重用（共享池中不存在该SQL）时，将需要解析及保存SQL语句的执行计划等操作，此时会消耗CPU和占用共享池的一些内存。

正常情况下，数据库会自动清理共享池中过期的数据，腾出内存供后续SQL使用，但随着数据量的累积，系统的复杂性增加，以及二开功能的加入，将逐渐耗尽了系统的冗余资源，当偶然业务洪峰突然来临，数据库无法（及时）清理共享池的数据，共享池没有足够的内存来响应/保存客户端的服务请求时，将会报ORA-4031错误，说到底，这错误，本质上是服务器的内存资源，不足以应付业务高峰值引起

1  开源的方法很简单，就是给服务器添加物理内存

2  节流也有两种方法:

2.1 升级数据库版本，新版本对共享池将使用更先进高效的管理方法，节省资源。

2.2 优化应用系统的代码，对于实现相同业务意义的代码，尽量使用相同的写法。也就是上面期望的：同样的代码，只需识别是否已经存在，不用再计算寻找新的执行计划；同时，也是下面所提到的，对简单的SQL语句，采用绑定变量的写法。

 二、绑定变量写法的优点

要想优化器重复使用现存语句的执行计划，就必须确保语句一模一样：优化器在接收到新的语句时，会排除语句中的空格，并把语句（非单引号内的）转换为大写，然后计算该语句的哈希值，若计算出的哈希值，与共享池中某个语句的哈希值相同，则会重用原语句的执行计划；反之，若优化器在共享池中没找到该哈希值的SQL，则会给该SQL计算寻找最佳执行计划。如下案例：

SELECT fmaterialid FROM T_bd_material  WHERE Fnumber=’A.05.01.1000000012’ ORDER BY 1;

SELECT fmaterialid FROM T_bd_material  WHERE Fnumber=’A.05.01.1000000013’ ORDER BY 1;

优化器执行完语句1后，接收到语句2的请求，经计算，发现语句2的哈希值和语句1的不一样，此时将对语句2计算其最佳执行计划，但实际上，计算后发现，两语句的执行计划（PLAN_HASH_VALUE）是一样的，如下：HASH_VALUE不一样，但PLAN_HASH_VALUE却是一样：

但显然，这种结果，并非我们期望的，因为语句的执行计划相同，但是却是经过优化器解析计算后才得到；而我们的目的，是希望只计算语句的哈希值，然后找到可重用的执行计划，不用再次计算第2条语句的执行计划；这样就可以节省掉计算新的执行计划的高CPU操作，同时也无需保存第二条SQL语句。要达到此目的，需要把原始语句改写成绑定变量的写法，也就是，把语句中常量值的位置（谓词），用变量替代，这样做的目的是告诉优化器，此处是个变量，运行时再读取。

优化器第2次接收到该语句时，发现其已经执行过一遍（共享池中保存了语句的HASH_VALUE），于是重用其此前的执行计划，运行时，再根据第二个常量值来执行查询，此时，得到的结果和最早的常量值的写法一样，但此写法却节省了语句再次解析、计算执行计划的过程。

不要忽视这点改进，事实上，应用系统日常运行时，很多时候，是在重复操作，也就是，反复执行同一功能（同一段代码），如下图：是在数据库里搜索到的，大量不同的，但执行计划相同的语句，如：排头的语句，执行了69239次，也就是，至少存在69239条这样语句，其执行计划是相同的，

此时，将这些语句改成绑定变量的写法，很有积极的优化意义。

三、绑定变量的弊端

之所以要把相近的语