一個參數引發的血案！

前言

曾遇到這樣一個案例：某保險行業客戶的一套業務系統，每個月月初會有大批業務集中辦理，這個時候前端人員會覺得操作比平時要慢很多，但總體是可以忍受，直到某個月初的一天，業務hang住了，於是總部當日接到了大量投訴。之後並沒有經過處理，系統逐漸又可以工作了，但每天高峰時都會hang一陣，當月的業務比平時多用了幾天才完成。事後研發人員經過分析，認為是應用從資料庫取數據的時間變長導致的，下面是問題的分析和處理過程。

出了份業務高峰一小時的AWR報告，主要內容如下：

一小時的dbtime高達7823.14分鐘，平均活動會話數130，而這台伺服器的CPU只有8顆，可以想見當時確實hang了。

看看load profile的信息:

看著除了硬解析比例稍微高點，會話登錄有點頻繁，並沒有什麼大問題。

再往下看看命中率:

Library Hit比較低，一般這個低和sharepool大小及解析有關

SGA自動管理的，shared pool居然比buffer cache還大，估計這個庫的SQL數量會很大，或者綁定變數用得不好。

繼續看比較關鍵的等待事件：

首位的cursor: pin S wait on X等待次數超多，且佔比最高，接近90%。這個等待事件的意思是有會話試圖以共享模式獲取mutiex pin，但其他會話以獨佔方式持有游標對象的mutex pin，於是造成該等待。而產生該等待的主要原因，有以下幾種：

shared pool設置不合理

硬解析過多

大量的version count

bug

解析失敗

在AWR中檢查SQLStatistics部分，在version count類里，發現明顯異常

存在大量有極高version count的SQL語句，如果按佔用共享內存排序則有如下信息：

單條SQL占共享內存容量巨大，還有大量未抓到的SQL，因此shared pool容量達到5個GB。依次查看SQL語句，version count最高的gx2820jz4369z如下：

其他語句也類似，中間都有類似":SYS_B_X"字樣，看起來是不是很像綁定變數？確實是，但一般應用的綁定變數都是類似":B1"之類，這個卻明顯不一樣。設置過cursor_sharing參數的同學應該知道，這是資料庫自己生成的綁定變數，繼續檢查初始化參數

cursor_sharing果然被設置成SIMILAR，而非默認的EXACT。

cursor_sharing參數介紹：

有時應用存在SQL語句寫法類似，只是條件中賦的字面值不同。由於編寫代碼時未使用綁定變數而導致每次執行都要進行一次硬解析，當這類SQL量非常巨大時，對系統的資源消耗不言而喻。cursor_sharing參數是用以嘗試應對（並非一定有效）這一問題的，即當應用代碼無法進行調整時，通過設置該參數由資料庫自己判斷是否對SQL語句進行變數綁定，好讓同一類SQL語句能做到共享，減少硬解析數量。

這個參數在Oracle8i時有兩個值--FORCE和EXACT，默認是後者，也就是只有當SQL語句完全相同時才會共享游標。如果設置為FORCE，則資料庫盡量把只有字面值不同的SQL進行變數綁定。

本案例SIMILAR值是在Oracle 9i版本中新增的，目的是優化綁定後SQL語句的執行計劃（比如綁定變數欄位若搜集了直方圖，會根據值的不同生成不同的執行計劃）。這一特性旨在兼顧綁定變數設置以及執行計劃的優化，但現實中設置為SIMILAR可能導致其他問題，比如SQL語句有一個父游標，卻派生出大量的子游標，反而造成新的瓶頸。

由於SIMILAR這個參數值問題太多，oracle在12c中移除了該值，在11.2.0.3和11.2.0.4版本中，雖然仍保留這個參數值，但其作用是和FORCE一樣的。Oracle建議11g版本中用AdaptiveCursor Sharing特性代替SIMILAR的調整執行計劃的功能，不過根據實踐，該特性也是問題多多，Oracle的自優化功能還有待提高。

因此，SIMILAR這個值看起來是本次問題產生的主要原因，經與研發溝通，應用層很難對SQL做綁定變數調整，於是建議將SIMILAR改為FORCE，以減少大量的子游標。

調整參數後的下一個月初，DB time已經明顯降低

等待事件也恢復正常

大量高version count的SQL也消失了

關注榮科雲數據公眾號：

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！