WAITEVENT:latch:cachebufferschains

关于CACHE BUFFERS CHAINS描述 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

关于CACHE BUFFERS CHAINS描述

CACHE BUFFERS CHAINS latch is acquired when searching <code class="bash keyword">for <code class="bash plain">data blocks cached <code class="bash keyword">in <code class="bash plain">the buffer cache. <code class="bash plain">Since the Buffer cache is implemented as a <code class="bash functions">sum <code class="bash plain">of chains of blocks, each of those chains is protected <code class="bash plain">by a child of this latch when needs to be scanned. Contention <code class="bash keyword">in <code class="bash plain">this latch can be caused by very heavy <code class="bash plain">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. <code class="bash plain">This happens when multiple sessions repeatedly access one or <code class="bash functions">more <code class="bash plain">blocks that are protected <code class="bash plain">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.

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

找出热点对象 <code class="bash plain">First determine <code class="bash functions">which <code class="bash plain">latch <code class="bash functions">id<code class="bash plain">(ADDR) are interesting by examining the number of <code class="bash plain">sleeps <code class="bash keyword">for <code class="bash plain">this latch. The higher the <code class="bash functions">sleep <code class="bash plain">count, the <code class="bash functions">more <code class="bash plain">interesting the <code class="bash plain">latch <code class="bash functions">id<code class="bash plain">(ADDR) is: <code class="bash plain">SQL> <code class="bash functions">select <code class="bash plain">CHILD<code class="bash comments"># "cCHILD" <code class="bash spaces"> <code class="bash plain">, ADDR <code class="bash string">"sADDR" <code class="bash spaces"> <code class="bash plain">, GETS <code class="bash string">"sGETS" <code class="bash spaces"> <code class="bash plain">, MISSES <code class="bash string">"sMISSES" <code class="bash spaces"> <code class="bash plain">, SLEEPS <code class="bash string">"sSLEEPS" <code class="bash spaces"> <code class="bash plain">from <code class="bash functions">v<code class="bash plain">$latch_children <code class="bash spaces"> <code class="bash plain">where name = <code class="bash string">'cache buffers chains' <code class="bash spaces"> <code class="bash plain">order by 5, 1, 2, 3; <code class="bash plain">Run the above query a few <code class="bash functions">times <code class="bash plain">to to establish the <code class="bash functions">id<code class="bash plain">(ADDR) that has the most <code class="bash plain">consistent amount of sleeps. Once the <code class="bash functions">id<code class="bash plain">(ADDR) with the highest <code class="bash functions">sleep <code class="bash plain">count is found <code class="bash keyword">then <code class="bash plain">this latch address can be used to get <code class="bash functions">more <code class="bash plain">details about the blocks <code class="bash plain">currently <code class="bash keyword">in <code class="bash plain">the buffer cache protected by this latch. <code class="bash plain">The query below should be run just after determining the ADDR with <code class="bash plain">the highest <code class="bash functions">sleep <code class="bash plain">count. <code class="bash plain">SQL> column segment_name <code class="bash functions">format <code class="bash plain">a35 <code class="bash spaces"> <code class="bash functions">select <code class="bash plain">/*+ RULE */ <code class="bash spaces"> <code class="bash plain">e.owner ||<code class="bash string">'.'<code class="bash plain">|| e.segment_name segment_name, <code class="bash spaces"> <code class="bash plain">e.extent_id extent<code class="bash comments">#, <code class="bash spaces"> <code class="bash plain">x.dbablk - e.block_id + 1 block<code class="bash comments">#, <code class="bash spaces"> <code class="bash plain">x.tch, <code class="bash spaces"> <code class="bash plain">l.child<code class="bash comments"># <code class="bash spaces"> <code class="bash plain">from <code class="bash spaces"> <code class="bash plain">sys.<code class="bash functions">v<code class="bash plain">$latch_children l, <code class="bash spaces"> <code class="bash plain">sys.x$bh x, <code class="bash spaces"> <code class="bash plain">sys.dba_extents e <code class="bash spaces"> <code class="bash plain">where <code class="bash spaces"> <code class="bash plain">x.hladdr = <code class="bash string">'&ADDR' <code class="bash plain">and <code class="bash spaces"> <code class="bash plain">e.file_id = x.<code class="bash functions">file<code class="bash comments"># and <code class="bash spaces"> <code class="bash plain">x.hladdr = l.addr and <code class="bash spaces"> <code class="bash plain">x.dbablk between e.block_id and e.block_id + e.blocks -1 <code class="bash spaces"> <code class="bash plain">order by x.tch desc ; <code class="bash plain">Example of the output : <code class="bash plain">SEGMENT_NAME EXTENT<code class="bash comments"># BLOCK# TCH CHILD# <code class="bash plain">-------------------------------- ------------ ------------ ------ ---------- <code class="bash plain">SCOTT.EMP_PK 5 474 17 7,668 <code class="bash plain">SCOTT.EMP 1 449 2 7,668 <code class="bash plain">Depending on the TCH column (The number of <code class="bash functions">times <code class="bash plain">the block is hit by a SQL <code class="bash plain">statement), you can identify a hot block. The higher the value of the TCH column, <code class="bash plain">the <code class="bash functions">more <code class="bash plain">frequent the block is accessed by SQL statements.

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