enq: TX - index contention

It’s possible that we see high Index leaf block contention  on index associated with  tables, which are having high concurrency from the application.  This usually happens when the application performs lot of INSERTs and DELETEs  .

The reason for this is the index block splits while inserting a new row into the index. The transactions will have to wait  for  TX lock in mode 4, until the session that is doing the block splits completes the operations

Causes

• Indexes on the tables which are being accessed heavily from the application.
• Indexes on table columns which are having values inserted by a monotonically increasing.
• Heavily deleted tables

Detecting index leaf block contention
There are many ways to find hot indexes

• Check high  "enq: TX – index contention" system waits   from AWR report
• At the same  time  you will see  high split events  in the  Instance  avtivity stats  in the AWR  report
• If yor system is RAC also   you will see  the  following  wait events in the AWR report
  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
• we  can find sql_id ' s  from v$active_session_history
  
  select sql_id,count(*) from v$active_session_history a
  
  where a.sample_time between sysdate - 5/24 and sysdate
  and trim(a.event) like 'enq: TX - index contention%'
  group by sql_id
  order by 2;
• Or we  can  query the segments  from  V$SEGMENT_STATISTICS or from the 'Segments by Row Lock Waits' of the AWR reports.
  
  select * from v$segment_statistics
  where statistic_name ='row lock waits'
  
  and value>0 order by value desc;
• we can  query   dba_hist_enqueue_stat and the stats$enqueuestat  or  v$enqueue_stat       
  select * from  v$enqueue_stat        order  by cum_wait_time;

Solutions 

• Reverse Key  Indexes :Rebuild the as reverse key indexes or hash partition the indexes which are listed in the 'Segments by Row Lock Waits' of the AWR reports.These indexes are excellent for insert performance.  But the downside of it is that, it may affect the performance of index range scans
• Hash partitioned global indexes :When an index is monotonically growing because of a sequence or date key, global hash-partitioned indexes improve performance by spreading out the contention. Thus, hash-partitioned global indexes can improve the performance of indexes in which a small number of leaf blocks in the index have high contention in multi-user OLTP environments
• 
  CACHE size of the sequences: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, which makes it a hot block and potential candidate for a block split. With CACHE SIZE (and probably with NOORDER option), each instance would use start using the sequence keys with a different range reduces the index keys getting insert same set of leaf blocks
• Index  block size:Because the blocksize affects the number of keys within each index block, it follows that the blocksize will have an effect on the structure of the index tree. All else being equal, large 32k blocksizes will have more keys per block, resulting in a flatter index than the same index created in a 2k tablespace. Adjusting the index block size may only produce a small effect and changing the index block size should never be your first choice for reducing index block contention, and it should only be done after the other approaches have been examined.
• Rebuild indexes:If  many rows are  deleted  or  it is an  skewed indes   rebuilding will help  for a  while 

See Also:
All Oracle enqueue waits
Solving Waits on "enq: TM - contention"









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