一个参数引发的血案！

前言

曾遇到这样一个案例：某保险行业客户的一套业务系统，每个月月初会有大批业务集中办理，这个时候前端人员会觉得操作比平时要慢很多，但总体是可以忍受，直到某个月初的一天，业务hang住了，于是总部当日接到了大量投诉。之后并没有经过处理，系统逐渐又可以工作了，但每天高峰时都会hang一阵，当月的业务比平时多用了几天才完成。事后研发人员经过分析，认为是应用从数据库取数据的时间变长导致的，下面是问题的分析和处理过程。

出了份业务高峰一小时的AWR报告，主要内容如下：

一小时的dbtime高达7823.14分钟，平均活动会话数130，而这台服务器的CPU只有8颗，可以想见当时确实hang了。

看看load profile的信息:

看着除了硬解析比例稍微高点，会话登录有点频繁，并没有什么大问题。

再往下看看命中率:

Library Hit比较低，一般这个低和sharepool大小及解析有关

SGA自动管理的，shared pool居然比buffer cache还大，估计这个库的SQL数量会很大，或者绑定变量用得不好。

继续看比较关键的等待事件：

首位的cursor: pin S wait on X等待次数超多，且占比最高，接近90%。这个等待事件的意思是有会话试图以共享模式获取mutiex pin，但其他会话以独占方式持有游标对象的mutex pin，于是造成该等待。而产生该等待的主要原因，有以下几种：

shared pool设置不合理

硬解析过多

大量的version count

bug

解析失败

在AWR中检查SQLStatistics部分，在version count类里，发现明显异常

存在大量有极高version count的SQL语句，如果按占用共享内存排序则有如下信息：

单条SQL占共享内存容量巨大，还有大量未抓到的SQL，因此shared pool容量达到5个GB。依次查看SQL语句，version count最高的gx2820jz4369z如下：

其他语句也类似，中间都有类似":SYS_B_X"字样，看起来是不是很像绑定变量？确实是，但一般应用的绑定变量都是类似":B1"之类，这个却明显不一样。设置过cursor_sharing参数的同学应该知道，这是数据库自己生成的绑定变量，继续检查初始化参数

cursor_sharing果然被设置成SIMILAR，而非默认的EXACT。

cursor_sharing参数介绍：

有时应用存在SQL语句写法类似，只是条件中赋的字面值不同。由于编写代码时未使用绑定变量而导致每次执行都要进行一次硬解析，当这类SQL量非常巨大时，对系统的资源消耗不言而喻。cursor_sharing参数是用以尝试应对（并非一定有效）这一问题的，即当应用代码无法进行调整时，通过设置该参数由数据库自己判断是否对SQL语句进行变量绑定，好让同一类SQL语句能做到共享，减少硬解析数量。

这个参数在Oracle8i时有两个值--FORCE和EXACT，默认是后者，也就是只有当SQL语句完全相同时才会共享游标。如果设置为FORCE，则数据库尽量把只有字面值不同的SQL进行变量绑定。

本案例SIMILAR值是在Oracle 9i版本中新增的，目的是优化绑定后SQL语句的执行计划（比如绑定变量字段若搜集了直方图，会根据值的不同生成不同的执行计划）。这一特性旨在兼顾绑定变量设置以及执行计划的优化，但现实中设置为SIMILAR可能导致其他问题，比如SQL语句有一个父游标，却派生出大量的子游标，反而造成新的瓶颈。

由于SIMILAR这个参数值问题太多，oracle在12c中移除了该值，在11.2.0.3和11.2.0.4版本中，虽然仍保留这个参数值，但其作用是和FORCE一样的。Oracle建议11g版本中用AdaptiveCursor Sharing特性代替SIMILAR的调整执行计划的功能，不过根据实践，该特性也是问题多多，Oracle的自优化功能还有待提高。

因此，SIMILAR这个值看起来是本次问题产生的主要原因，经与研发沟通，应用层很难对SQL做绑定变量调整，于是建议将SIMILAR改为FORCE，以减少大量的子游标。

调整参数后的下一个月初，DB time已经明显降低

等待事件也恢复正常

大量高version count的SQL也消失了

关注荣科云数据公众号：