本文节选自《云和恩墨技能通讯》（12月刊）下载链接：https://www.modb.pro/doc/1593（复制链接至浏览器或扫描下方二维码即可下载）
library cache lock等待事宜是Oracle数据库较为常见的等待事宜之一，在之前的几次月刊中，我们也提到过产生library cache lock等待涌现的缘故原由有很多，如登录密码缺点考试测验过多、热表网络统计信息和SQL解析失落败等。
在Oracle 11g 版本中可能涌现由于JDBC bug导致sql绑定变量无法共享，过期游标过多的情形，此时如果发生大量并发业务，很有可能造成非常library cache lock等待事宜，造成数据库突发性能问题。在此，我们分享一次由jdbc bug和绑定变量长度问题共同“作案”引发数据库性能故障的案例，供各位参考。
问题描述
2019年10月11号晚22:00分旁边，运维职员对生产系统数据库进行清理历史分区操作，实行近100个分区删除操作后（22:05旁边）创造该数据库压力飙升，掩护职员紧急停滞历史分区清理操作，创造大量业务数据插入（INSERT）缓慢。
查看故障期间数据库创造大量library cache lock等待，数据库活动会话飙升至1000以上，数据库相应非常缓慢，业务受到严重影响。
问题剖析
从故障期间ASH的整体运行情形看：
从22:00开始，数据库的活动会话飙升，每秒活动会话飙升至1000以上。故障韶光段内的TOP EVENT紧张表现在library cache lock和library cache: mutex X等待上。
查看故障期间数据库活动会话情形：
从10:00:08的ash信息来看，多个library cache lock被4276会话壅塞，4276会话被4374会话 “cursor : mutex S”壅塞，同时4374会话被8168“library cache lock”壅塞。从ash剖析来看，大量的library cache lock会话的p3值都是5373954和5373955。5373954指的是mode=2，5373955的mode=3，只是持有的办法不同mode=3便是exclusive独占锁。
而4276会话library cache lock的p3值是5373955，对应的namespace HEX:52 —>DEC:82，mode=3。
SQL AREA BUILD解释library cache lock是在SQL解析上或SQL AREA上的问题。发生等待是会话都是在实行g14zxrn7wyaxh INSERT SQL语句：
/ PayOrderMapper.insert /INSERT INTO TxxxxxxT T(T.ID,……T.SxxxO)VALUES (SEQ_xxx.nextval, :1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36,:37,:38,:39,:40,:41,:42,:43,:44,:45,:46,SYSDATE,:47,SYSDATE,:48,:49,:50,:51)
该SQL中有51个绑定变量，多个绑定变量可能会导致bind variable graduation问题涌现，继而导致cursor无法被shared。
从ASH和DBA_HIST_SQLSTAT中可以看出21:45分之后SQL频繁load到cursor cache中，个中invalidations有120次，这是从DASH中取的数据，实际数值比采样还要大，其余SQL的LOADED_VERSION从原来的2431个在短韶光内增长到5411个，实际的version count由于11.2.0.3的隐含参数_cursor_obsolete_threshold的关系，version count超过100会重新开始。
这个时候就疑惑是由于SQL的子游标过多引起SQL解析时遍历library cache object handle链表须要很永劫光，造成了library cache: mutex x等待。
在数据库中可以看出大量loaded_version超过1000的SQL语句，并且个中有大量游标是过期的。个中SQL_ID：g14zxrn7wyaxh便是这次library cache lock等待最为严重的SQL。
导致SQL不共享的缘故原由很多，一部分是由于SQL中绑定变量长度不一致导致。
采集故障期间的AWR，创造当时DB TIME靠近2w，均匀活动会话达到1200+。
排在前五的等待事宜都属于并发类的等待事宜，个中cursor：mutex S等待次数最多。
从ASH等分析library cache lock可以得出，多个会话等待library cache lock紧张发生在SQL AREA BUILD的mutex持有争用上。Library cache: mutex X 是10.2.0.2之后library cache latch衍生出来等待。
以下是部分等待事宜的含义：
此类等待事宜每每都是发生在SQL解析前遍历library cache object handle链表找到shared cursor。
查看AWR中的Mutex Sleep信息创造：Mutex紧张有三个函数的sleep是非常高的，kgllkal3 82、kkshGetNextChild[KKSHBKLOC1]、kglUpgradeLock 119。
函数-kgllkal3 82:kglkal的意思便是kernel generic library cache management library cache lock allocate 82的意思便是SQL AREA BUILD的意思。
函数-kkshGetNextChild [KKSHBKLOC1]:kksh的意思是kernel compile shared objects (cursor) cursor hash table，便是shared cursor的hash链表。持有mutex从library cache 的handle的hash链表上找出可共享的游标。
查看library cache中namespace的命中：
从AWR中可以看出SQL AREA BUILD被要求次数是最多的，这跟ASH中大量library cache lock是吻合的，SQL AREA中cursro reloads次数也达到10740次。Invali_dations达到1170次，解释有很多cursor失落效了。
造成library cache lock等一系列严重等待事宜的缘故原由是大量的过期游标导致sql解析前花了大量韶光去遍历library cache object handle，问题SQL的5415个cursor中有5367个是标记为过期的，查看游标不能被共享的缘故原由：
造成游标不能被共享的缘故原由中有5257个游标的缘故原由是Bind Mismatch(22)，也便是绑定变量的字符长度发生变革，从32位升级到128位。
个中Bind mismatch(14)的也有3294个，这个紧张是绑定变量TIMESTAMP类型传值到DATE类型导致的问题。Bind mismatch(14)多发生在第6个绑定变量上，对应表中第7个字段，该字段恰好的DATE类型。
综合以上剖析,造成大量游标过期的缘故原由有以下两个：1、绑定变量长度导致游标无法共享2、JDBC的bug导致日期类型通过TIMESTAMP传值，继而导致绑定变量无法共享
干系bug：
Bug 18617175 : JDBC THIN SENDS SCALE VALUE OF 0 OR 9 FOR BINDS CAUSING MANY CHILD CURSORSBug 12596686 : JDBC THIN APP SENDS SCALE VALUE OF 0 OR 9 FOR BINDS CAUSING MANY CHILD CURSORSPatch 12596686: JDBC THIN APP SENDS SCALE VALUE OF 0 OR 9 FOR BINDS CAUSING MANY CHILD CURSORS
从上面截图的MOS文档来看，JDBC版本升级到11203或11204仍有发生此例绑定变量传值问题。
为什么重启运用无法办理？
1、kill session：故障发生后数据库端进行kill session操作，但是由于有连接池，以是连接池会考试测验重连数据库，kill 后的重连在连接池上险些是并发的，因此负载也很高，以是kill session弗成；
2、重启运用：重启运用前数据库真个latch竞争一贯都有，大量的活动会话并没有开释。如果这个时候重启运用还是会有新的连接进来，这些新进的连接依然会进入到行列步队中等待，继而加剧争用，由于重启并不会中止数据库上之前的连接，以是重启运用也弗成；
3、关闭运用并kill session：该当关闭运用，然后数据库端kill session，再启动运用。
问题办理建议一：后期进行历史分区清理的操作（DDL操作同类）时，需提前查询表上SQL的游标是否超过200，如超过这个阈值，应主动利用DBMS_SHARED_POOL.PURGE的办法将过期的游标清理出内存，尽可能的减少遍历游标HASH链表韶光较长的征象；
查询并清理过期游标的SQL：
select q'[exec sys.dbms_shared_pool.purge(']' || address || ',' || hash_value || q'[','C');]' as flush_sql from v$sql t where t.sql_id = '&sqlid' and t.is_obsolete = 'Y' group by address,hash_value;
建议二：从运用层面，建议将前述同一个SQLID(g14zxrn7wyaxh)的SQL文本，通过在原SQL文本中，加入不同的注释，从而将其变为多少个不同SQLID，但功能相同的SQL。其目的也是业务峰期时，将访问分散到不同的父游标上。
其他建议：1、将单个SQL游标总数加入到监控告警中，条件是v$sql_shared_cursor中的游标总量在阈值内，目前根据测试和履历总结建议阈值设置为200；
2、数据库分区掩护操作属于DDL操作，影响较大，应选择业务最低峰期进行操作；
3、数据库上实行DDL操作时，应实时监控数据库的活动会话等待事宜，如果涌现mutex或latch等待持续上升，应立即取消DDL操作，并持续监控数据库性能。
另：想理解更多数据库的知识与用法，欢迎关注墨天轮“数据库专栏”（地址：https://www.modb.pro/db，或者扫描下方二维码可直达），此外，墨天轮开放了很多数据库专栏，如 GaussDB、PolarDB、OceanBase、TDSQL、GoldenDB 等浩瀚数据库专栏，欢迎关注学习！
