Trouble shooting latch: cache buffers chains

SQL> select name, parameter1, parameter2, parameter3 from v$event_name where name like '&event';
Enter value for event: latch: cache buffers chains
old   1: select name, parameter1, parameter2, parameter3 from v$event_name where name like '&event'
new   1: select name, parameter1, parameter2, parameter3 from v$event_name where name like 'latch: cache buffers chains'

NAME                                PARAMETER1      PARAMETER2      PARAMETER3
----------------------------------- --------------- --------------- ---------------
latch: cache buffers chains         address         number          tries

Troubleshooting Steps：故障排除步骤
"latch: cache buffers chains" contention is typically encountered because SQL statements read more buffers than they need to and multiple sessions are waiting to read the same block.
通常会遇到"latch：cache buffers chains"争用，因为SQL语句读取的缓冲区比他们需要的多，且多个会话正在等待读取同一个块。

If you have high contention, you need to look at the statements that perform the most buffer gets and then look at their access paths to determine whether these are performing as efficiently as you would like.
如果您的争用率很高，则需要查看执行最多缓冲区的语句，然后查看其访问路径，以确定这些语句的执行效率是否符合您的要求。

Typical solutions are:
    Look for SQL that accesses the blocks in question and determine if the repeated reads are necessary. This may be within a single session or across multiple sessions.查找访问相关块的SQL，并确定重复读取是否必要。 这可能在单个会话内或跨多个会话。
    Check for suboptimal SQL (this is the most common cause of the events) - look at the execution plan for the SQL being run and try to reduce the gets per executions which will minimize the number of blocks being accessed and therefore reduce the chances of multiple sessions contending for the same block.检查次优SQL（这是事件的最常见原因） - 查看正在运行的SQL的执行计划，并尝试减少每次执行的获取次数，这将最大限度地减少正在访问的块的数量，从而减少多次出现的机会 竞争同一块的会话。
    If you can identify a poor SQL and have identified a better plan, you can direct the optimizer to use this plan using the following article:如果您能识别出一个糟糕的SQL，并确定了一个更好的计划，那么可以使用下面的文章来指导优化器使用这个计划

思路一：
查询awr的top sql:

Top 5 Timed Events                                  

SQL ordered by Gets  

Segments by Logical Reads

思路二：
热点块争用  
 1）查找数据库是否存在latch的争用
select sid,event,p1text,p1raw from v$session_wait where event='latch: cache buffers chains';   

2)下面查询查出Top 5 的争用的latch address。
select * from( select CHILD#,ADDR,GETS ,MISSES,SLEEPS from v$latch_children where name = 'cache buffers chains' and misses>0 and sleeps>0 order by 5 desc, 1, 2, 3) where rownum<6;

3)然后利用下面查询找出Hot block。    
 select /*+ RULE */
 e.owner || '.' || e.segment_name segment_name,
 e.extent_id extent#,
 x.dbablk - e.block_id + 1 block#,
 x.tch, /* sometimes tch=0,we need to see tim */
 x.tim,
 l.child#
  from v$latch_children l, x$bh x, dba_extents e
 where x.hladdr = '&ADDR'
   and e.file_id = x.file#
   and x.hladdr = l.addr
   and x.dbablk between e.block_id and e.block_id + e.blocks - 1
 order by x.tch desc;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31397003/viewspace-2149606/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31397003/viewspace-2149606/