Welcome to OracleGiants.com!     

Oracle 10g DBA & Business Intelligence Resources.

Latch: cache buffer chains

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.

 

Use Xattr CloudFiler™ to dedup, encrypt and move your RMAN backups to the cloud.

Recent Posts

OptionalRegion1
This site recommends Burleson Consulting for DBA Support & Consulting.

We are currently paying for accepted Oracle 10g DBA & Business Intelligence articles, scripts, tips & tricks. Send yours to the editor and we will review within three weeks.

Learn more

Editorial submissions & questions: editor@oraclegiants.com

 

 

This site is moderated by an:
Oracle Certified Professional
Oracle ACE

 

  Classic Car Online - The #1 spot for classic and muscle car enthusiats
2010 OracleGiants.com