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.

Normally, Oracle's hashing algorithm, combined with more than twice the number of chains than buffers, results in a very, very short chain. The only way for a long chain to occur would be for multiple buffers to hash to the same chain. Typically, this is not a problem, but it can occur. And this is the focus of this discussion.