Oracle 11g 中 cursor_sharing 設定為SIMILAR 導致的問題

由於是一個老專案，程式裡面基本沒有用繫結變數，所以，原來使用Oracle9i的時候，為了減少SQL的解析次數，特意把 cursor_sharing 引數設定為SIMILAR ，提高系統的效能。這次按客戶要求將系統遷移，把原來的9i資料庫直接升級為11g，對於這塊也沒細想，按照原來在9i上的引數設定方式對11g資料庫引數進行設定。

最近，系統經常性的突然宕掉，很是奇怪。因為系統包括三臺web伺服器和一臺資料庫伺服器，web伺服器前面用的F5做負載均衡，按道理一下子三臺web同時出問題的可能性不大。而且在系統不能登入的情況下，分別檢測3臺web伺服器的實體記憶體和CPU使用情況，發現資源利用率也不高，不應該是由於web伺服器的物理資源問題造成的。分析3臺web的日誌檔案，也沒有找到明細的導致問題的錯誤資訊。

初步懷疑是Oracle資料庫的問題，如是檢測了警告日誌和監聽日誌，一切正常，沒有任何錯誤資訊。如是手動建立一個快照後，重啟資料庫服務，重啟完後，3臺web上的應用麻煩恢復正常，控制檯也可以正常登入。檢視awr報告，發現top event如下：

Event	Waits	Time(s)	Avg wait (ms)	% DB time	Wait Class
cursor: mutex S	25,509,723	106,490	4	59.49	Concurrency
library cache lock	4,432,424	50,705	11	28.33	Concurrency
DB CPU		19,996		11.17
library cache: mutex X	694,774	2,246	3	1.25	Concurrency
log file sync	9,120	7	1	0.00	Commit

可以看出，主要的Wait Class都是Concurrency，證明這個系統併發很高。各種Wait Class解釋參考後面附錄。

根據最佔時間的Event cursor: mutex S去網上查詢，發現遇到這個問題的還不少，基本都是在Oracle 11g使用上遇到的，解決辦法就是把cursor_sharing 引數設定為FORCE，不需要重啟資料庫。如是按要求設定了該引數，經過了幾天的考驗，系統執行正常，不再像以前那樣平均一天宕一次了。現在的Top Event如下：

Event	Waits	Time(s)	Avg wait (ms)	% DB time	Wait Class
DB CPU		1,326		99.34
log file sync	31,957	14	0	1.07	Commit
SQL*Net more data to client	88,957	3	0	0.21	Network
direct path read	265,631	2	0	0.15	User I/O
asynch descriptor resize	1,448,518	1	0	0.10	Other

Oracle Database 10g Enhanced wait model

Idle Waits: Whenever an Oracle process has no work to do this is an idle wait. For most processes this is because they are waiting on the user to provide a new SQL statement to execute.

Application: These are waits caused by the way the application is designed. These include row lock waits, and table or other locks that are requested by the application either explicitly or implicitly (possibly due to DDL).

Configuration: These are waits which occur in a badly configured system and weill be reduced dramatically as a result of proper tuning.

Administrative: These are waits imposed by a privileged users by some action.

Concurrency: These are waits that can not be tuned and will occur on a system with High Concurrency.

Commit: This class only has log file sync. It deserves a special class because it is a necessary event and will be high and is supposed to be high on a system doing queries.

Network: All waits due to network messaging delays belong here. They are supposed to point out network congestion or latency. They should not include think or processing time, only the time spent in the networking code and hardware.

User I/O Waits: All waits for Disk I/O done by User queries or even SMON, MMON

System I/O Waits: All waits for Disk I/O done by backgrnd processes like LGWR, DBWR, ARCH, RFS. But not SMON and MMON

Scheduler: These are waits due to the resource manager

Cluster: waits which will occur only in RAC mode.

Other: All the wait events, which do not fit into one of the above classes clearly, or are not important to classify. By not important I mean those that wait for an insignificant amount of time or really do not fit into any one class.