Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Fix high "cache buffer chain" waits

Oracle Database Tips by Donald Burleson

 

 

Question: We have just upgraded our systems to run Oracle Apps 11.5.10.2 on Solaris 64 bit on Oracle 10.2.0.2.

We have a 9 gig SGA and we're having problems 80/20 and 50/20 "cache buffer chain" waits running 3 concurrent payroll schemes.

 

How to I reduce these "cache buffer chain" latch waits?
 

Answer: The "cache buffer chain" latch wait is normal, but high values are associated with high simultaneous buffer access, similar to a freelist shortage on an index or table segment header.

 

Oracle suggests using the _db_block_hash_buckets and
_db_block_hash_latches undocumented parameters have been suggested as a remedy, but always check with MOSC before using any undocumented parameters.  To see the "cache buffer chain" waits:

select
   count(*)    child_count,
   sum(gets)   sum_gets,
   sum(misses) sum_misses,
   sum(sleeps) sum_sleeps
from
   v$latch_children
where
   name = 'cache buffers chains';

Cache Buffer Chain Latch Family 

The first main type of latch that will be detailed for Oracle is called the buffer cache latch. The buffer cache latch family consists of two types of latches: the cache buffers chain latchand the other is the cache buffers LRU chain latch. First, take a look at the cache buffers chain latch.

Cache buffers chain latches are acquired at the moment in time when a data block within the Oracle buffer cache is accessed by a process within Oracle. Usually latch contention for these buffer caches is due to poor disk I/O configuration. Reducing contention with these latches involves tuning the logical I/O for the associated SQL statements as well as the disk subsystem.

Another factor for latch contention with buffers chain latches could possibly be hot block contention. Oracle Metalink Note # 163424.1 has some useful tips on tuning and identifying hot blocks within the Oracle database environment.

The other buffer cache latch type is the cache buffers LRU chain latch. Whenever a new block enters the Oracle buffer cache within the SGA, this latch is acquired to allow block management in the Oracle SGA.

Also, the latch is acquired when buffers are written back to disk such as when a scan is performed to move the LRU or least recently used chain of dirty blocks to flush out to disk from the buffer cache.

 

 

Finding the hot blocks

 

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.  For example, start with this query:

select
   P1
from
   v$session_wait
where
   event = 'cache buffer chains';

 

Next, use these directions for using P1 to find the exact data block.

 

If you are using Automatic Segment Storage Management (ASSM), you can see segment header contention under high DML loads, and you may need to redefine your freelists back to the traditional freelist structures.

 

If you are not using ASSM (bitmap freelists), you can easily relieve the buffer chain latch wait by adding freelists, up to your high-water mark of concurrent DML on the object:

 

alter index hot_idx freelists 4;

 

MOSC has this script to locate a hot block:

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;

 

Also, check out if you have undo latch contention The undo segment extension Oracle metric occurs when the undo segment extension is being extended or shrunk. The session must wait until the operation on the undo segment has finished.
 
select
   swh.seq#,
   sess.sid,
   sess.username username,
   swh.event     event,
   swh.p1,
   swh.p2
from
   v$session               sess,
   v$session_wait_history  swh
where
   sess.sid = 74
and
   sess.sid = swh.sid
order by
   swh.seq#;
 
 
SEQ# SID USERNAME EVENT                       P1         P2
---- --- -------- --------------------------- ---------- ----------
   1  74 PCS      buffer busy waits                    3      21277
   2  74 PCS      latch: cache buffers chains 1556332118        172
   3  74 PCS      latch: cache buffers chains 1556332118        172
   4  74 PCS      buffer busy waits                    4        155

Take the p1raw column from the v$session_wait for the waiting session.

In sum, most buffer cache waits can be fixed with additional freelists, but there are exceptions.  For a full treatment of fixing latch contention, see my book "Oracle Tuning: The Definitive Reference",

   
Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training
 
 

 


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational