Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

 
 

Latch: cache buffer chains

Article by Rampant Author Brian Carr

The latch: cache buffers chains Oracle metric is used to protect a buffer list in the buffer cache. These latches are used when searching for, adding, or removing a buffer from the buffer cache. Contention on this latch usually means that there is a block that is greatly contended for (known as a hot block).

Recommendation:
To see the exact blocks that experience the "cache buffer chain" waits, you start by finding the object associated with the data block and then see if it is a segment header block. This is a situation that must be caught in the act.
SELECT p1 "file#", p2 "block#", p3 "class#"
FROM v$session_wait
WHERE event = 'cache buffer chains';

Once a common block has been identified use this query to identify the hot object:
SELECT relative_fno, owner, segment_name, segment_type
FROM dba_extents
WHERE file_id = &file AND &block BETWEEN block_id AND block_id + blocks - 1;

The next step is tuning queries that interact with this object. Secondly increasing PCTFREE and allowing for less dense blocks may address the problem, depending on the real root cause, however the best solution is SQL tuning.

 

 

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks