Cursor Sharing in Oracle Database 11g

遭遇過一次由於cursor_sharing = similar導致的系統問題，大量遊標無法共享，產生巨大的version count，最終讓整個系統崩潰。

在這個案例中我提到有4個條件導致了問題的發生：
1. cursor_sharing = similar
2. 收集了列上的histogram
3. SQL中使用到了此列作為條件，並且條件是“等於”
4. 這個SQL是沒有繫結變數的

在最近Optimizer Development Group的Why do I have hundreds of child cursors when cursor_sharing set to similar in 10g文章中又再次提到這個現象。

This is in fact the expected behavior. when
1. CURSOR_SHARING is set to similar
2. Bind peeking is in use
3. And a histogram is present on the column used in the where clause predicate of query

在Oracle10g中這是正常的現象，如果在某列上收集了histograms資訊，那麼就等於告訴CBO這一列上的資料是不平衡的，如果都使用同一個執行計劃那麼就可能產生問題，因此對於每一個distinct值，CBO都會產生一個child cursor，這一點無法避免。當然，由於這是child cursor，因此比cursor_sharing = exact時候產生的parent cursor還是要節省記憶體空間，至少SQL語句本身不需要重複儲存了。

在Oracle10g中解決方法是：
1. 去掉這列上的histograms統計資訊，或者
2. 將CURSOR_SHARING = FORCE

雖然源於對Oracle Database的熱愛，我們無條件接受了10g中的這個方式，並且不認為這是bug，但是實際上心裡一定暗暗罵過，傻啊，我收集了histogram你就要每個值產生1個cursor嗎？自己再分析一下不行嗎？都是一樣的執行計劃何必要用不同的cursor空間呢？

我想Oracle自己也一定意識到了這點，於是在Oracle11g中這一切有了變化，Oracle推出了稱為Adaptive Cursor Sharing（自適應遊標共享）的遊標共享機制，可以閱讀這幾篇相關文章。

Optimizer Development Group：Adaptive Cursor Sharing
Optimizer Development Group：Update on Adaptive Cursor Sharing
Arup Nanda：Adaptive Cursors and SQL Plan Management
Tim Hall：Adaptive Cursor Sharing in Oracle Database 11g Release 1

什麼是Adaptive Cursor Sharing就不重複敘述了，上面的幾篇文章說的非常清楚，那麼ACS機制對於之前在10g中碰到的child cursor過多的情況有何種改善呢？

簡單地說，在Oracle11g中我們可以保留cursor_sharing=similar並且也保留列上的histograms統計資訊，ACS機制將不會對每一個distinct值都產生一個child cursor，而是對每一個不同的執行計劃產生一個child cursor，這大大減少了子游標的數量。這種處理方式無疑是合理的，只有在執行計劃確實需要不相同的時候才產生額外的child cursor，當然，在bind peeking之後，CBO是否確實能夠選擇一個最優的執行計劃那另當別論，是另外的話題。

注意，實際上產生的child cursor數量仍然是會大於execution plan數量的，也就是加入對於一個繫結變數的SQL一共有2種執行計劃，那麼child cursor數量會大於2，因為CBO始終在監控SQL的執行效率，如果認為變數的某一個真實值跟其它值的分佈情況有很大的不同，那麼CBO就會讓這個SQL再做一次hard parse，這樣就會產生出來一個新的child cursor，即使最終這個cursor的執行計劃還是跟之前的相同。但是我們不用擔心這些多餘的cursor，因為這些cursor被標誌為無法共享（可以通過v$sql.is_shareable欄位得知），在需要的時候將會被age out出去。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/730796/viewspace-607397/，如需轉載，請註明出處，否則將追究法律責任。