Fix high "cache buffer chain" waits
Oracle Database Tips by Donald Burleson
We have just upgraded our systems to run Oracle Apps
22.214.171.124 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?
The "cache buffer chain" latch wait is normal, but
high values are associated with high simultaneous buffer access,
a freelist shortage on an index or table segment header.
Oracle suggests using the _db_block_hash_buckets
_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:
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.
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
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
the object associated with the data block and then see if it
is a segment header block. For example, start with this
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
header contention under high DML loads, and you may need to
redefine your freelists back to the traditional freelist
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
has this script to locate a hot block:
select /*+ RULE */
e.owner ||'.'|| e.segment_name segment_name,
x.dbablk - e.block_id + 1 block#,
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 segment extension
occurs when the undo segment extension is being
extended or shrunk. The session must wait until the operation on the
undo segment has finished.
sess.sid = 74
sess.sid = swh.sid
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
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!