[ORACLE]Cache Buffers LRU Chain 闩锁竞争与解决 latch: cache buffers lru chain
Oracle 的 Buffer Cache 是共享内存,可以为众多并发进程并发访问,所以在搜索的过程中必须获取 Latch(Latch是 Oracle 的一种串行锁机制,用于保护共享内存结构),锁定内存结构,防止并发访问损坏内存中的数据(我们必须认识到对于数据的访问、Buffer 的存取就意味着多次的 Latch 访问,而过于严重的 Latch 竞争常常是系统的瓶颈所在
latch: cache buffers lru chain事件
SQL> select * from v$event_name where NAME='latch: cache buffers lru chain'; EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS DISPLAY_NAME CON_ID
------ ---------- ------------------------------- ---------- --------- ----------- ------------- ----------- ---------- ---------------------------- ----
781 3401628503 latch: cache buffers lru chain address number why 1893977003 0 Other latch: cache buffers lru chain 0
SQL> col name for a25 SQL> select addr,latch#,name,gets,misses,immediate_gets,immediate_misses from v$latch where name = 'cache buffers lru chain'; ADDR LATCH# NAME GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES ---------------- ---------- ------------------------- ---------- ---------- -------------- ---------------- 0000000060167918 322 cache buffers lru chain 29612 12 80511 153
Cache Buffers Lru Chain Latch 存在多个子 Latch,其数量受隐含参数_db_block_lru_latches控制
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ FROM x$ksppi x,x$ksppcv y WHERE x.inst_id =USERENV ('Instance') AND y.inst_id =USERENV ('Instance') AND x.indx = y.indx AND x.ksppinm LIKE '%lru_latches%'; NAME VALUE DESCRIB ------------------------ ------------------ ------------------------------ _db_block_lru_latches 36 number of lru latches
从 v$latch_children 视图察看当前各子 Latch 使用情况:
SQL> select addr,child#,name,gets,misses,immediate_gets igets,immediate_misses imisses from v$latch_children where name = 'cache buffers lru chain'; ADDR CHILD# NAME GETS MISSES IGETS IMISSES ---------------- ---------- ------------------------- ---------- ---------- ---------- ---------- 00000000A870A718 72 cache buffers lru chain 0 0 0 0 00000000A870A640 71 cache buffers lru chain 18 0 1 0 00000000A8709E60 70 cache buffers lru chain 0 0 0 0 00000000A8709D88 69 cache buffers lru chain 18 0 1 0 00000000A87095A8 68 cache buffers lru chain 0 0 0 0 00000000A87094D0 67 cache buffers lru chain 18 0 1 0 00000000A8708CF0 66 cache buffers lru chain 0 0 0 0 00000000A8708C18 65 cache buffers lru chain 18 0 1 0 00000000A8708208 64 cache buffers lru chain 0 0 0 0 00000000A8708130 63 cache buffers lru chain 18 0 1 0 00000000A8707950 62 cache buffers lru chain 0 0 0 0 00000000A8707878 61 cache buffers lru chain 18 0 1 0 00000000A8707098 60 cache buffers lru chain 0 0 0 0 00000000A8706FC0 59 cache buffers lru chain 18 0 1 0 00000000A87067E0 58 cache buffers lru chain 0 0 0 0 00000000A8706708 57 cache buffers lru chain 18 0 1 0 00000000A8705CF8 56 cache buffers lru chain 0 0 0 0 00000000A8705C20 55 cache buffers lru chain 18 0 1 0 00000000A8705440 54 cache buffers lru chain 0 0 0 0 00000000A8705368 53 cache buffers lru chain 18 0 1 0 00000000A8704B88 52 cache buffers lru chain 0 0 0 0 00000000A8704AB0 51 cache buffers lru chain 18 0 1 0 00000000A87042D0 50 cache buffers lru chain 0 0 0 0 00000000A87041F8 49 cache buffers lru chain 18 0 1 0 00000000A87037E8 48 cache buffers lru chain 0 0 0 0 00000000A8703710 47 cache buffers lru chain 18 0 1 0 00000000A8702F30 46 cache buffers lru chain 0 0 0 0 00000000A8702E58 45 cache buffers lru chain 18 0 1 0 00000000A8702678 44 cache buffers lru chain 0 0 0 0 00000000A87025A0 43 cache buffers lru chain 18 0 1 0 00000000A8701DC0 42 cache buffers lru chain 0 0 0 0 00000000A8701CE8 41 cache buffers lru chain 18 0 1 0 00000000A87012D8 40 cache buffers lru chain 0 0 0 0 00000000A8701200 39 cache buffers lru chain 18 0 1 0 00000000A8700A20 38 cache buffers lru chain 0 0 0 0 00000000A8700948 37 cache buffers lru chain 18 0 1 0 00000000A8700168 36 cache buffers lru chain 0 0 0 0 00000000A8700090 35 cache buffers lru chain 18 0 1 0 00000000A86FF8B0 34 cache buffers lru chain 0 0 0 0 00000000A86FF7D8 33 cache buffers lru chain 18 0 1 0 00000000A86FEDC8 32 cache buffers lru chain 0 0 0 0 00000000A86FECF0 31 cache buffers lru chain 18 0 1 0 00000000A86FE510 30 cache buffers lru chain 0 0 0 0 00000000A86FE438 29 cache buffers lru chain 18 0 1 0 00000000A86FDC58 28 cache buffers lru chain 0 0 0 0 00000000A86FDB80 27 cache buffers lru chain 18 0 1 0 00000000A86FD3A0 26 cache buffers lru chain 0 0 0 0 00000000A86FD2C8 25 cache buffers lru chain 18 0 1 0 00000000A85C8A98 24 cache buffers lru chain 0 0 0 0 00000000A85C89C0 23 cache buffers lru chain 7410 5 20378 36 00000000A85C81E0 22 cache buffers lru chain 0 0 0 0 00000000A85C8108 21 cache buffers lru chain 7263 2 20272 39 00000000A85C7928 20 cache buffers lru chain 0 0 0 0 00000000A85C7850 19 cache buffers lru chain 7239 2 20229 36 00000000A85C7070 18 cache buffers lru chain 0 0 0 0 00000000A85C6F98 17 cache buffers lru chain 7097 3 20069 42 00000000A85C6588 16 cache buffers lru chain 0 0 0 0 00000000A85C64B0 15 cache buffers lru chain 18 0 1 0 00000000A85C5CD0 14 cache buffers lru chain 0 0 0 0 00000000A85C5BF8 13 cache buffers lru chain 18 0 1 0 00000000A85C5418 12 cache buffers lru chain 0 0 0 0 00000000A85C5340 11 cache buffers lru chain 18 0 1 0 00000000A85C4B60 10 cache buffers lru chain 0 0 0 0 00000000A85C4A88 9 cache buffers lru chain 18 0 1 0 00000000A85C4078 8 cache buffers lru chain 0 0 0 0 00000000A85C3FA0 7 cache buffers lru chain 18 0 1 0 00000000A85C37C0 6 cache buffers lru chain 0 0 0 0 00000000A85C36E8 5 cache buffers lru chain 18 0 1 0 00000000A85C2F08 4 cache buffers lru chain 0 0 0 0 00000000A85C2E30 3 cache buffers lru chain 18 0 1 0 00000000A85C2650 2 cache buffers lru chain 0 0 0 0 00000000A85C2578 1 cache buffers lru chain 18 0 1 0 72 rows selected.
如果该 Latch 竞争激烈,通常有如下方法可以采用:
(1) 适当增大 Buffer Cache,这样可以减少读数据到 Buffer Cache 的机会,减少扫Lru List 的竞争。
(2) 可以适当增加 LRU Latch 的数量,修改_db_block_lru_latches 参数可以实现,但是该参数通常来说是足够的,除非在 Oracle Support 的建议下或确知该参数将带来的影响,否则不推荐修改。
(3) 通过多缓冲池技术,可以减少不希望的数据老化和全表扫等操作对于 Default池的冲击,从而可以减少竞争。
(4) 优化 SQL,减少数据读取,从而减少对于 LRU List 的扫᧿。
每天进步一点点,多思考,多总结
版权声明:本文为CNblog博主「zaituzhong」的原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接及本声明。