_library_cache_advice和latch:shared pool、latch:shared pool simulator

版本10.2.0.4和11.1.0.6中"_library_cache_advice"=TRUE的情況下可能出現高latch:shared pool、latch: shared pool simulator等latch争用等待事件，預設情況下_library_cache_advice受到參數"statistics_level"的影響為TRUE，當_library_cache_advice=TRUE時他啟用library cache simulator特性。

該library cache simulator特性負責估算shared pool LRU的表現，simulator模拟器收集heap記憶體堆大小以及load載入、pin、unpin的次數資訊；通過這些資料來估算出若我們有更大的shared pool，我們可以由更大的共享池來緩存更多的SQL、PLSQL在共享池中，以此來節約加載時間。若我們設定更小的shared pool size，則又會對加載時間有何等的影響？

題外話：另一個對ASMM 下shared pool有作用的參數：

_memory_broker_shrink_heaps：

• If 0, will not try to shrink shared pool or Java pool
• If greater than zero, will wait this many seconds after failed shrink request to ask again

禁用library cache simulator設定"_library_cache_advice"=false"可能"(具體仍需要診斷)解決高latch:shared pool、latch: shared pool simulator、Library Cache - Mutex X具體等的問題，禁用library cache simulator會導緻AWR中"shared pool advisory"和 "java pool advisory"2個環節不可用，但是這些特性實際可有可無。

但是"_library_cache_advice"=false"時且啟用了ASMM(sga_target>0)的情況，注意為shared_pool_size設定一個合理的最小值！

分别在10.2.0.4和11.1.0.6上進行了針對解析的壓力測試：

10204 no change baseline	Executes/second = 3,610, DB Time = 12,349s, DB CPU = 8,938s, latch:library cache wait = 598s, avg.wait = 34ms
10204 - _library_cache_advice=off	Executes/second = 3,843, DB Time = 16,208s, DB CPU = 9,402s, latch:library cache wait = 616s, avg. wait = 50ms
11106- no change -baseline	Executes/second = 3,529, DB Time = 14,148s, DB CPU = 9,286s, library cache: mutex X wait = 2,725s, avg. wait = 1ms
11106 -session_cache=500, instantiation=150	Executes/second = 3,436, DB Time = 13,396s, DB CPU = 9,040s, library cache: mutex X wait = 2,383s avg. wait = 1ms
11106 - _library_cache_advice=off	Executes/second = 6,059, DB Time = 75,134s, DB CPU = 17,321s, library cache: mutex X wait = 38,892s,avg. wait = 1ms

針對高latch:shared pool、latch: shared pool simulator、Library Cache - Mutex X解析類等待事件，解決的思路包括：

1. 更新到最新的Patch set + PSU
2. 考慮cursor_sharing=FORCE
3. 注意即使_optim_peek_user_binds=false，若你的SQL本身還是有硬綁定的自由變量，則dc_histogram仍可能是硬解析争用的焦點
4. 設定較大的 session_cachced_cursor和instantiation
5. 設定library_cache_advice=false
6. 關閉11g中的ACS自适應遊标特性
7. 關閉11g中的cardinality feedback特性
8. 使用MSSM，或者 ASMM下