This discussion is archived
3 Replies Latest reply: Apr 27, 2012 6:39 AM by 726175 RSS

enq: TX - index contention for rac

726175 Explorer
Currently Being Moderated
Hello All
System configuration is 2 node 11gR2 Rac used by 11.5.10.2 ERP application on RHEL 5

Faced index contention for insert on large table with 27 million row .
we resolved this after purging data to reduce rows to 4 million and rebuild indexes .
i was against the approach as keeping mind that B tree indexes are auto balanced need not rebuold

i have already seen Joel's Presentation and JLewis comments on this wait event
Index stats before purge :

SQL> select NAME,HEIGHT,BLOCKS,LF_ROWS,LF_BLKS ,LF_BLK_LEN,BR_BLKS,DISTI
NCT_KEYS ,BTREE_SPACE,USED_SPACE ,PCT_USED  from index_stats;

NAME                               HEIGHT     BLOCKS    LF_ROWS    LF_BLKS LF_BLK_LEN    BR_BLKS DISTINCT_KEYS BTREE_SPACE USED_SPACE   PCT_USED
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ------------- ----------- ---------- ----------
OE_PROCESSING_MSGS_TL_U1                3     151040   27108882     149878       4276        422      27108882   644266144  516301179         81


Index stats After purge :


SQL> select NAME,HEIGHT,BLOCKS,LF_ROWS,LF_BLKS ,LF_BLK_LEN,BR_BLKS,DISTINCT_KEYS ,BTREE_SPACE,USED_SPACE ,PCT_USED  from index_stats;


NAME                               HEIGHT     BLOCKS    LF_ROWS    LF_BLKS LF_BLK_LEN    BR_BLKS DISTINCT_KEYS BTREE_SPACE USED_SPACE   PCT_USED
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ------------- ----------- ---------- ----------
OE_PROCESSING_MSGS_TL_U1                3      14816     4763902     14556    5644         34        4763902    82618912    90654959           110


ASH data before data purge
-----------------------

Top User Events                   DB/Inst: PROD/PROD1  (Apr 04 12:00 to 13:00)

                                                               Avg Active
Event                               Event Class        % Event   Sessions
----------------------------------- --------------- ---------- ----------
db file sequential read             User I/O             32.36       9.69
read by other session               User I/O             19.43       5.82
enq: TX - index contention          Concurrency          11.29       3.38
enq: TX - row lock contention       Application          10.78       3.23
CPU + Wait for CPU                  CPU                   7.32       2.19
          -------------------------------------------------------------



Top Service/Module                DB/Inst: PROD/PROD1  (Apr 04 12:00 to 13:00)

Service        Module                   % Activity Action               % Action
-------------- ------------------------ ---------- ------------------ ----------
PROD           NPIWIPLR                      23.90 Concurrent Request      23.90
               OMRSVORD                       9.44 Concurrent Request       9.44
               NPIIMGION                      8.65 Concurrent Request       8.65
               OEOIMP                         4.53 Concurrent Request       4.53
               OEXOEORD                       3.83 FRM:B82562:NPI Cus       1.09
          -------------------------------------------------------------


Top User Events                   DB/Inst: PROD/PROD2  (Apr 04 12:00 to 13:00)

                                                               Avg Active
Event                               Event Class        % Event   Sessions
----------------------------------- --------------- ---------- ----------
db file sequential read             User I/O             30.41       5.62
CPU + Wait for CPU                  CPU                  22.40       4.14
enq: TX - row lock contention       Application          13.71       2.53
enq: TX - index contention          Concurrency           8.24       1.52
read by other session               User I/O              7.80       1.44
          -------------------------------------------------------------

Service        Module                   % Activity Action               % Action
-------------- ------------------------ ---------- ------------------ ----------
PROD           NPIWIPLR                      10.21 Concurrent Request      10.21
               OEXOIMPT                       9.60 FRM:B60221:NPI Cus       4.31
                                                   FRM:N11217:NPI Cus       2.75
                                                   FRM:N13737:NPI Ord       2.54
               OEXOEORD                       8.78 FRM:N11247:NPI Cus       3.80
                                                   FRM:C35809:NPI Cus       1.34
                                                   FRM:N11218:NPI Cus       1.20
               NPIIMGION                      6.67 Concurrent Request       6.67
               NPISLIM                        5.41 Concurrent Request       5.41
          -------------------------------------------------------------
Questions

1) Although this seems to unique index it doesn't use sequence but the contention is there ?
2) After rebuild we see pct_used > 110 , What situation it would happen ?
3) Earlier usage was 81% , is it higher and that would lead to node splits for large indsddexes ?

Regards
KG
  • 1. Re: enq: TX - index contention for rac
    jgarry Guru
    Currently Being Moderated
    KG wrote:
    Hello All
    System configuration is 2 node 11gR2 Rac used by 11.5.10.2 ERP application on RHEL 5

    Faced index contention for insert on large table with 27 million row .
    we resolved this after purging data to reduce rows to 4 million and rebuild indexes .
    i was against the approach as keeping mind that B tree indexes are auto balanced need not rebuold

    i have already seen Joel's Presentation and JLewis comments on this wait event
    Index stats before purge :
    
    SQL> select NAME,HEIGHT,BLOCKS,LF_ROWS,LF_BLKS ,LF_BLK_LEN,BR_BLKS,DISTI
    NCT_KEYS ,BTREE_SPACE,USED_SPACE ,PCT_USED  from index_stats;
    
    NAME                               HEIGHT     BLOCKS    LF_ROWS    LF_BLKS LF_BLK_LEN    BR_BLKS DISTINCT_KEYS BTREE_SPACE USED_SPACE   PCT_USED
    ------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ------------- ----------- ---------- ----------
    OE_PROCESSING_MSGS_TL_U1                3     151040   27108882     149878       4276        422      27108882   644266144  516301179         81
    
    
    Index stats After purge :
    
    
    SQL> select NAME,HEIGHT,BLOCKS,LF_ROWS,LF_BLKS ,LF_BLK_LEN,BR_BLKS,DISTINCT_KEYS ,BTREE_SPACE,USED_SPACE ,PCT_USED  from index_stats;
    
    
    NAME                               HEIGHT     BLOCKS    LF_ROWS    LF_BLKS LF_BLK_LEN    BR_BLKS DISTINCT_KEYS BTREE_SPACE USED_SPACE   PCT_USED
    ------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ------------- ----------- ---------- ----------
    OE_PROCESSING_MSGS_TL_U1                3      14816     4763902     14556    5644         34        4763902    82618912    90654959           110
    
    
    ASH data before data purge
    -----------------------
    
    Top User Events                   DB/Inst: PROD/PROD1  (Apr 04 12:00 to 13:00)
    
    Avg Active
    Event                               Event Class        % Event   Sessions
    ----------------------------------- --------------- ---------- ----------
    db file sequential read             User I/O             32.36       9.69
    read by other session               User I/O             19.43       5.82
    enq: TX - index contention          Concurrency          11.29       3.38
    enq: TX - row lock contention       Application          10.78       3.23
    CPU + Wait for CPU                  CPU                   7.32       2.19
    -------------------------------------------------------------
    
    
    
    Top Service/Module                DB/Inst: PROD/PROD1  (Apr 04 12:00 to 13:00)
    
    Service        Module                   % Activity Action               % Action
    -------------- ------------------------ ---------- ------------------ ----------
    PROD           NPIWIPLR                      23.90 Concurrent Request      23.90
    OMRSVORD                       9.44 Concurrent Request       9.44
    NPIIMGION                      8.65 Concurrent Request       8.65
    OEOIMP                         4.53 Concurrent Request       4.53
    OEXOEORD                       3.83 FRM:B82562:NPI Cus       1.09
    -------------------------------------------------------------
    
    
    Top User Events                   DB/Inst: PROD/PROD2  (Apr 04 12:00 to 13:00)
    
    Avg Active
    Event                               Event Class        % Event   Sessions
    ----------------------------------- --------------- ---------- ----------
    db file sequential read             User I/O             30.41       5.62
    CPU + Wait for CPU                  CPU                  22.40       4.14
    enq: TX - row lock contention       Application          13.71       2.53
    enq: TX - index contention          Concurrency           8.24       1.52
    read by other session               User I/O              7.80       1.44
    -------------------------------------------------------------
    
    Service        Module                   % Activity Action               % Action
    -------------- ------------------------ ---------- ------------------ ----------
    PROD           NPIWIPLR                      10.21 Concurrent Request      10.21
    OEXOIMPT                       9.60 FRM:B60221:NPI Cus       4.31
    FRM:N11217:NPI Cus       2.75
    FRM:N13737:NPI Ord       2.54
    OEXOEORD                       8.78 FRM:N11247:NPI Cus       3.80
    FRM:C35809:NPI Cus       1.34
    FRM:N11218:NPI Cus       1.20
    NPIIMGION                      6.67 Concurrent Request       6.67
    NPISLIM                        5.41 Concurrent Request       5.41
    -------------------------------------------------------------
    Questions

    1) Although this seems to unique index it doesn't use sequence but the contention is there ?
    You've condensed down the wasted space in the index, but your app still has to get the same information. So you've traded cpu waits with read by other session waits, but haven't affected TX waits at all. I would guess that is because the TX wait is app dependent. I'm not sure what you mean by sequence.
    2) After rebuild we see pct_used > 110 , What situation it would happen ?
    Not quite getting what you are saying. Edit: oops, posted too soon. That must be some bug or obsolete stats, see http://richardfoote.wordpress.com/category/index-shrink/
    3) Earlier usage was 81% , is it higher and that would lead to node splits for large indsddexes ?
    I don't trust the 110.
    >
    Regards
    KG
    Edited by: jgarry on Apr 26, 2012 11:22 AM
  • 2. Re: enq: TX - index contention for rac
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    KG wrote:

    Faced index contention for insert on large table with 27 million row .
    we resolved this after purging data to reduce rows to 4 million and rebuild indexes .
    i was against the approach as keeping mind that B tree indexes are auto balanced need not rebuold
    First point to note - if you delete 23M rows from a table with 27 million rows, it probably makes sense to rebuild the table and indexes to reclaim the empty space, unless you're expecting another 20+Million rows to be arriviing the near future and re-using the space very soon. In fact, you might want to use the dbms_redefinition package to extract the data you want to keep rather than doing a delete; alternatively you might actually marked as unused as many indexes on the table as you can get away with before deleteing the data and (optionally) doing a shrink space.

    >
    Index stats before purge :
    SQL> select NAME,HEIGHT,BLOCKS,LF_ROWS,LF_BLKS ,LF_BLK_LEN,BR_BLKS,DISTI
    NCT_KEYS ,BTREE_SPACE,USED_SPACE ,PCT_USED  from index_stats;
    
    NAME                               HEIGHT     BLOCKS    LF_ROWS    LF_BLKS LF_BLK_LEN    BR_BLKS DISTINCT_KEYS BTREE_SPACE USED_SPACE   PCT_USED
    ------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ------------- ----------- ---------- ----------
    OE_PROCESSING_MSGS_TL_U1                3     151040   27108882     149878       4276        422      27108882   644266144  516301179         81
    
    
    Index stats After purge :
    
    
    SQL> select NAME,HEIGHT,BLOCKS,LF_ROWS,LF_BLKS ,LF_BLK_LEN,BR_BLKS,DISTINCT_KEYS ,BTREE_SPACE,USED_SPACE ,PCT_USED  from index_stats;
    
    
    NAME                               HEIGHT     BLOCKS    LF_ROWS    LF_BLKS LF_BLK_LEN    BR_BLKS DISTINCT_KEYS BTREE_SPACE USED_SPACE   PCT_USED
    ------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ------------- ----------- ---------- ----------
    OE_PROCESSING_MSGS_TL_U1                3      14816     4763902     14556    5644         34        4763902    82618912    90654959           110
    1) Although this seems to unique index it doesn't use sequence but the contention is there ?
    2) After rebuild we see pct_used > 110 , What situation it would happen ?
    3) Earlier usage was 81% , is it higher and that would lead to node splits for large indexes ?
    One of the comments in one of the blogs I wrote about indexes highlights a key problem here: http://jonathanlewis.wordpress.com/2009/07/20/index-quiz-1/#comment-33897
    Note the LF_BLK_LEN in both cases - it is well below the 8000 bytes that you typically get for a btree index built in an 8KB block size. This is because the ITL list in some of the leaf blocks has grown to handle concurrent changes to the blocks, and the ANALZYE command has taken the largest ITL list as typical of ALL the blocks in the index. The 81% and 110% are a side effect of this because Oracle has taken the used_space and the number of lf_blks * lf_blk_len to calculate the percenage: e.g. 90654959 / (5644 * 14556) = 1.1035

    Either you created the indexes with a very large INITRANS, or a number of leaf blocks have already been subject to concurrent insert pressure and have hit the "ITL Explosion" bug, wasting a lot of ITL entries that cannot be reclaimed without some sort of index maintenance.

    You've said that the index is not based on a sequence - but is it based on some form of monotonic increasing value (such as a timestamp), you don't need a sequence to hit the problem you simply need values that keep increasing. The problem is base bug number 8767925, fixed in 12.1 with backports available to some earlier versions.

    Regards
    Jonathan Lewis
  • 3. Re: enq: TX - index contention for rac
    726175 Explorer
    Currently Being Moderated
    Thanks JL and Joel for views ,its always a learning experience to hear from you guys

    some more data for module contributing to contention, suggestive of ITL waits
    Index desc 
    COLUMN_NAME                              COLUMN_POSITION COLUMN_LENGTH
    ---------------------------------------- --------------- -------------
    TRANSACTION_ID                                         1            22
    LANGUAGE                                               2             4
    
    
     INI_TRANS  MAX_TRANS   PCT_FREE
    ---------- ---------- ----------
            11        255         10
    
    
    Top User Events                   DB/Inst: PROD/PROD1  (Apr 04 11:00 to 14:00)
    
                                                                   Avg Active
    Event                               Event Class        % Event   Sessions
    ----------------------------------- --------------- ---------- ----------
    enq: TX - index contention          Concurrency          60.29       0.97
    db file sequential read             User I/O             29.28       0.47
    enq: TX - allocate ITL entry        Configuration         8.77       0.14
              -------------------------------------------------------------
    
    
    Top User Events                   DB/Inst: PROD/PROD2  (Apr 04 11:00 to 14:00)
    
                                                                   Avg Active
    Event                               Event Class        % Event   Sessions
    ----------------------------------- --------------- ---------- ----------
    enq: TX - index contention          Concurrency          69.71       0.75
    db file sequential read             User I/O             25.30       0.27
    gc cr block busy                    Cluster               1.64       0.02
    CPU + Wait for CPU                  CPU                   1.38       0.01
    gc cr block 2-way                   Cluster               1.03       0.01
              -------------------------------------------------------------
    It seem we faced very rare case .
    JL ,whether Partitioning large indexes would avoid ITL contention ?

    regards
    kg

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points