cache buffers lru chain闩锁竞争与解决
当用户进程需要读数据到buffer cache时,或cache buffer根据lru算法进行管理时,就不可避免地要扫描
lru list获取可用buffer或更改buffer的状态,我们知道,oracle的buffer cache是共享内存,可以为众多
并发进程并发访问,所以在搜索的过程中必须获取latch(latch是oracle的一种串行锁机制,用于保护共享内存结构)
,锁定内存结构,防止并发访问损坏内存中的数据.
这个用于锁定lru的latch就是经常见到的cache buffers lru chain.
SQL> select addr,latch#,name,gets,misses,immediate_gets,immediate_misses 2 from v$latch where name='cache buffers lru chain'; ADDR LATCH# NAME GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES ---------------- ---------- ----------------------------- ---------- ---------- -------------- ---------------- C00000000BE23B10 117 cache buffers lru chain 2601887 8060 106765296 597096
cache buffers lru chain latch存在多个子latch,它的数量受隐含参数_db_block_lru_latches控制
SQL> select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ
2 from sys.x$ksppi x,sys.x$ksppcv y
3 where x.inst_id=USERENV('Instance')
4 and y.inst_id=USERENV('Instance')
5 and x.indx=y.indx
6 and x.ksppinm like '%_db_block_lru_latches%';
NAME VALUE DESCRIB
---------------------------- ------------- -------------------------------
_db_block_lru_latches 32 number of lru latches
可以从v$latch_children视图查看当前各子latch使用的情况:
SQL> select addr,latch#,name,gets,misses,immediate_gets,immediate_misses 2 from v$latch_children where name='cache buffers lru chain'; ADDR LATCH# NAME GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES ---------------- ---------- ------------------------- -------------- ---------- -------------- ---------------- C00000047AB14E80 117 cache buffers lru chain 68 0 0 0 C00000047AB14928 117 cache buffers lru chain 68 0 0 0 C00000047AB143D0 117 cache buffers lru chain 68 0 0 0 C00000047AB13E78 117 cache buffers lru chain 68 0 0 0 C00000047AB13920 117 cache buffers lru chain 68 0 0 0 C00000047AB133C8 117 cache buffers lru chain 68 0 0 0 C00000047AB12E70 117 cache buffers lru chain 68 0 0 0 C00000047AB12918 117 cache buffers lru chain 68 0 0 0 C00000047AB123C0 117 cache buffers lru chain 68 0 0 0 C00000047AB11E68 117 cache buffers lru chain 68 0 0 0 C00000047AB11910 117 cache buffers lru chain 68 0 0 0 C00000047AB113B8 117 cache buffers lru chain 68 0 0 0 C00000047AB10E60 117 cache buffers lru chain 68 0 0 0 C00000047AB10908 117 cache buffers lru chain 68 0 0 0 C00000047AB103B0 117 cache buffers lru chain 68 0 0 0 C00000047AB0FE58 117 cache buffers lru chain 68 0 0 0 C00000047AB0F900 117 cache buffers lru chain 68 0 0 0 C00000047AB0F3A8 117 cache buffers lru chain 68 0 0 0 C00000047AB0EE50 117 cache buffers lru chain 68 0 0 0 C00000047AB0E8F8 117 cache buffers lru chain 68 0 0 0 ADDR LATCH# NAME GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES ---------------- ---------- ------------------------- -------------- ---------- -------------- ---------------- C00000047AB0E3A0 117 cache buffers lru chain 658582 2096 27333396 148252 C00000047AB0DE48 117 cache buffers lru chain 659346 2092 27372470 148770 C00000047AB0D8F0 117 cache buffers lru chain 660582 2168 27373352 148620 C00000047AB0D398 117 cache buffers lru chain 657057 1824 27227832 152743 C00000047AB0CE40 117 cache buffers lru chain 68 0 0 0 C00000047AB0C8E8 117 cache buffers lru chain 68 0 0 0 C00000047AB0C390 117 cache buffers lru chain 68 0 0 0 C00000047AB0BE38 117 cache buffers lru chain 68 0 0 0 C00000047AB0B8E0 117 cache buffers lru chain 70 0 2 0 C00000047AB0B388 117 cache buffers lru chain 70 0 3 0 C00000047AB0AE30 117 cache buffers lru chain 70 0 3 0 C00000047AB0A8D8 117 cache buffers lru chain 70 0 2 0 32 rows selected
如果该latch竞争激烈,通常有如下方法可以采用.
适当的增大buffer cache,这样可以减少读数据到buffer cache的机会,减少扫描lru list的竞争
可以适当增加lru latch的数量,修改_db_block_lru_latches参数可以实现,但是该参数通常来说
是足够的,除非在oracle support的建议下或确知是该参数带来的影响,否则不推荐修改
通过多缓冲池技术,可以减少不希望的数据老化和全表扫描等操作对于default池的冲击,
从而可以减少竞争