This discussion is archived
7 Replies Latest reply: Mar 28, 2012 2:05 PM by 927110 RSS

what causes enq: TX - index contention?

762481 Newbie
Currently Being Moderated
I am wondering whether Oracle sequence will cause this enq: TX - index contention issue? Or application causing this?


Thanks.
  • 1. Re: what causes enq: TX - index contention?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    user9294644 wrote:
    I am wondering whether Oracle sequence will cause this enq: TX - index contention issue? Or application causing this?
    This wait event shows one process waiting for another to complete an index block split.

    If you've created an index on a column that is populated from a call to sequence.nextval on insert then you're quite likely to see many of these waits if your system inserts into the table very frequently from a number of concurrent sessions. (You're also likely to see a couple of other wait events as well, depending on your configuration).

    A standard (though not necessarily perfect) solution to this problem is to redefine the index as "reverse key" if you're running single instance. If you're running RAC than setting the sequence cache to something in the region of a few thousand may help (by reducing competition for the same leaf node across instances).


    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    There is a +"Preview"+ tab at the top of the text entry panel. Use this to check what your message will look like before you post the message. If it looks a complete mess you're unlikely to get a response. (Click on the +"Plain text"+ tab if you want to edit the text to tidy it up.)
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 2. Re: what causes enq: TX - index contention?
    762481 Newbie
    Currently Being Moderated
    this is what I have in the AWR report
    Top 5 Timed Events                                         Avg %Total
    ~~~~~~~~~~~~~~~~~~                                        wait   Call
    Event                                 Waits    Time (s)   (ms)   Time Wait Class
    ------------------------------ ------------ ----------- ------ ------ ----------
    gc buffer busy                      413,173     144,475    350   44.0    Cluster
    enq: TX - index contention          161,725      64,703    400   19.7 Concurrenc
    log file sync                       567,623      41,558     73   12.6     Commit
    buffer busy waits                    57,181      21,253    372    6.5 Concurrenc
    gc current block busy                56,003      14,835    265    4.5    Cluster
              -------------------------------------------------------------       
    Wait Events                         
    -> s  - second      
    -> cs - centisecond -     100th of a second
    -> ms - millisecond -    1000th of a second
    -> us - microsecond - 1000000th of a second
    -> ordered by wait time desc, waits desc (idle events last)
    
                                                                       Avg          
                                                 %Time  Total Wait    wait     Waits
    Event                                 Waits  -outs    Time (s)    (ms)      /txn
    ---------------------------- -------------- ------ ----------- ------- ---------
    gc buffer busy                      413,173   19.8     144,475     350       0.3
    enq: TX - index contention          161,725   54.4      64,703     400       0.1
    log file sync                       567,623     .0      41,558      73       0.4
    buffer busy waits                    57,181    9.5      21,253     372       0.0
    gc current block busy                56,003    2.1      14,835     265       0.0
    gc current block 2-way              192,823     .1       9,211      48       0.1
    db file sequential read             168,255     .0       7,899      47       0.1
    gc cr block 2-way                   155,086     .0       6,507      42       0.1
    enq: HW - contention                 18,878   54.6       6,415     340       0.0
    buffer deadlock                      85,978   99.1       4,225      49       0.1
    gc current grant 2-way               74,800     .0       3,940      53       0.0
    gc current grant busy                16,925    1.1       1,810     107       0.0
    gcs log flush sync                  114,953   40.9       1,726      15       0.1
    gc cr block busy                     10,143     .0       1,532     151       0.0
    log file parallel write             268,236     .0       1,510       6       0.2
    control file sequential read         25,175     .0         901      36       0.0
    wait for scn ack                     52,557     .0         786      15       0.0
    gc cr grant 2-way                    16,312     .0         692      42       0.0
    DFS lock handle                      11,336    1.1         443      39       0.0
    enq: TX - contention                  1,027   34.1         330     322       0.0
    latch: cache buffers chains           6,262     .0         325      52       0.0 
  • 3. Re: what causes enq: TX - index contention?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    user9294644 wrote:
    this is what I have in the AWR report
    Top 5 Timed Events                                         Avg %Total
    ~~~~~~~~~~~~~~~~~~                                        wait   Call
    Event                                 Waits    Time (s)   (ms)   Time Wait Class
    ------------------------------ ------------ ----------- ------ ------ ----------
    gc buffer busy                      413,173     144,475    350   44.0    Cluster
    enq: TX - index contention          161,725      64,703    400   19.7 Concurrenc
    log file sync                       567,623      41,558     73   12.6     Commit
    buffer busy waits                    57,181      21,253    372    6.5 Concurrenc
    gc current block busy                56,003      14,835    265    4.5    Cluster
    ------------------------------------------------------------- 
    I wonder if anyone's wondering how I guessed that you were running RAC.

    A huge fraction of the time reported here could be due to a few indexes on columns based on sequences that have been declared with a small CACHE size.

    You don't often see large numbers of "enq: TX - index contention" waits - unless something is slowing down your access to the blocks that are being split. And RAC can do that for you.

    In part, the buffer busy waits, gc buffer busy waits, and gc current block busy, will be from the index blocks that are flying back and fore across the interconnect because both nodes are constantly working on the "right hand block" of an index. Similarly the excessive number of log file syncs will be due (in part) to the fact that Oracle has to flush any redo log relating to a block before passing the block (whether in CUR or CR mode) across the interconnect. Finally, the extra time and resource involved in handling the index block split (and this may also be branch block splits) makes the TX contention appear more frequently and persist for longer.

    In this situation, I'd also look at the Instance Statistics to see the number of "split" statististics reported (leaf node splits, leaf node 90/10 splits, brancy node splits - I just search for "split" because I can never remember the exact name.

    Your other waits show some interesting effects, though, which may be cause or effect - your single block reads are slow on average ... possibly I/O system is overloaded, and if a session has to read an empty index block from disc before splitting it this would make the processes waiting on the TX spend more time waiting.

    You have some waits on DFS lock handle - and one way to get those is to declare sequences with the ORDER option - maybe your wait time is a large number of short waits on some indexes, plus a small number of long waits on other indexes which are based on ORDERed sequences.

    Basically, you're probably looking in the right area by focusing on indexes and sequences - but you will need to spend a few hours rummaging around the related statistics to decide where the worst issues are and how best to fix them. (Looking at "Segments by ..." may be a big help here identifying the busiest offenders.


    Update: Another point is the time spent on HW waits - which is indicative of time spent on moving the highwater mark.  If an index block split needs to move the high water mark on the index, a slow HW wait for the splitter will increase the time spent waiting on the TX for the waiter.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    There is a +"Preview"+ tab at the top of the text entry panel. Use this to check what your message will look like before you post the message. If it looks a complete mess you're unlikely to get a response. (Click on the +"Plain text"+ tab if you want to edit the text to tidy it up.)
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  • 4. Re: what causes enq: TX - index contention?
    762481 Newbie
    Currently Being Moderated
    Sorry for did not tell you that my current database is running RAC.I am new to Oracle database especially Oracle RAC database. Based on the Top 5 Timed events that I posted earlier, does it means that the enq: TX - index contention is high and significant causing slow performance on the database because I am facing slow performance on the server and I have no idea how to resolve it. Can you point me where is the place that I should looking on to it? I have some sequence on the schema with order and noorder. Anything that I should focus on?

    Instance Activity Stats             
    
    Statistic                                     Total     per Second     per Trans
    -------------------------------- ------------------ -------------- -------------
    CPU used by this session                    133,010           36.9           0.1
    CPU used when call started                  131,060           36.4           0.1
    CR blocks created                            96,984           26.9           0.1
    Cached Commit SCN referenced                 46,351           12.9           0.0
    Commit SCN cached                               380            0.1           0.0
    DB time                                  34,486,615        9,569.2          21.7
    DBWR checkpoint buffers written             322,904           89.6           0.2
    DBWR checkpoints                                  3            0.0           0.0
    DBWR fusion writes                           44,249           12.3           0.0
    DBWR object drop buffers written                  0            0.0           0.0
    DBWR parallel query checkpoint b                  0            0.0           0.0
    DBWR revisited being-written buf                  0            0.0           0.0
    DBWR thread checkpoint buffers w              1,277            0.4           0.0
    DBWR transaction table writes                 5,970            1.7           0.0
    DBWR undo block writes                       83,576           23.2           0.1
    DDL statements parallelized                       0            0.0           0.0
    DFO trees parallelized                           16            0.0           0.0
    PX local messages recv'd                         45            0.0           0.0
    PX local messages sent                           45            0.0           0.0
    PX remote messages recv'd                       443            0.1           0.0
    PX remote messages sent                         331            0.1           0.0
    Parallel operations downgraded t                  0            0.0           0.0
    Parallel operations not downgrad                 16            0.0           0.0
    RowCR - row contention                           10            0.0           0.0
    RowCR attempts                               63,348           17.6           0.0
    RowCR hits                                   62,540           17.4           0.0
    SMON posted for undo segment shr                176            0.1           0.0
    SQL*Net roundtrips to/from clien          2,922,987          811.1           1.8
    active txn count during cleanout          2,143,374          594.7           1.4
    application wait time                         4,056            1.1           0.0
    background checkpoints completed                  3            0.0           0.0
    background checkpoints started                    3            0.0           0.0
    background timeouts                          13,019            3.6           0.0
    branch node splits                               69            0.0           0.0
    buffer is not pinned count                  950,427          263.7           0.6
    buffer is pinned count                    3,897,898        1,081.6           2.5
    bytes received via SQL*Net from         158,764,277       44,053.0          99.8
    bytes sent via SQL*Net to client        151,330,836       41,990.4          95.1
    calls to get snapshot scn: kcmgs          2,211,527          613.6           1.4
    calls to kcmgas                           1,261,882          350.1           0.8
    calls to kcmgcs                              15,591            4.3           0.0
    change write time                             4,089            1.1           0.0
    cleanout - number of ktugct call            334,113           92.7           0.2
    cleanouts and rollbacks - consis             91,005           25.3           0.1
    cleanouts only - consistent read              5,282            1.5           0.0
    cluster key scan block gets                  18,048            5.0           0.0
    cluster key scans                             8,006            2.2           0.0
    cluster wait time                        18,222,488        5,056.3          11.5
    commit batch performed                            0            0.0           0.0
    commit batch requested                            0            0.0           0.0
    commit batch/immediate performed              3,343            0.9           0.0
    commit batch/immediate requested              3,343            0.9           0.0
    commit cleanout failures: block              91,547           25.4           0.1
    commit cleanout failures: buffer                166            0.1           0.0
    commit cleanout failures: callba             15,227            4.2           0.0
    commit cleanout failures: cannot             16,854            4.7           0.0
    commit cleanout failures: hot ba                  0            0.0           0.0
    commit cleanouts                          2,741,733          760.8           1.7
    commit cleanouts successfully co          2,617,939          726.4           1.7
    commit immediate performed                    3,343            0.9           0.0
    Instance Activity Stats              
    
    Statistic                                     Total     per Second     per Trans
    -------------------------------- ------------------ -------------- -------------
    commit immediate requested                    3,343            0.9           0.0
    commit txn count during cleanout            451,507          125.3           0.3
    concurrency wait time                     8,559,933        2,375.2           5.4
    consistent changes                          906,695          251.6           0.6
    consistent gets                          41,146,077       11,417.0          25.9
    consistent gets - examination             5,422,337        1,504.6           3.4
    consistent gets direct                            0            0.0           0.0
    consistent gets from cache               41,146,081       11,417.0          25.9
    current blocks converted for CR                   0            0.0           0.0
    cursor authentications                          183            0.1           0.0
    data blocks consistent reads - u            884,564          245.4           0.6
    db block changes                          7,767,946        2,155.4           4.9
    db block gets                            12,896,410        3,578.4           8.1
    db block gets direct                            828            0.2           0.0
    db block gets from cache                 12,895,600        3,578.2           8.1
    deferred (CURRENT) block cleanou            494,734          137.3           0.3
    dirty buffers inspected                         778            0.2           0.0
    enqueue conversions                           1,576            0.4           0.0
    enqueue releases                          2,317,369          643.0           1.5
    enqueue requests                          2,316,881          642.9           1.5
    enqueue timeouts                                 59            0.0           0.0
    enqueue waits                                82,078           22.8           0.1
    exchange deadlocks                          115,482           32.0           0.1
    execute count                             1,388,282          385.2           0.9
    failed probes on index block rec                  0            0.0           0.0
    free buffer inspected                       700,645          194.4           0.4
    free buffer requested                       839,400          232.9           0.5
    gc CPU used by this session                  27,181            7.5           0.0
    gc blocks lost                                    8            0.0           0.0
    gc cr block build time                          328            0.1           0.0
    gc cr block flush time                       47,559           13.2           0.0
    gc cr block receive time                    813,526          225.7           0.5
    gc cr block send time                         1,103            0.3           0.0
    gc cr blocks received                       165,491           45.9           0.1
    gc cr blocks served                         175,820           48.8           0.1
    gc current block flush time                 291,159           80.8           0.2
    gc current block pin time                   570,546          158.3           0.4
    gc current block receive time             2,415,352          670.2           1.5
    gc current block send time                    1,932            0.5           0.0
    gc current blocks received                  249,372           69.2           0.2
    gc current blocks served                    244,635           67.9           0.2
    gc local grants                             163,144           45.3           0.1
    gc remote grants                            121,563           33.7           0.1
    gcs messages sent                         1,192,653          330.9           0.8
    ges messages sent                            68,275           18.9           0.0
    global enqueue get time                   7,161,884        1,987.2           4.5
    global enqueue gets async                     6,383            1.8           0.0
    global enqueue gets sync                  1,750,381          485.7           1.1
    global enqueue releases                   1,745,381          484.3           1.1
    heap block compress                          31,428            8.7           0.0
    hot buffers moved to head of LRU            239,506           66.5           0.2
    immediate (CR) block cleanout ap             96,291           26.7           0.1
    immediate (CURRENT) block cleano            112,637           31.3           0.1
    index crx upgrade (found)                         0            0.0           0.0
    index crx upgrade (positioned)                5,012            1.4           0.0
    index fast full scans (full)                     49            0.0           0.0
    index fast full scans (rowid ran                  0            0.0           0.0
    index fetch by key                          178,155           49.4           0.1
    index scans kdiixs1                         584,830          162.3           0.4
    leaf node 90-10 splits                          419            0.1           0.0
    Instance Activity Stats             
    
    Statistic                                     Total     per Second     per Trans
    -------------------------------- ------------------ -------------- -------------
    leaf node splits                             10,430            2.9           0.0
    lob reads                                       243            0.1           0.0
    lob writes                                      187            0.1           0.0
    lob writes unaligned                            187            0.1           0.0
    logons cumulative                             6,616            1.8           0.0
    messages received                           409,201          113.5           0.3
    messages sent                               409,201          113.5           0.3
    no buffer to keep pinned count                    0            0.0           0.0
    no work - consistent read gets           34,846,757        9,669.1          21.9
    opened cursors cumulative                   107,362           29.8           0.1
    parse count (failures)                           13            0.0           0.0
    parse count (hard)                            7,013            2.0           0.0
    parse count (total)                         102,428           28.4           0.1
    parse time cpu                                1,629            0.5           0.0
    parse time elapsed                            8,087            2.2           0.0
    physical read IO requests                   174,021           48.3           0.1
    physical read bytes                   1,425,580,032      395,561.6         895.9
    physical read total IO requests             200,077           55.5           0.1
    physical read total bytes             3,434,015,744      952,850.7       2,158.2
    physical read total multi block               1,794            0.5           0.0
    physical reads                              174,015           48.3           0.1
    physical reads cache                        168,495           46.8           0.1
    physical reads cache prefetch                 1,633            0.5           0.0
    physical reads direct                         5,526            1.5           0.0
    physical reads direct (lob)                       0            0.0           0.0
    physical reads direct temporary                   0            0.0           0.0
    physical reads prefetch warmup                    0            0.0           0.0
    physical write IO requests                  245,706           68.2           0.2
    physical write bytes                  3,031,171,072      841,071.7       1,905.0
    physical write total IO requests            799,118          221.7           0.5
    physical write total bytes            8,074,667,008    2,240,511.6       5,074.7
    physical write total multi block            445,256          123.6           0.3
    physical writes                             370,016          102.7           0.2
    physical writes direct                        3,096            0.9           0.0
    physical writes direct (lob)                      0            0.0           0.0
    physical writes direct temporary                  0            0.0           0.0
    physical writes from cache                  366,920          101.8           0.2
    physical writes non checkpoint              279,721           77.6           0.2
    pinned buffers inspected                      1,803            0.5           0.0
    prefetch clients - default                       50            0.0           0.0
    prefetch warmup blocks aged out                   0            0.0           0.0
    prefetched blocks aged out befor                  0            0.0           0.0
    process last non-idle time                    3,530            1.0           0.0
    queries parallelized                             16            0.0           0.0
    recovery blocks read                              0            0.0           0.0
    recursive aborts on index block                   0            0.0           0.0
    recursive calls                           2,396,555          665.0           1.5
    recursive cpu usage                           9,605            2.7           0.0
    redo blocks written                       1,626,560          451.3           1.0
    redo buffer allocation retries                  814            0.2           0.0
    redo entries                              4,121,674        1,143.7           2.6
    redo log space requests                       1,204            0.3           0.0
    redo log space wait time                     27,456            7.6           0.0
    redo ordering marks                          74,092           20.6           0.1
    redo size                             1,491,611,712      413,883.7         937.4
    redo subscn max counts                       16,075            4.5           0.0
    redo synch time                           4,236,737        1,175.6           2.7
    redo synch writes                           563,531          156.4           0.4
    redo wastage                            147,279,824       40,866.4          92.6
    redo write time                             156,767           43.5           0.1
    Instance Activity Stats              
    
    Statistic                                     Total     per Second     per Trans
    -------------------------------- ------------------ -------------- -------------
    redo writer latching time                       401            0.1           0.0
    redo writes                                 267,421           74.2           0.2
    rollback changes - undo records              15,115            4.2           0.0
    rollbacks only - consistent read              6,649            1.8           0.0
    rows fetched via callback                   127,339           35.3           0.1
    session connect time                              0            0.0           0.0
    session cursor cache hits                    46,385           12.9           0.0
    session logical reads                    54,042,726       14,995.5          34.0
    session pga memory                      177,050,112       49,126.8         111.3
    session pga memory max                  405,970,976      112,646.5         255.1
    session uga memory                  133,342,404,896   36,999,073.8      83,801.9
    session uga memory max                2,599,224,176      721,217.6       1,633.5
    shared hash latch upgrades - no             153,355           42.6           0.1
    shared hash latch upgrades - wai                 58            0.0           0.0
    sorts (disk)                                      0            0.0           0.0
    sorts (memory)                              628,675          174.4           0.4
    sorts (rows)                              1,391,095          386.0           0.9
    sql area evicted                              6,973            1.9           0.0
    sql area purged                                  13            0.0           0.0
    summed dirty queue length                     3,658            1.0           0.0
    switch current to new buffer                 40,730           11.3           0.0
    table fetch by rowid                      2,330,194          646.6           1.5
    table fetch continued row                       386            0.1           0.0
    table scan blocks gotten                    519,236          144.1           0.3
    table scan rows gotten                   38,613,964       10,714.4          24.3
    table scans (direct read)                         0            0.0           0.0
    table scans (long tables)                         0            0.0           0.0
    table scans (rowid ranges)                        0            0.0           0.0
    table scans (short tables)                   17,971            5.0           0.0
    total number of times SMON poste                179            0.1           0.0
    transaction lock background gets                  0            0.0           0.0
    transaction lock foreground requ                  0            0.0           0.0
    transaction lock foreground wait                  0            0.0           0.0
    transaction rollbacks                         3,343            0.9           0.0
    transaction tables consistent re                155            0.0           0.0
    transaction tables consistent re              5,033            1.4           0.0
    undo change vector size                 506,006,440      140,403.7         318.0
    user I/O wait time                          799,895          222.0           0.5
    user calls                                2,963,067          822.2           1.9
    user commits                                560,573          155.5           0.4
    user rollbacks                            1,030,589          286.0           0.7
    workarea executions - multipass                   0            0.0           0.0
    workarea executions - onepass                     0            0.0           0.0
    workarea executions - optimal               490,486          136.1           0.3
    write clones created in backgrou                 11            0.0           0.0
    write clones created in foregrou                750            0.2           0.0
              -------------------------------------------------------------       
  • 5. Re: what causes enq: TX - index contention?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    >
    Sorry for did not tell you that my current database is running RAC.I am new to Oracle database especially Oracle RAC database.
    That's not a good position to be in if you're supposed to fix the performance problems here.
    Based on the Top 5 Timed events that I posted earlier, does it means that the enq: TX - index contention is high and significant causing slow performance on the database because I am facing slow performance on the server and I have no idea how to resolve it.
    Can you point me where is the place that I should looking on to it? I have some sequence on the schema with order and noorder. Anything that I should focus on?
    A huge amount of your time is spent on traffic between instances, and the most obvious strategy is to shut down one instance and run single instance. This may not be a politically correct suggestion, though.

    It appears that a major cause of your time loss is the way you are using sequences and indexes; it's possible that you are also losing time to table configuration (relating to use ot freelists/freelist groups vs. ASSM). Check the "Segments by ... " sections of the AWR, with special reference to the ones that talk about buffer busy waits and global cache waits to see which indexes seem to be the worst hit, and check whether those indexes are based on sequences that have a small cache, or have been defined with nocache or order.

    Strategies for dealing with the sequence/index probem are: (a) set the sequence cache to a large value. (b) don't use NOCACHE on sequences, (c) don't use ORDER on sequences, (d) recreate the indexes as reverse key indexes.

    (b) and (c) may be denied to you because the business (possibly the auditors) don't like the idea.
    (d) can have side effects, especially if the index is a multi-column index currently used in queries that result in range scans.

    In passing, some of the complaints about performance may simply be due to inefficient SQL - this looks like a web-based OLTP application, but it's showing a comparatively high level of "rows fetched by table scan" - which tends to suggest a poor execution plan in OLTP.

    Joel Goodman wrote an article quite recently about indexes, sequences and RAC that you might find helpful: http://dbatrain.wordpress.com/2010/02/17/tell-rac-to-leave-your-leaves-alone/

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    There is a +"Preview"+ tab at the top of the text entry panel. Use this to check what your message will look like before you post the message. If it looks a complete mess you're unlikely to get a response. (Click on the +"Plain text"+ tab if you want to edit the text to tidy it up.)
    
    +"I believe in evidence. I believe in observation, measurement, and reasoning, confirmed by independent observers. I'll believe anything, no matter how wild and ridiculous, if there is evidence for it. The wilder and more ridiculous something is, however, the firmer and more solid the evidence will have to be."+
    Isaac Asimov                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 6. Re: what causes enq: TX - index contention?
    762481 Newbie
    Currently Being Moderated
    Hi Jonathan,


    Thanks for your information. Is there any website that discuss about how to analyze AWR report because I have no idea on how to read it. Therefore i posted my question here.


    Thanks.
  • 7. Re: what causes enq: TX - index contention?
    927110 Newbie
    Currently Being Moderated
    Thanks for the detailed discussion about index contention in RAC environment. As per oracle , "reverse key is index excellent for insert performance, limited in index range scans. Useful in sequences and time stamp values". If the application logic is based on range scan of indexes, what is the advantage of reverse key index in RAC environment ?

Legend

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