Index Leaf Block contention Tuning

Index block contention is very common in busy databases and it’s especially common on tables that have monotonically increasing key values.

In a high-DML system, index management activities are constantly taking place (b-tree splitting and spawning) and these events can cause transient waits, but far and away, the greatest cause of index contention in an Oracle environment is the “high key” issue. Oracle b-tree indexes are “right-handed” and the right-hand leafs of the b-tree contain the highest key in the lowest tree level.

Index leaf node contention happens when rows are inserted based on a user generated key (i.e. a sequence) and because the sequence key is always the high order key, each insert of a low-level index tree node must propagate upwards to the high-key indicators in the b-tree index.

Detecting index leaf block contention

Index block contention can be tricky to diagnose and repair, especially since Oracle does not have many direct tuning knobs for tuning for contention.

The wait “enq: TX – index contention” indicates a wait on an index, and in a RAC environment you might also see “gc” (global cache) waits on indexes:

·         gc buffer busy waits on Index Branch Blocks

·         gc buffer busy waits on Index Leaf Blocks

·         gc current block busy on Remote Undo Headers

·         gc current split

·         gcs ast xid

·         gcs refuse xid