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)