Wait Event: Cache buffer chain latch

Wait Event: Cache buffer chain latch

Possible Causes:

• This latch is acquired when searching for data blocks
• Buffer cache is a chain of blocks and each chain is protected by a child latch when it needs to be scanned
• Hot blocks are another common cause of cache buffers chains latch contention. This happens when multiple sessions repeatedly access one or more blocks that are protected by the same child cache buffers chains latch.
• SQL statements with high BUFFER_GETS (logical reads) per EXECUTIONS are the main culprits 
• Multiple concurrent sessions are executing the same inefficient SQL that is going after the same data set

Actions:

• Reducing contention for the cache buffer chains latch will usually require reducing logical I/O rates by tuning and minimizing the I/O requirements of the SQL involved. High I/O rates could be a sign of a hot block (meaning a block highly accessed). 
• Exporting the table, increasing the PCTFREE significantly, and importing the data. This minimizes the number of rows per block, spreading them over many blocks. Of course, this is at the expense of storage and full table scans operations will be slower
• Minimizing the number of records per block in the table
• For indexes, you can rebuild them with higher PCTFREE values, bearing in mind that this may increase the height of the index.
• Consider reducing the block size
• Starting in Oracle9i Database, Oracle supports multiple block sizes. If the current block size is 16K, you may move the table or recreate the index in a tablespace with an 8K block size. This too will negatively impact full table scans operations. Also, variousblock sizes increase management complexity.

Ref:
The default number of hash latches is usually 1024

The number of hash latches can be adjusted by the parameter _DB_BLOCKS_HASH_LATCHES

What are latches and what causes latch contention - 22908.1

gavinsoorma.com