_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有作用的参数:
针对高latch:shared pool、latch: shared pool simulator、Library Cache - Mutex X解析类等待事件,解决的思路包括:
_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
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 |
- 升级到最新的Patch set + PSU
- 考虑cursor_sharing=FORCE
- 注意即使_optim_peek_user_binds=false,若你的SQL本身还是有硬绑定的自由变量,则dc_histogram仍可能是硬解析争用的焦点
- 设置较大的 session_cachced_cursor和instantiation
- 设置library_cache_advice=false
- 关闭11g中的ACS自适应游标特性
- 关闭11g中的cardinality feedback特性
- 使用MSSM,或者 ASMM下 _memory_broker_shrink_heaps=0 + _enable_shared_pool_durations=false
posted on 2013-03-19 00:52 Oracle和MySQL 阅读(210) 评论(0) 编辑 收藏 举报