Detect 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


There are many ways to Fixing Oracle index contention (RAC / NON-RAC ENV.)

There are three techniques that are used to relieve this index contention issue:

       1. Reverse key indexes
       2. Sequences with the cache and noorder options
       3. Using hash partitioned global indexes
       4. Adjusting the index block size


select sid, sql_text
from  v$session s, v$sql q
where    sid in (select sid from v$session where state in ('WAITING')
   and   wait_class != 'Idle'
   and   event='enq: TX - index contention'
   and (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id));