数据库故障诊断:一则library cache lock问题处理
【云和恩墨,提供7*24最专业的数据恢复(Oracle,MySQL,SQL server)服务,致力于为您的数据库系统做最后一道安全防护!服务热线:010-59007017-7030】数据恢复|数据库运维|性能优化|安全保障|Oracle培训|MySQL培训
编辑手记:library cache lock 大家都并不陌生,在MOS上对该阻塞的一般成因描述为:一般可以理解的是alter table或者alter package/procedure会以X模式持有library cache lock,造成阻塞(444560.1)。但针对具体问题仍要具体分析,今天分享一则因SQL绑定变量出现空值,导致大量子游标产生并引发library cache lock的故障,供大家参考借鉴。
某数据库为Oracle 11.2.0.3.9 RAC Linux 64bit,一天晚上9点左右,业务系统反应缓慢,数据库曾发现有大量library cache lock等待事件,并伴随有library cache:mutex X,导致业务系统短暂无法正常提供业务处理
当天起发现数据库有大量librarycache lock,平均等待有1775ms ,并伴随有librarycache: mutex X。
观察ASH报告,等待library cache lock会话在执行SQL如下
对比上周同一天的AWR,这个SQL执行的次数差不多,大概半小时7万次左右,但在23号的AWR中,该SQL在Order by Version Count出现,Version Count为76(实际上在v$sql中发现有2万个 不同CHILD_ADDRESS出现),说明该SQL产生过2万个子游标。这里也看到其他SQL High Version,但由于其他SQL执行没有0pjnn575vchbg频繁,并不引发library cache lock等待。
该SQL已占用了1GB的共享池空间
结合数据库版本和环境情形,初步推断为ACS BUG引发。但在关闭ACS特性后,library cache lock等待事件与子游标依然存在。
这样排除了ACS BUG引起后。观察V$SQL_SHARED_CURSOR中大量BIND_MISMATCH,但BIND_MISMATCH根据Oracle的规则,只有5,6种不同的可能性,不至于产生2万个子游标。进一不查看V$SQL_BIND_CAPTURE发现绑定变量值中,出现异常的varchar2类型,且值为空。结合Bug 8198150 - High Versioncount with bind_mismatch with passing null value to bind (文档 ID 8198150.8),推断该SQL绑定变量时输入了空值,导致产生大量子游标。在V$SQL_BIND_CAPTURE视图中表现为VARCHAR2类型(varchar2为Oracle默认类型,null值无类型则为Oracle默认类型)。
应用做调整限制SQL绑定NULL输入后,SQL正常,无子游标产生。
通过故障的情况相关信息初步推断为ACS(自适应)bug引起。
在关闭ACS特性后观察,SQL子游标和librarycache lock等待事件依然存在。
进步分析并通过测试确认,原因由于SQL绑定变量输入null值触发BUG,导致会产生大量子游标,引发library cache lock等待。在应用代码中将null限制后SQL正常
应用端严格限制非合理的绑定变量时null值输入。
建议给数据库打上最新PSU,避免触发各BUG,提高系统健壮性。