70-根因分析-oracle数据库突发性能问题，谁来背这个锅

数据库突发性能问题，有时可能通过重启应用、重新收集统计信息、重启数据库等方法得到临时解决，但是，如何把故障根本原因找到，避免故障再次发生，是问题得到完美闭环的一个关键步骤（当然，能够快速恢复业务也是非常关键的一环）。这也是为什么很多对业务稳定性要求非常高的行业（比如金融、通信、铁路、航空等）的数据库系统，购买oracle 售后高服（ACS）中的顶级服务SSC服务的一个主要原因。

根因分析的另外一个重要性就是找到问题的责任方：运维、开发还是数据库产品自身原因（缺陷或是bug），有的问题是单方问题，有的可能几个方面都有。根因分析的结论会影响到部门考核，一个大家都信服的结论，可以让背锅的一方心服口服。有时候让产品背锅，也是一个比较好的结局。

见过网上有人分享一些故障处理的文章，虽然问题得到了解决，但是根因分析的结论却是完全错误的，比如有一个结论是index unique scan效率远远大于index range scan的案例，把一个数据库直方图局限性的问题，“有理有据”的解释成了索引扫描方式的问题，实属低级错误，误导观众。

今天介绍的这个案例是一个学员发来的，请我对一个银行业务系统的SQL执行计划突变导致的性能问题做根因分析。我花了半小时左右对这个问题进行了分析，并给出了根因。

问题描述：

2019-11-21 零时左右， 某重要业务SQL执行计划发生改变，执行效率严重下降。下面是采集到的sqlhc信息（数据库版本 11.2.0.4）：

根据上图信息可以看到，执行计划的cost变小了，效率却下降了几千倍。

对于有经验的优化专家来说，对于这个问题的第一反应就是谓词越界，按照这个思路，很快拿到证据（客户认为信息敏感，这里就不贴过多的细节），并给出根因。

根因分析：

1、通过表的统计信息收集历史可以查到，11月20日晚，数据库对表做了自动统计信息收集。知识点1：工作日每天的22：00~02：00这个时段，系统会对需要重新收集统计信息的表，收集统计信息。

2、知识点2：统计信息收集后的一段时间内，会对使用该表的相关SQL做硬解析。

3、知识点3：硬解析时，会发生绑定变量窥视。如果窥视到的绑定变量，超出了字段上统计信息的最大最小（一般是最大）值范围，就可能发生谓词越界。谓词越界就可能会出现索引选择错误，导致生成低效执行计划。

4、知识点4：真正的谓词越界一般发生在varchar2和number字段，日期字段虽然经常发生越界，但是系统对于日期类型字段的越界算法，有一定的容忍度，不会认为是真正的越界。这个sql发生越界的字段保存的数据是日期数据，但是使用的却是number类型。这里面涉及到一个重要的开发规范相关内容：非常不建议用number或varchar2类型保存日期数据，规范做法是使用date类型。上面数据类型随意使用的做法是开发人员为了少敲几个字母的偷懒行为。因为按天查询时，date类型一般需要写两段范围条件，而number或varchar2类型，可以用一个等值条件即可完成，而且不用做to_date转换。这种偷懒的不规范做法，会给SQL性能带来较大的性能隐患。

5、知识点5：发生谓词越界后，数据库优化器在评估这个sql可以使用的两个索引（都是以越界字段开头的组合索引）时，就不是常规的选择方式，而是选择leaf blocks较少的那一个，这个案例，leaf blocks较少的那个索引，恰好是低效的那一个。

下图上面是正常执行计划使用的高效索引，sql用到了索引的全部3个字段；下面是性能故障时执行计划使用的低效索引，sql只用到了JYRQ（number类型保存日期数据）一个字段：

下图#1对应的是上面的高效索引，#2对应上面的低效索引：

6、知识点6：sqlhc捕获到了sql后面的一些执行情况，很多使用的绑定变量已经不再越界，而且系统的自适应游标（ACS）保持开启状态，为什么ACS没能及时把执行计划调整回正常？ 这里面又涉及到另一个不规范的情况：绑定变量使用的数据类型是char，刚刚我们提到字段使用的类型是number，优化器需要对绑定变量做to_number隐式类型转换，这种转换导致了ACS不生效。

7、知识点7：网上很多文章介绍说要关闭ACS，本人对这个说法不太赞同，这个案例，虽然ACS因为绑定变量类型不匹配没有生效，但是如果绑定变量使用的数据类型也是number，那么ACS就会生效，不会出现执行计划一错到底的情况：对于后面谓词不越界的情况，ACS还能及时调整回正常的执行计划。ACS在11g版本引入，开始时bug较多，到了11204版本，很多bug已经修复了，它起到的作用远远高于bug带来的一些小问题。

总结：

通过以上分析，明显这个问题的根本原因是开发人员使用不合适的数据类型保存日期数据，导致谓词越界非常容易发生；同时又使用了错误的绑定变量数据类型，让优化器的ACS功能失效。这个锅，完全应该由开发人员来背。 但是，如果运维人员没有掌握上面的知识点，就无法分析出根因，那这个性能故障的锅就只能自己背着。

这个sql，如果开发不做代码调整，相同故障仍有可能再次出现。临时解决方法是先使用sql profile绑定执行计划。

（完）