cache buffers lru chain

http://www.jydba.net/cache-buffers-lru-chain/

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竞争激烈,通常有如下方法可以采用.

1.适当的增大buffer cache,这样可以减少读数据到buffer cache的机会,减少扫描lru list的竞争

2.可以适当增加lru latch的数量,修改_db_block_lru_latches参数可以实现,但是该参数通常来说
是足够的,除非在oracle support的建议下或确知是该参数带来的影响,否则不推荐修改

3.通过多缓冲池技术,可以减少不希望的数据老化和全表扫描等操作对于default池的冲击,
从而可以减少竞争