Library Cache最佳化篇（一）降低library cache lock和library cache pin的方法

說明

    本文會介紹解決不同競爭的不同的方法。但是，很多時候這些現象都是由於 SQL 語句的version count造成的。 一旦看到了任何跟 library cache 相關的競爭，應該馬上檢查 AWR Report 確保沒有版本數很高（比如幾百）的 SQL 語句。官方文件296377.1有相應的troubleshooting，大家可以參考。

附上一張官方圖供參考：

 

一、降低 library cache lock 等待的方法

    這裡提供一下排查思路：首先要確認的是 library cache 的競爭是整個系統層面的還是隻發生在某個或某些 SQL 語句上？這個"library cache lock"是被一個特定的 SQL 持有很長的時間嗎？或者總是在等待某個特定的物件？還是說這個鎖在短時間內被請求的次數很多從而造成的競爭？

有兩個排查方向：系統和SQL。

1.系統層

如果問題是在整個系統層面發生的，一般來說是由於 shared pool 太小或 SQL 語句不共享造成的。

解決競爭的方法：

（1）增大 shared pool 從而減少 reload 的次數，這是因為 shared pool 過小會造成獲取鎖的時間加長。

（2）透過將 cursor_sharing 設定為 similar 或 force 來使 SQL 語句共享。

需要小心的是這樣做可能會改變SQL的執行計劃，所以做之前需要做完整的測試。

（3）在系統不繁忙的時候做統計資訊的收集或其它維護作業，從而降低invalidation的次數。

 

2. SQL層

如果是某條或某些SQL產生的問題，那麼需要檢查為什麼它持有鎖的時間會那麼長，可參考官方文件122793.1有詳細的排查步驟，這裡不再贅述。

 

二、降低 library cache pin 等待的方法

1. 如果"library cache pin"等待的時間很長，就必須判斷是隻有一兩個 process 在等待還是有很多的 process 都在等待。

2. 如果說只是一兩個 process 被另一個 process 阻塞的話，那麼需要檢查持有這個 pin 的 process 為什麼這麼長時間不釋放。

3. 如果說等待是大範圍的那麼說明 shared pool 需要最佳化。