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;

Trouble shooting latch: cache buffers chains

相關文章