About CACHEBUFFERSCHAINS Description Examples. SincetheBuffercacheisimplementedasasumofchainsofblocks, eachofthosechainsisprotectedbyachild
About cache buffers chains description cache buffers chains latch is acquired when searching for data blocks cached in the buffer cache. since the Buffer cache is implemented as a sum of chains of blocks, each of those chains is protected by a child
Description of 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 protectedby a child of this latch when needs to be scanned. Contentionin this latch can be caused by very heavyaccess to a single block. This can require the application to be reviewed. |
Reason for 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 protectedby the same child cache buffers chains latch. |
Cache buffers chains Processing Method
1) Examine the application to see if the execution of certain DML and SELECT statements can be reorganized to eliminate contention on the object.
The solution is as follows:-- Determine latch: cache buffers chains wait through the reportTop 5 Timed Events Avg % Total~~~~~~~~~~~~~~~~~~ Wait CallEvent Waits Time (s) (MS) Time Wait Class---------------------------------------------------------------------------Latch: cache buffers chains 74,642 35,421 475 6.1 ConcurrencCPUTime11,422 2.0LogFileSync34,890 1,748 50 0.3 CommitLatchFree2,279 774 340 0.1 OtherDbFileParallel write 18,818 768 41 0.1 System I/O--------------------------------------------------------------- Find the logic read high SQLSQL ordered by Gets DB/Inst: Snaps: 1-2-> Resources reportedForPL/SQLCode nodes des the resources used by all SQLStatements called by the code.-> Total Buffer Gets: 265,126,882-> Captured SQL accountFor99.8% of Total Gets CPU ElapsedBuffer Gets Executions per Exec % Total Time (s) SQL Id-------------------------------------------------------------------------- 256,763,367 19,052 13,477.0 96.8################ A9nchgksux6x2Module: JDBC Thin ClientSELECT * from sales .... 1,974,516 987,056 2.0 0.7 80.31 110.94 ct6xwvwg3w0bvSelect count (*) from orders ....-- Logical read large objectSegments by Logical Reads-& Gt; Total Logical Reads: 265,126,882-> Captured Segments accountFor98.5% of Total Tablespace Subobject Obj. LogicalOwner Name Object Name Type Reads % Total--------------------------------------------------------------------------Dmsuser users sales table 212,206,208 80.04Dmsuser users SALES_PK INDEX 44,369,264 16.74Dmsuser users SYS_C0012345 INDEX 1,982,592. 75Dmsuser users ORDERS_PK INDEX 842,304. 32Dmsuser users invoices table 147,488. 06 -------------------------------------------------------------Solution: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.
Find hotspot objectsFirst determinewhich latch id(ADDR) are interesting by examining the number ofsleepsfor this latch. The higher thesleep count, themore interesting thelatchid(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 mostconsistent amount of sleeps. Once theid(ADDR) with the highestsleep count is foundthenthis latch address can be used to get moredetails about the blockscurrentlyin the buffer cache protected by this latch.The query below should be run just after determining the ADDR withthe 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,668SCOTT.EMP 1 449 2 7,668 Depending on the TCH column (The number oftimes the block is hit by a SQLstatement), 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) |
