首页 > 代码库 > WAIT EVENT: latch: cache buffers chains

WAIT EVENT: latch: cache buffers chains

2024-07-21 22:03:52   218人阅读

关于CACHE BUFFERS CHAINS描述

CACHE BUFFERS CHAINS latch is acquired when searchingfor data blocks cachedin the buffer cache.
Since the Buffer cache is implemented as asum of chains of blocks, each of those chains is protected
by a child of this latch when needs to be scanned. Contentionin this latch can be caused by very heavy
access to a single block. This can require the application to be reviewed.

产生CACHE BUFFERS CHAINS原因

The main cause of the cache buffers chains latch contention is usually a hot block issue.
This happens when multiple sessions repeatedly access one ormore blocks that are protected
by the same child cache buffers chains latch.

CACHE BUFFERS CHAINS 处理方法
1) Examine the application to see if the execution of certain DML and SELECT statements can be reorganized to eliminate contention on the object.

处理方法如下:
--通过报告确定latch: cache buffers chains 等待
 
Top 5 Timed Events                                      Avg    %Total
~~~~~~~~~~~~~~~~~~                                      wait   Call
Event                          Waits        Time (s)    (ms)   Time   Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
latch: cache buffers chains          74,642      35,421    475    6.1 Concurrenc
CPUtime                                        11,422           2.0
logfile sync                       34,890       1,748     50    0.3 Commit
latchfree                            2,279         774    340    0.1 Other
dbfile parallel write               18,818         768     41    0.1 System I/O
-------------------------------------------------------------
 
--找出逻辑读高sql
SQL ordered by Gets         DB/Inst:  Snaps: 1-2
-> Resources reportedfor PL/SQLcode includes the resources used by all SQL
statements called by the code.
-> Total Buffer Gets:   265,126,882
-> Captured SQL accountfor   99.8% of Total
 
 
                            Gets                CPU      Elapsed
Buffer Gets    Executions   per Exec     %Total Time (s) Time (s)  SQL Id
-------------- ------------ ------------ ------ -------- --------- -------------
   256,763,367       19,052     13,477.0   96.8######## ######### a9nchgksux6x2
Module: JDBC Thin Client
SELECT * FROM SALES ....
 
     1,974,516      987,056          2.0    0.7    80.31    110.94 ct6xwvwg3w0bv
SELECT COUNT(*) FROM ORDERS ....
 
--逻辑读大对象
Segments by Logical Reads          
-> Total Logical Reads:     265,126,882
-> Captured Segments accountfor   98.5% of Total
 
           Tablespace                      Subobject  Obj.       Logical
Owner         Name    Object Name            Name     Type         Reads  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
DMSUSER    USERS      SALES                           TABLE  212,206,208   80.04
DMSUSER    USERS      SALES_PK                        INDEX   44,369,264   16.74
DMSUSER    USERS      SYS_C0012345                    INDEX    1,982,592     .75
DMSUSER    USERS      ORDERS_PK                       INDEX      842,304     .32
DMSUSER    USERS      INVOICES                        TABLE      147,488     .06
          -------------------------------------------------------------
处理思路：
1.Lookfor SQL that accesses the blocksin question and determineif the repeated reads are necessary.
  This may be within a single session or across multiple sessions.
 
2.Checkfor suboptimal SQL (this is the most common cause of the events) 
 lookat the execution plan forthe SQL being run and try to reduce the
 gets per executionswhich will minimize the number of blocks being accessed
 and therefore reduce the chances of multiple sessions contendingfor the same block.

Note:1342917.1 Troubleshooting ‘latch: cache buffers chains’ Wait Contention

2) Decrease the buffer cache -although this may only help in a small amount of cases.

3) DBWR throughput may have a factor in this as well.If using multiple DBWR’s then increase the number of DBWR’s.

4) Increase the PCTFREE for the table storage parameters via ALTER TABLE or rebuild. This will result in less rows per block.

找出热点对象
First determinewhich latch id(ADDR) are interesting by examining the number of
sleepsfor this latch. The higher thesleep count, themore interesting the
latchid(ADDR) is:
 
SQL>select CHILD#  "cCHILD"
     ,      ADDR   "sADDR"
     ,      GETS   "sGETS"
     ,      MISSES "sMISSES"
     ,      SLEEPS "sSLEEPS"
     fromv$latch_children
     where name =‘cache buffers chains‘
     order by 5, 1, 2, 3;
 
Run the above query a fewtimes to to establish theid(ADDR) that has the most
consistent amount of sleeps. Once theid(ADDR) with the highestsleep count is found
thenthis latch address can be used to get moredetails about the blocks
currentlyin the buffer cache protected by this latch.
The query below should be run just after determining the ADDR with
the highestsleep count.
 
SQL> column segment_nameformat 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 ;
 
Example of the output :
SEGMENT_NAME                     EXTENT#      BLOCK#       TCH    CHILD#
-------------------------------- ------------ ------------ ------ ----------
SCOTT.EMP_PK                       5            474          17     7,668
SCOTT.EMP                          1            449           2     7,668
 
Depending on the TCH column (The number oftimes the block is hit by a SQL
statement), you can identify a hot block. The higher the value of the TCH column,
themore frequent the block is accessed by SQL statements.

5) Consider implementing reverse key indexes (if range scans aren’t commonly used against the segment)

WAIT EVENT: latch: cache buffers chains


联系
我们