問題概述
數據庫在凌晨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’”);




