Realistically though, one solution to CBC latch contention is to increase the number of hash buckets. While this will decrease the average chain length, if one particular chain is very long and popular, this solution will not improve performance. Furthermore, Oracle creates a large number of chains by default, so it is unlikely that adding chains will create the performance benefit you seek. But it is a valid approach and should be considered.

Although the problem of long chains is very rare, if it does occur, it is very serious indeed. Understanding how this could happen not only helps you solve the problem if it should arise, but also allows a much deeper understanding of the CBCs, latching, undo, and read consistency. It even touches on RAC systems.

Long chains present a very challenging problem. First, hashing structures are fast because there is little or no scanning, so a long chain quickly diminishes the benefit of using a hashing algorithm. Second, a scanning process must possess a CBC latch-and not just any CBC latch, but the CBC latch protecting the specific chain of interest. A longer chain means that the latch will be held longer and also that more CPU will be burned while scanning the list. Plus, since the latch is being held longer, there is an increased likelihood that another process will be contending for the latch. That contending process is also consuming CPU while spinning and posting a wait event while sleeping. But the problem is even deeper than this.