### 一、现象：
*某客户Oracle 12.1.0.2 RAC HP_UNIX核心数据库出现大量的library cache lock、library cache mutex x竞争，业务反馈超时严重。出现问题的时间2021-01-13 15:54活动会话发生波动，直到16:00:02等待消失。*
###二、问题分析：
当时等待事件为library cahce lock，产生library cache lock的原因通常有三种：登录密码错误尝试过多、热表收集统计信息和SQL解析失败。
首先看看这段时间内，数据库到底发生了什么。
通过查看发现该库在15:54:33存在对核心表进行grant操作，然后grant操作导致游标被purge。
Oracle中的cursor有两种，一种是session cursor，一种是shared cursor;shared cursor缓存了sql，pl/sql语句的sql以及sql语句的执行计划，sql涉及的对象，sql使用的绑定变量类型及长度。SQL 040tcvz8pv1gz游标被purge,该SQL解析失败。

详细步骤如下：

1.活动会话分析
数据库按照分钟级别分组发现15:54活动会话发生波动，持续到16:00下降
![](http://img.blog.itpub.net/blog/2022/02/23/f8d903d9124ce252.jpeg?x-oss-process=style/bb)

2.等待事件分析
查看等待事件，library cache lock等待严重
![](http://img.blog.itpub.net/blog/2022/02/23/db0065bb0252cd23.jpeg?x-oss-process=style/bb)
![](http://img.blog.itpub.net/blog/2022/02/23/3b486a1c2ed38449.png?x-oss-process=style/bb)

3.library cache lock的P3参数进行拆分
![](http://img.blog.itpub.net/blog/2022/02/23/6713e23eee33808f.png?x-oss-process=style/bb)
将其转换为16进制
![](http://img.blog.itpub.net/blog/2022/02/23/1970dbb677f43759.png?x-oss-process=style/bb)
前面的4位0052代表namespace,后面的4位0002代表mode
转换为10进制：
![](http://img.blog.itpub.net/blog/2022/02/23/9aeae1854639f0a1.png?x-oss-process=style/bb)

82对应的NAMESPACE为SQL AREA BUILD
![](http://img.blog.itpub.net/blog/2022/02/23/68a27a4808d02db9.jpeg?x-oss-process=style/bb)

SQL AREA BUILD通常是由于大量解析导致。

4.SQL重新解析的原因
等待都集中在SQL 040tcvz8pv1gz 上，脱敏后的语句为：

```sql
select *  from T1 a
 where (((a.col1 = :p1 and a.col2 = :p2 and a.col3 > CAST(:p3 AS date)) 
and (a.col4 <> :p4 or a.col5 IS NULL)))
```

通常，重新解析有几个可能性，SQL被挤出去，或者底层对象发生DDL需要重新解析。

检查涉及的表的last_ddl_time

```sql
select last_ddl_time from dba_objects where object_name = 'T1';

last_ddl_time
-----------------------------
2021-01-13 15:54:33
```

发现该表在15:54发生过ddl.

5.通过审计追溯DDL

该数据库启用DDL触发器，会记录产生的DDL, 检查DDL日志表,发现该表在15:54:33存在grant
该库在15:54:33存在grant操作，grant操作导致游标被purge，直到16:00:02游标才重新load进来，此时等待消失。
![](http://img.blog.itpub.net/blog/2022/02/23/5ef42ae1653fd993.png?x-oss-process=style/bb)
![](http://img.blog.itpub.net/blog/2022/02/23/e88fdb14e4378423.png?x-oss-process=style/bb)
游标并没有大量增加，并且loads、invalidations、version_count都没有较大的变化，排除掉是子游标过多等导致的library cache问题。

6.查看SQL游标不能被共享的原因，是由于该cursor在故障前已经被purge，实际的子游标也仅仅只有5个：

Purged_cursor：cursor marked for purging,the cursor has been marked for puring with dbms_shared_pool.purge

![](http://img.blog.itpub.net/blog/2022/02/23/4287fb872bf341f0.jpeg?x-oss-process=style/bb)
![](http://img.blog.itpub.net/blog/2022/02/23/c2ea1199d4de92cc.jpeg?x-oss-process=style/bb)
数据库在16:00:02等待消失，DCL授权也类同DDL也可能导致大量的library cache等等待。
![](http://img.blog.itpub.net/blog/2022/02/23/44943a2b70eb352c.jpeg?x-oss-process=style/bb)

### 三：参考知识
关于library cache相关等待可参考'library cache lock' Waits: Causes and Solutions (文档 ID 1952395.1)

In this Document
Purpose
Troubleshooting Steps
 	wait: library cache lock
 	Cause Identified: Unshared SQL Due to Literals
 	Solution Identified: Rewrite the SQL to use bind values
 	Solution Identified: Use the CURSOR_SHARING initialization parameter
 	Cause Identified: Shared SQL being aged out
 	Solution Identified: Increase the size of the shared pool
 	Solution Identified: 10g+: Use the Automatic Shared Memory Manager (ASMM) to adjust the shared pool size
 	Solution Identified: Keep ("pin") frequently used large PL/SQL and cursor objects in the shared pool
 	Cause Identified: Library cache object Invalidations
 	Solution Identified: Do not perform DDL operations during busy periods
 	Solution Identified: Do not collect optimizer statistics during busy periods
 	Solution Identified: Do not perform TRUNCATE operations during busy periods
 	Cause Identified: Objects being compiled across sessions
 	Solution Identified: Avoid compiling objects in different sessions at the same time or during busy times
 	Cause Identified: Auditing is turned on
 	Solution Identified: Evaluate the need to audit
 	Cause Identified: Unshared SQL in a RAC environment
 	Solution Identified: Rewrite the SQL to use bind values
 	Solution Identified: Use the CURSOR_SHARING initialization parameter
 	Cause Identified: Extensive use of row level triggers
 	Solution Identified: Evaluate the need for the row trigger
 	Cause Identified: Excessive Amount of Child Cursors
 	Solution Identified: Inappropriate use of parameter CURSOR_SHARING set to SIMILAR
References

1、绑定变量未使用：导致shared pool对象被频繁的age in和age out
2、shared pool对象被频繁的age in和age out：shared pool不足、ASMM动态管理带来的SGA抖动、较大内存的PL/SQL和cursor object存储在shared pool中，每次重新装载进来都需要进行空间整理，此时会导致相关对象被age out
3、library cache object invalidations：对象被DDL、收集统计信息、truncate table，如果存在并发的DDL操作和DML，而DDL一直未完成，此时DDL会持有该对象的library cache object handle的X Lock，DML会请求该对象的ibrary cache object handle的S lock模式，此时DML就会被hang住
4、对象被编译：编译会对该对象的library cache object handle持有library cache lock x模式和library cache pin x模式，此时如果还有并发的相关SQL涉及到存储过程，执行存储过程需要持有library cache lock null和library cache pin s，则会出现library cache pin等待，如果有并发的编译或者DDL则可能出现library cache lock等待。
5、审计引起
6、行级触发器：触发器会级联相关对象的DML，级联对象越多，隐患越大
7、child cursor：每次生成child cursor，需要在library cache object中装载新对象，就需要获取相关library cache object handle对象的x lock，latch层面还需要获取latch shared pool和latch library cache cache(在oracle 11g后latch library cache lock被library cache mutex代替),如果不断产生大量子游标，则会导致在申请新cursor时出现library cache lock等待；当然一般子游标过多肯定也会伴随着latch shared pool和latch library cache或者library cache mutex x等待。
8、DDL触发会导致相关SQL全部重新硬解析，并发的SQL硬解析则会出现library cache lock竞争(对象在table的library cache object handle的library cache lock)

ITPub博客

故障：核心表业务高峰期授权导致library cache lock和mutex x竞争