在开发过程中，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条这样语句，其执行计划是相同的，

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

三、绑定变量的弊端

之所以要把相近的语句改成采用绑定变量的写法，是因为我们发现，优化器对其解析后，得到的执行计划是一样的，此时，重用老的执行计划即可，没必要重复解析；显然，此时优化器是忽略掉绑定变量具体的值，认为任意的值都具有相同的意义；但实际上，若语句的谓词条件中，使用不同的常量值，产生的执行计划不一样时，若用回老的执行计划，其效率肯定不是最佳的，如下面：表TAB1的字段COL1=21时，其总行数大幅增加，

也就是，字段COL1的直方图值，并非均匀，而是明显倾斜的。此时，当执行此语句：

Select * from tabs where col1=1，语句将走索引；

若 Select * from tabs where col1=21，语句将走全表扫描。

这是两种完全不一样的执行计划，但按照我们上面的设想，把语句改成绑定变量的写法后，不论是1，或21，第一次执行产生的执行计划，将会被语句后续再执行时，再次使用，显然，这种情况下，不论优化器如何取舍，都不是最佳选择。　　换句话说，我们上面设想的，把常量值改成绑定变量的写法，是基于该字段的直方图值，是平均的，也就是，每个字段值的个数，是基本接近的；当直方图值明显倾斜时，不同的常量值，会产生不同的执行计划时，此时，这种写法将存在负面意义。

四、绑定变量的规范

现实中，很难评判什么样的语句，采用什么样的写法，能带来更好的效率；为方便行事，我们立个简单的规矩（后续会持续改进），给大伙个大概的印象，方便判断是否对语句采用绑定变量的写法：

1 语句总共只涉及到的对象：表或视图，不超过2个，也就是，语句足够简单。

2从业务上可判断：语句的执行计划，是从某个唯一性较高的ID字段开始，走索引查询。

如：

3.  2个表 

 　select t6.f_dm_filethick from t_sal_orderentry_k t5 left outer join f_dm_filingthick t6 on t6.fid = t5.f_dm_filingthickid where t5.fentryid = 15661838

.4.  1个视图

   select * from v_invoice_detail_fp Where (xsddm=2806507)

  5.  1个表，而且基本可以确定：执行计划从这一步开始执行：t0.fid = 2675668（该字段有索引）。

 　select fid from t_bd_flexsitemdetailv where 1=1  and ff100501 = n' ' and ff100001 = 99296243 order by fid;

这样要求的目的，是希望语句的执行计划够简单，不会有翻盘的情况；若语句涉及到表（对象）较多，where段谓词过滤复杂，那执行计划将有多种选择，此时，绑定变量就未必是最好的选择，此时，还是使用常量写法比较合适，能让优化器找到最佳的执行计划。