Database Reference
In-Depth Information
Root block
Branch
blocks
1000
1001
1002
1003
Figure 6-1.
Leaf block contention
If insert concurrency into the employee table increases, contention on the current rightmost leaf block will
increase sharply. Contention on the rightmost leaf block is exacerbated because the leaf block splits when the block
becomes full. In a single-instance database, leaf block contention will result in waits for events such as 'buffer busy'
waits, 'cache buffers chain' latch waits, and so on. In a RAC database, the current rightmost leaf block must be
transferred between the instances, leading to an increase in global cache transfer. Further, sessions will suffer from wait
events such as gc buffer busy acquire and gc buffer busy release as global cache locks are acquired and released. The
effect of waiting for these global events in a RAC database will be an order of magnitude larger than the single-instance
waits, possibly leading to an unusable application. This phenomenon is called
right-hand index growth contention
.
Figure
6-2
shows the output from gv$session and the effect of leaf block contention in a busy database.
Thousands of sessions were stuck waiting for gc buffer busy event waits due to index leaf block contention. Waits for
buffer busy wait events and ITL contention events are also shown in the output.
Figure 6-2.
Events in a busy database
