数据库故障诊断：一则library cache lock问题处理

2017年12月11日

云和恩墨

2345

编辑手记：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，提高系统健壮性。