问题概述
数据库在凌晨1点到2点之间,整体hang,出现大量的library cache lock,同时存在大量会话阻塞。业务端重启应用后恢复正常。
分析过程
从ash中可以看到数据库在01:13的时候,活动会话明显增多,并且出现大量的library cache lock等待和部分tx锁:
通过对ash分析,发现主要是因为一条高并发的insert语句:
通过对堵塞链分析,发现几乎都是这个sql,并且这个sql绑定变量非常多。
分析到这里,心里大概有了想法,大概率是因为绑定变量过多,导致的绑定变量分级,从而导致子游标不能共享,并且因为某个特殊原因触发了sql的硬解析,接下来继续往这个方向分析:
首先分析硬解析情况:
通过数据库查询,发现是因为一个drop partition操作,导致sql游标失效从而触发了硬解析。
游标共享情况:
忘记截图,确实是因为绑定变量分级导致的游标不能共享。
验证library cache lock的p3参数:
52转换成10进制等于82,对应的是sql area build,代表sql解析。
分析到此,可以确定是为因为drop partition操作导致sql游标失效,同时sql绑定变量过多,绑定变量分级导致子游标不能共享,version count过高,从而导致的硬解析风暴。
本以为分析到此已经结束,后来客户提出质疑,因为另一套几乎一样的系统,同样的操作,只卡了1分钟左右就自动恢复了,而这套卡了一个多小时,并且还是人为的去恢复,为什么?
继续思考~~
首先对比两个数据库的参数,与sql解析相关的参数其实并不多,首先想到的就是_cursor_obsolete_threshold参数,通过对比发现这个参数确实在两套库中配置不一致,一个是默认的,一个是100。
_cursor_obsolete_threshold设置为100,表示sql子游标数达到100后立即全部失效,导致sql重新硬解析,这一点oracle处理的比较暴力。
接下来分析sql的load version情况:
从sqlhc中可以看到,此sql的高峰期,load version达1600多次,按子游标数每达到100就全部失效重新硬解析来算,故障期间此sql反复失效差不多16次,而另一套库因为没有这100限制,不会反复失效。
结论
业务高峰期对表进行ddl,导致高并发的insert语句游标失效,由于绑定变量过多,绑定变量分级导致子游标不能共享,从而导致version count较高,同时又因为_cursor_obsolete_threshold参数设置较低,导致sql的version count每达到100又重新硬解析,这样反反复复,进而导致了硬解析风暴一直持续。
建议
1,禁止业务高峰期进行ddl操作
2,_cursor_obsolete_threshold的值必须大于高频语句的version count数据,防止频繁失效,频繁硬解析,造成解析性能问题。
3,根本上优化该sql多版本问题,通过多种不同的sql写法实现相同功能,分散游标到多个不同的数据库bucket上,从而减少对同一个bucket的争用。比如,增加类似/* sql1/这种写法。
4,4) 应用程序修改相关代码,手动通过10503事件指定绑定变量长度,减少因绑定变量分级导致的sql不能共享,减少sql的子游标数量,代码示例如下:
statement stmt = conn.createstatement ();
stmt.execute (“alter session set events ‘10503 trace name context forever, level 2000’”);