1. 爱易网页
  2. 数据库教程
  3. _library_cache_advice跟latch:shared pool、latch:shared pool simulator

_library_cache_advice跟latch:shared pool、latch:shared pool simulator

日期：2014-05-16　 浏览次数：20792 次

_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, libra

相关资料更多>

  1. 关于日期系统日期处置
  2. Json树形构造数据转Java对象并存储到数据库的实现-超简单的JSON复杂数据处理
  3. JDBC分页挑动的一场血案（第一回）（本故事纯属虚构如有雷同联系作者）
  4. ORACLE 8i，9i 表联接方法
  5. MySQL 有关“InnoDB Error ib_logfile0 of different size”异常
  6. 数据库优化草案
  7. 微软认证考试70-461 Modify Data 数据修改 -24%比重-(一)
  8. oracle10.2.0.1打补丁升级到10.2.0.4,促成数据库打不开，报ORA-00704
  9. 装配HBase Snappy