Oracle_Event_buffer cache_latch:cache buffer chains

1.defination

• Waits for 'latch: cache buffers chains' indicate that a session is trying to find a database block in the buffer cache. To do this it has to take a latch on the chain of buffers while the scanning takes place. This prevents the linked list of buffers from being changed while it is scanned. 
  
• Possible hot blocks in the buffer cache normally can be identified by a high or rapid increasing wait count on the CACHE BUFFERS CHAINS latch. This latch is acquired when searching for data blocks cached in the buffer cache. The Buffer cache is implemented as a chain of blocks linked together under a hash value. When a buffer is requested the hash value of the chain that this buffer will be in is calculated and that chain is scanned. Each chain is protected by a latch to prevent it from being changed while it is scanned. Contention in this latch can be caused by very heavy access to a single block. This can require the application to be reviewed.
• 此等待事件为在buffer cache中寻找block（内存中的block和数据文件中的block可以认为是等同的），要先去扫描buffer chains，在扫描buffer chains之前先给buffer chains加latch，以防止在扫描buffer chains的时候，buffer chains被修改。

2.parameter

• P1 = Latch address
• P2 = Latch number
• P3 = Tries

• Latch address（为latch在share pool中的地址，p1raw地址排列是有规律的）

  The address of the latch that the process is waiting for. The hexadecimal value of P1 (P1RAW) can be used to determine which latch is waited for thus: 
  

    SELECT name, 'Child '||child#, gets, misses, sleeps
      FROM v$latch_children 
     WHERE addr='&P1RAW' ;
  Each child latch protects one or more buffer hash chains.
  
  

• Latch number

  This is the latch number, but we already known the latch type involved is "cache buffers chains" so this parameter is not really very useful.

• Tries

  This is basically a counter that counts the number of times we tried to get the latch (slow with spinning) and the process had to sleep. See the "Wait Time" notes below.

 3.wait time

When a session waits on latch free it effectively sleeps for a short time then re-tests the latch to see if it is free . If it still cannot be acquired then P3 is incremented and the session waits again. The wait time may increase exponentially and does not include spinning on the latch (active waiting).

 4.finding blockers

Latches are typically held for a very short period of time so unless a process is stuck / hung in an unexpected manner then the actual issue is more likely to be due to one or more DB blocks being heavily competed for by different sessions.

If the P1RAW value of the wait is often the same then it points to a hot block (or a hung process). You can find details of buffer cache blocks covered by a particular child latch using the following SQL (as sysdba):

  SELECT File#, dbablk, class, state 
    FROM x$bh
   WHERE hladdr='&P1RAW'
  ;

If this list is short (3 to 10 buffers) then one of the buffers in this list is probably very 'hot' - ie: suffers from lots of concurrent access attempts. Repeatedly monitoring X$BH for this latch should show which blocks are always there and which are transient.

There is a touch-count column in X$BH in Oracle8i (X$BH.TCH) which can be used to see the HOT buffers. Hot buffers will typically have a high touch count.

 

  5.how to find hot block

• First determine which latch addresses (ADDR) are interesting by examining the number of  sleeps for this latch. The higher the sleep count, the more processes are having to wait for that latch, so the higher the sleeps, the higher the contention. If we are investigating contention, the higher the sleeps the more interesting the latch addresses (ADDR) is. The following select returns latch addresses (ADDR) ordered by sleeps:

1. SELECT child# "cCHILD" ,
     addr "sADDR" ,
     gets "sGETS" ,
     misses "sMISSES" ,
     sleeps "sSLEEPS"
   FROM v$latch_children
   WHERE name = 'cache buffers chains'
   ORDER BY 5,  1,  2,  3;

• Identify the blocks protected under that address .Once the addresses (ADDR) with the highest sleep count have been determined, these latch addresses can be used to get more detail about the blocks currently in the buffer cache protected by this latch（latch保护的应该是buffer chains，不是block）. The following query identifies the blocks protected by a particular ADDR and should be run just after determining the ADDR with the highest sleep count:

1. SQL>  column segment_name format a35
   SELECT  /*+ RULE */
     e.owner  || '.'  || e.segment_name segment_name,
     e.extent_id extent#,
     x.dbablk - e.block_id + 1 block#,
     x.tch,
     l.child#
   FROM sys.v$latch_children l,
     sys.x$bh x,
     sys.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 ;

   SEGMENT_NAME                     EXTENT#      BLOCK#       TCH    CHILD# 
   -------------------------------- ------------ ------------ ------ ---------- 
   SCOTT.EMP_PK                                5          474     17      7,668 
   SCOTT.EMP                                   1          449      2      7,668

• The TCH column identifies the number of times each block has been hit by a SQL statement, so you can use this information to identify a hot block. The higher the value of the TCH column, the more frequent the block is accessed by SQL statements.

Material：https://pan.baidu.com/s/1Q5SQA-OLljY_9iqKoYQaWQ  jtnt

Reference：163424.1 、2098064.1

Question：

• buffer header是什么，在share pool 里还是在buffer cache上？

temp reference：https://blog.csdn.net/haibusuanyun/article/details/17525523

 

SQL> select name from v$event_name where name like '%buffer%';

NAME
----------------------------------------------------------------
Backup: MML obtain a direct buffer
Backup: MML release a direct buffer
Backup: MML query for direct buffers
enq: PW - flush prewarm buffers
latch: cache buffers chains
free buffer waits
buffer read retry
buffer busy waits
gc buffer busy acquire
gc buffer busy release
log buffer space

NAME
----------------------------------------------------------------
gc flushed buffer
LogMiner reader: buffer
PX Deq Credit: need buffer
ges message buffer allocation
buffer exterminate
buffer resize
latch: cache buffers lru chain
latch: cache buffer handles
buffer deadlock
buffer latch
recovery buffer pinned

NAME
----------------------------------------------------------------
block change tracking buffer space
parallel recovery read buffer free
parallel recovery change buffer free
PX Deq Credit: free buffer
latch: parallel query alloc buffer
buffer busy
buffer freelistbusy
buffer rememberlist busy
buffer writeList full
no free buffers
buffer write wait

NAME
----------------------------------------------------------------
buffer invalidation wait
buffer dirty disabled

35 rows selected.

SQL>