### 一、現象：
*某客戶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)

故障：核心表業務高峰期授權導致library cache lock和mutex x競爭

相關文章