ffer的存取就需要多次方位lru list,从而会产生cache buffer lru chain的竞争。
另外对于该latch 还有很多子latch,可以通过v$latch_children查看:
Eg:
SQL> selectname,gets,misses,sleeps,immediate_gets,immediate_misses,spin_gets,wait_timefrom v$latch_children where name like '%cache buffers lru chain%';
NAME GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES SPIN_GETS WAIT_TIME
-------------------------------------------------------------------------- ---------- ---------- -------------- ---------------- --------------------
cache bufferslru chain 0 0 0 0 0 0 0
cache bufferslru chain 12 0 0 3 0 0 0
cache bufferslru chain 0 0 0 0 0 0 0
cache bufferslru chain 12 0 0 3 0 0 0
cache bufferslru chain 0 0 0 0 0 0 0
cache bufferslru chain 12 0 0 3 0 0 0
cache bufferslru chain 0 0 0 0 0 0 0
cache bufferslru chain 12 0 0 3 0 0 0
cache bufferslru chain 0 0 0 0 0 0 0
cache bufferslru chain 12 0 0 3 0 0 0
cache bufferslru chain 0 0 0 0 0 0 0
对于子latch的数量受一个隐含参数控制:
Eg:
SQL>@getpar.sql
Enter value forpar: block_lru_latches
KSPPINM KSPPSTVL KSPPDESC
---------------------------------------------------------------------- ------------------------------------------------------------
_db_block_lru_latches 240 number of lru latches
SQL>
对于,这种竞争我们采用如下办法解决:
1) 修改buffercache大小,减小物理读的次数
2) 调整_db_block_lru_latches的个数,增加latch个数
3) 优化sql语句减少读取lrulist的次数
4)设置oracle多缓冲池技术,一个是recycle池另一个是keep池,以此来减少数据老化和全表扫描对default buffer 的压力。