WAITEVENT:latch:cachebufferschains - 数据库编程

TOP

WAITEVENT:latch:cachebufferschains(一)

2015-07-24 10:22:22 来源: 作者: 【大中小】浏览:0次

关于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 of this latch when needs to be scanned. Contention in 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 or more 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 CPU time 11,422 2.0 log file sync 34,890 1,748 50 0.3 Commit latch free  2,279 774 340 0.1 Other db file parallel write 18,818 768 41 0.1 System I/O ------------------------------------------------------------- --找出逻辑读高sql SQL ordered by Gets DB/Inst: Snaps: 1-2 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total Buffer Gets: 265,126,882 -> Captured SQL account for 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 account for 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.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. 2.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.

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 determine which latch id(ADDR) are interesting by examining the number of sleeps for this latch. The higher the sleep c

首页上一页 1 2 下一页尾页 1/2/2
【大中小】【打印】【繁体】【投稿】【收藏】【推荐】【举报】【评论】【关闭】【返回顶部】
分享到:
上一篇：使用tab自动补全mysql命令	下一篇：Atitit.并发测试解决方案(2)-----..