'enq: TX - index contention' Waits in a RAC Environment. [ID 873243.1]

該等待是由於索引塊分裂引起，metalink的解決方案如下

Applies to:

Goal

Solution

某些索引被大量的訪問(大量的insert 或 delete)

Identifying the Hot index:

The indexes which are having contention can be identified from the AWR reports taken during the time of the issue.

Top 5 Timed Events:

Event                       Waits      Time(s)   Avg Wait(ms)  % Total Call  Time Wait Class 
en: TX - index contention   89,350     40,991    459           63.3          Concurrency 
db file sequential read     1,458,288  12,562    9             19.4          User I/O 
CPU time                               5,352                   8.3   

Instance Activity Stats:

Statistic                Total     per Second    per Trans 
branch node splits       945       0.26          0.00 
leaf node 90-10 splits   1,670     0.46          0.00 
leaf node splits         35,603    9.85          0.05                          

And the objects can be found either from V$SEGMENT_STATISTICS or from the 'Segments by Row Lock Waits' of the AWR reports.

 透過AWR等位索引

Solutions:

Solution here is to tune the indexes avoid heavy access on a few set of blocks. 

Following are the options we could try:

o        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

From the Performance Tuning Guide - 

Reverse key indexes are designed to eliminate index hot spots on insert applications.  These indexes are excellent for insert performance.  But the downside of it is that, it may affect the performance of index range scans. 

The hash method can improve performance of indexes where a small number leaf blocks in the index have high contention in multiuser OLTP environment. 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.

Its recommended to test the application performance,  after rebuilding the indexes as  reverse key or hash partitioned.

o        Consider increasing the 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.

解決方案：

  總體思路是減少索引熱快

 1 建立反鍵索引 

 2  hash索引分析或  (改成local索引 如果能分散索引值)

 3 增加序列 cache size 

注意1和2 將影響索引範圍掃描