在开发过程中,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段谓词过滤复杂,那执行计划将有多种选择,此时,绑定变量就未必是最好的选择,此时,还是使用常量写法比较合适,能让优化器找到最佳的执行计划。
推荐阅读