問題概述

數據庫在凌晨1點到2點之間，整體hang,出現大量的library cache lock，同時存在大量會話阻塞。業務端重啟應用后恢復正常。

分析過程

從ASH中可以看到數據庫在01:13的時候，活動會話明顯增多，并且出現大量的library cache lock等待和部分tx鎖：

通過對ASH分析，發現主要是因為一條高并發的insert語句：




通過對堵塞鏈分析，發現幾乎都是這個sql，并且這個sql綁定變量非常多。
分析到這里，心里大概有了想法，大概率是因為綁定變量過多，導致的綁定變量分級，從而導致子游標不能共享，并且因為某個特殊原因觸發了SQL的硬解析，接下來繼續往這個方向分析：
首先分析硬解析情況：


通過數據庫查詢，發現是因為一個drop partition操作，導致SQL游標失效從而觸發了硬解析。

游標共享情況：
忘記截圖，確實是因為綁定變量分級導致的游標不能共享。

驗證library cache lock的P3參數：



52轉換成10進制等于82，對應的是SQL AREA BUILD，代表SQL解析。

分析到此，可以確定是為因為drop partition操作導致SQL游標失效，同時SQL綁定變量過多，綁定變量分級導致子游標不能共享，version count過高，從而導致的硬解析風暴。

本以為分析到此已經結束，后來客戶提出質疑，因為另一套幾乎一樣的系統，同樣的操作，只卡了1分鐘左右就自動恢復了，而這套卡了一個多小時，并且還是人為的去恢復，為什么？

繼續思考~~
首先對比兩個數據庫的參數，與sql解析相關的參數其實并不多，首先想到的就是_cursor_obsolete_threshold參數，通過對比發現這個參數確實在兩套庫中配置不一致，一個是默認的，一個是100。
_cursor_obsolete_threshold設置為100，表示sql子游標數達到100后立即全部失效，導致SQL重新硬解析，這一點Oracle處理的比較暴力。

接下來分析SQL的load version情況：

從sqlhc中可以看到，此sql的高峰期，load version達1600多次，按子游標數每達到100就全部失效重新硬解析來算，故障期間此sql反復失效差不多16次，而另一套庫因為沒有這100限制，不會反復失效。

結論

業務高峰期對表進行DDL，導致高并發的insert語句游標失效，由于綁定變量過多，綁定變量分級導致子游標不能共享，從而導致version count較高，同時又因為_cursor_obsolete_threshold參數設置較低，導致sql的version count每達到100又重新硬解析，這樣反反復復，進而導致了硬解析風暴一直持續。

建議

1，禁止業務高峰期進行DDL操作
2，_cursor_obsolete_threshold的值必須大于高頻語句的version count數據，防止頻繁失效，頻繁硬解析，造成解析性能問題。
3，根本上優化該SQL多版本問題，通過多種不同的sql寫法實現相同功能，分散游標到多個不同的數據庫bucket上，從而減少對同一個bucket的爭用。比如，增加類似/*+SQL1/這種寫法。
4，4) 應用程序修改相關代碼，手動通過10503事件指定綁定變量長度，減少因綁定變量分級導致的SQL不能共享，減少SQL的子游標數量，代碼示例如下：
Statement stmt = conn.createStatement ();
stmt.execute (“ALTER SESSION SET EVENTS ‘10503 trace name context forever, level 2000’”);