latch: cache buffers chains – Oracle wait event

The Buffer cache is implemented as a chain of blocks linked together under a hash value known as cache buffer chains (CBC). When a buffer is requested the hash value of the chain where the buffer is, will be calculated and then that chain is scanned by walking the chain until the buffer is found. Each chain is protected by a latch to prevent it from being changed while it is scanned. Processes need to acquire the child latch to scan the CBC for buffer, other processes would wait until the child latch is released on wait ‘latch: cache buffers chains’.

Contention on the cache buffers chains latch may occur when block headers are accessed very frequently (via logical reads), and these blocks are often referred to as Hot blocks.

Following are the common approaches to mitigate the wait on “latch: cache buffers chains” wait event:-

  • Examine the application to see if the execution of certain DML and SELECT statements can be reorganized to eliminate contention on the object. This may be because individual SQL statements are reading more buffers than they need to or SQL affecting specific buffers does so at the same time.
  • DBWR throughput may have a factor in this as well because this determines how quickly buffers are flushed to disk. If dirty buffers are present then processes requesting those buffers may need to find earlier version meaning that they hold latches longer increasing the chances of contention. If using multiple DBWR’s then increase the number of DBWR’s.
  • Increase the PCTFREE for the table storage parameters via ALTER TABLE or rebuild. This will result in less rows per block such that there is less chance of the same buffers being requested by processes.
  • Consider implementing reverse key indexes (if range scans aren’t commonly used against the segment)
Advertisements

2 thoughts on “latch: cache buffers chains – Oracle wait event

  1. Pingback: Oracle Wait Events | Oracle Database Internal Mechanism

  2. Your explanation are excellent, I always like to read your block. Could you please help by giving realtime example how to diagnose the issue for such type of wait events.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.