If you performed lot of DMLs (specially Deletes) on a table, you may have lots of empty leaf blocks in the associated indexes on that table. These empty leaf blocks are left in the b-tree structure AND attached to the freelist. On subsequent inserts, the process might need to split a block so it takes the top block off that freelist where the empty leaf blocks were attached and then share the data from the existing block between the old and new blocks.

During this splitting process its possible that Oracle will discover that the block it took from the freelist was also in a position in the index structure that it doesn’t have the required space for splitting but this is only discovered after doing all the work of splitting and relinking the blocks. Oracle then has has to roll back all that work and try another block and If you have done a massive delete its possible that Oracle work through a hundreds of such blocks before finding the one it can use.

The session has to wait until this splitting is performed and the wait event on which it waits is known as “enq: TX – index contention”.

So all together there can be three most probable reasons for sessions to wait on enq: TX – index contention:-

• Indexes on the tables which are being accessed heavily from the application.
• Indexes on table columns which are monotonically growing. In other words, most of the index insertions occur only on the right edge of an index.
• Large data purge has been performed, followed by high concurrent insert

To fix this following solutions can be tried:-

Solution 1: Rebuild the index  as reverse key indexes or hash partition the indexes which are listed in the ‘Segments by Row Lock Waits’ of the AWR reports.

CREATE INDEX <index name> ON <column> REVERSE;

Reverse key indexes are designed to eliminate index hot spots on insert applications.

In some OLTP applications, index insertions happen only at the right edge of the index. This could happen when the index is defined on monotonically increasing  columns. In such situations right edge of the index becomes a hotspot because of contention for index pages, buffers, latches for update, and additional index  maintenance activity, which results in performance degradation and hash method can improve performance of indexes where a small number leaf blocks in the index have high contention in multiuser OLTP environment

Solution 2: Consider increasing the CACHE size of the sequences.

alter sequence <owner>.<seq name> cache <required value>;

When we use monotonically increasing sequences for populating column values, the leaf block which is having “high sequence key” will be changing with every insert and thus makes it a potential candidate for a block split. With CACHE SIZE (and probably with NOORDER option), each instance would start using the sequence keys with a different range and thus would reduce probability of a block split.

Solution 3: Rebuild or shrink associated index after huge amount of data purge.

If there is a huge amount of data purge (delete) has been done, rebuild or shrink associated index should help to reduce the wait via alter index rebuild or alter index shrink command.

Solution 4: Increase PCT_FREE for the index.

A higher PCT_FREE would help to avoid ITL contention on index blocks. Contention for ITL occurs when all the available ITLs within a block are currently in use and there is not enough space in the PCT_FREE area for Oracle to dynamically allocate a new ITL slot.