This discussion is archived
10 Replies Latest reply: Nov 2, 2007 1:08 AM by Jonathan Lewis RSS

Enq: TX - index contention - blevel / leaf_blocks

538022 Newbie
Currently Being Moderated
Hello guys,
today i have faced a performance issue with some insert statements on one table.

The session which was blocking the others has the wait event "db file sequential read" and all other sessions were waiting in the state "enq: TX - index contention".
The table has 5 indexes (with round about 30GBs and the table itself with round about 25 GB).
The insert statement inserts in all fields and the most fields are VARCHAR2(80).

From the oracle documentation i thought an index split would cause this problem:
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14211/instance_tune.htm
-> Waits for TX in mode 4 also occur when a transaction inserting a row in an index has to wait for the end of an index block split being done by another transaction. This type of TX enqueue wait corresponds to the wait event enq: TX - index contention.

But i have a look at the collected statistics (both runs were fired with estimate 10 percent).

Before the problem:
INDEX_NAME                     BLEVEL                 LEAF_BLOCKS            LAST_ANALYZED             
------------------ ---------------------- ---------------------- ------------------------- 
MYTAB~AL             3                      420600                 15.10.07                  
MYTAB~DT             3                      995700                 15.10.07                  
MYTAB~UE             3                      604300                 15.10.07                  
MYTAB~00             3                      885500                 15.10.07                  
MYTAB~0              3                      1162000                15.10.07                  
After the problem
INDEX_NAME                     BLEVEL                 LEAF_BLOCKS            LAST_ANALYZED             
------------------------------ ---------------------- ---------------------- ------------------------- 
MYTAB~AL             3                      333800                 31.10.07                  
MYTAB~DT             3                      614400                 31.10.07                  
MYTAB~UE             3                      466600                 31.10.07                  
MYTAB~00             3                      623300                 31.10.07                  
MYTAB~0              3                      739500                 31.10.07        
The segment_statistics by V$SEGMENT_STATISTICS shows some strange values:
OBJECT_NAME                    STATISTIC_NAME     STATISTIC#             VALUE
------------------------------ ------------------ ---------------------- ----------------------
MYTAB                          space used         14                     -10907907375
MYTAB~AL                       space used         14                     -730875296
MYTAB~DT                       space used         14                     49366502
MYTAB                          db block changes   3                      63269568
MYTAB~DT                       logical reads      0                      86268224
MYTAB~UE                       space used         14                     88343944
MYTAB~AL                       space allocated    15                     118489088
MYTAB~00                       space used         14                     137376256
MYTAB~0                        space used         14                     143013882
MYTAB~DT                       space allocated    15                     175112192
MYTAB~UE                       space allocated    15                     184549376
MYTAB~00                       space allocated    15                     235929600
MYTAB~0                        space allocated    15                     286261248
MYTAB                               space allocated    15                     534773760
The ITL waits are not too many (round about 300).

Now i have a really understanding problem.
1) Why are there less leaf blocks (i don't know anything about big deletes)?
2) What can cause also an "enq: TX - index contention"?

I would prefer any suggestions.

The database version is 10.2.0.2.0

Thanks and Regards
Stefan
  • 1. Re: Enq: TX - index contention - blevel / leaf_blocks
    601585 Oracle ACE
    Currently Being Moderated
    1. "enq: TX - index contention" event is certainly related with index split. I don't know any other situation that is related with this event.

    2. Session with "db file sequential read" event is (probably) splitting index. Reading index block needs single block I/O, hence db file sequential event occurs. Check which object and block the blocking session is waiting for.(As you know p1=file#, p2=block#). By that way you can infer which indexes are related.

    If your db file sequential read wait time is stranglely high(which really occurs sometime), you need to investigate I/O performance.

    3. Leaf block count was reduced? Well, isn't there any chance that indexes were rebuilt before/after inserting sessions began working? It's a likely story as following:
    - ScenarioA
    * You rebuild your indexes.
    * You insert. Because index was rebuilt(got smaller), massive split occurs.

    - ScenarioB
    * You insert.
    * Somebody rebuilds indexes.
    I think scenarioA is quite interesting, and scenarioB sucks... And i don't expect both scenarios really happend :)

    Browsing index split statistics(v$sysstat, v$sesssta) will help.

    4. Index key with continously increasing value(like sequence) is main cause of index split issue.

    5. Some values of V$SEGMENT_STATISTICS show inconsistent value. You need to consult metalink for that.
  • 2. Re: Enq: TX - index contention - blevel / leaf_blocks
    538022 Newbie
    Currently Being Moderated
    Hello Cho,
    thanks for your answer.

    Point 1 and 2 is clear, because of the indeces are very big.

    Point 3: No there was no index rebuild.. i am the only dba who can do that... I can only think about big deletes (archiving) from the application and that the leaf blocks get reduced... but that would not explain the really long index split wait event, or?
    SQL> select NAME, VALUE from V$SYSSTAT where NAME like '%split%';
    NAME                                                             VALUE                  
    ---------------------------------------------------------------- ---------------------- 
    leaf node splits                                                 2996070                
    leaf node 90-10 splits                                           203923                 
    branch node splits                                               25982                  
    queue splits                                                     0                 
    
    
    And here for the session 1024:
    
    SQL> select b.SID, a.NAME, b.VALUE from V$SESSTAT b, V$SYSSTAT a where b.sid = 1024 and a.STATISTIC# = b.STATISTIC# and a.name like '%split%';
    SID                    NAME                                                             VALUE                  
    ---------------------- ---------------------------------------------------------------- ---------------------- 
    1024                   leaf node splits                                                 17459                  
    1024                   leaf node 90-10 splits                                           1894                   
    1024                   branch node splits                                               87                     
    1024                   queue splits                                                     0      
    Point 4 is clear too.

    Point 5: I think the negative values are coming from the high value and the column is overflowed.

    Is there any explanation at the oracle site for the STATISTIC_NAME column in V$SEGMENT_STATISTICS. For example what means "space allocated" or "space used"?

    Can i determine the index that was split by a v$session snapshot or how can i find that out?

    Regards
    Stefan
  • 3. Re: Enq: TX - index contention - blevel / leaf_blocks
    601585 Oracle ACE
    Currently Being Moderated
    I've also tried to find the good explanation on V$SEGMENT_STATISTICS's values, but with no success.

    You can't find which index is on split from v$sesstat or "enq: TX - index contention" event.

    "db file sequential read" event might have answer. P1=file# and P2=block#.
    You can query x$bh/v$bh(if it's in buffer cache) or execute block dump(alter system dump datafile P1 block P2). It's quite nasty, isn't it? :)

    I've seen quite large amount of wait time of "enq: TX - index contention" event especially when branch node is being split. The only way to resolve this may be reducing index split, but it doesn't seem like an easy job.

    PS) As you said, massive deletion would be the only reason for smaller leaf block count without rebuilding index.
  • 4. Re: Enq: TX - index contention - blevel / leaf_blocks
    538022 Newbie
    Currently Being Moderated
    Hello Cho,
    thanks again for your answer... one last question about the splitting and performance.

    I think the "leaf node splits" is the normal 50 to 50 split - so how does oracle decide to split the leaf node to 50/50 or 90/10?

    As you can see at my stats some branch node splits occur too.. so i think not only the leaf nodes were "full" - the branch nodes were too.

    Regards
    Stefan
  • 5. Re: Enq: TX - index contention - blevel / leaf_blocks
    601585 Oracle ACE
    Currently Being Moderated
    Oracle does 90/10 split when rightmost leaf node gets full. This is intentional and for performance. When rightmost leaf node gets full, Oracle expects that insertion might occur on rightmost block. So Oracle split 90/10(actually 99/1 or 100/0), and you have a nearly empty new block for continous insertion.
  • 6. Re: Enq: TX - index contention - blevel / leaf_blocks
    528670 Newbie
    Currently Being Moderated
    Hi Stefan,

    Was the tablespace in which this table stored created with ASSM?
    Probably wait events you are experiencing are on BMB?

    Best Regards,
    Alex
  • 7. Re: Enq: TX - index contention - blevel / leaf_blocks
    538022 Newbie
    Currently Being Moderated
    Hello Alex,
    yes the table was stored in a LMT and ASSM tablespace... but does the wait events points to another problem.

    Regards
    Stefan
  • 8. Re: Enq: TX - index contention - blevel / leaf_blocks
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Just a brief note about the general topic.

    After a massive delete, you may have a lot of empty leaf blocks in the index. These blocks are left in the b-tree structure AND attached to the freelist.

    On a subsequent insert, if a process needs to split a block it takes the top block off the free list and shares the data from the existing block between the old and new blocks; after which the two blocks have various pointers corrected.

    However, there are cases where the splitting process will discover that the block it took from the free-list was also in a position in the index structure that made it an illegal choice. It only discovers this after doing all the work of splitting and relinking the blocks, and so has to roll back that work - which shows up as a 'transaction rollback' in v$sysstat - and try another block.

    If you have done a massive delete, you could find that one process COULD work through a couple of hundred "not quite free enough" blocks before finding one it can use. This could (a) require lots of db file sequential reads to pick up each block in turn (b) cause other transactions to wait on a TX/4 for each of the blocks in turn.

    I have seen this happen on a couple of live systems. I have not yet been able to emulate it on a test system because I can't figure out the rules that cause the leaf block to be a reject.

    A work around to the problem is to do an 'alter index coalesce' after doing the massive delete. This cleans all the empty blocks and takes them out of the index structure. (You have to have done a commit after the delete for this to work).

    I don't thnk you can spot this from the statistics - one long catastrophe appears as a single event in the stats. I think it might also be recorded as a hidden statistics in v$segstat, the 'ITL service wait'.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk
  • 9. Re: Enq: TX - index contention - blevel / leaf_blocks
    538022 Newbie
    Currently Being Moderated
    Hello Jonathan,
    thanks for sharing your experiences with us.
    However, there are cases where the splitting process will discover that the block it took from the free-list was also in a position in the index structure that made it an illegal choice. It only discovers this after doing all the work of splitting and relinking the blocks, and so has to roll back that work
    Can you please tell us in which cases this can happen (that oracle makes an illegal choice)?
    If you have done a massive delete, you could find that one process COULD work through a couple of hundred "not quite free enough" blocks before finding one it can use.
    Why - Oracle knows the filling degree of an index block... so why touching couple of blocks for finding? I am using ASSM.


    Here is just a little proof, that the leaf_block value changes after some deletes, but the index size will be the same (leaf blocks are still allocated):
    SQL> create table TEST (a number, b number);
    Table created.
    SQL> create index TEST_I on TEST (a);
    Index created.
    SQL> exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST');
    PL/SQL procedure successfully completed.
    
    SQL> select index_name, leaf_blocks from dba_indexes where table_name = 'TEST';
    INDEX_NAME                     LEAF_BLOCKS
    ------------------------------ -----------
    TEST_I                                   0
    
    SQL> select segment_name, blocks from dba_segments where segment_name = 'TEST_I';
    SEGMENT_NAME             BLOCKS
    -------------------- ----------
    TEST_I                        8
    
    
    SQL> BEGIN
      2  FOR i in 1 .. 50000 LOOP
      3  INSERT INTO TEST VALUES(i,i);
      4  END LOOP;
      5  COMMIT;
      6  END;
      7  /
    
    SQL> exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST');
    PL/SQL procedure successfully completed.
    
    SQL> select index_name, leaf_blocks from dba_indexes where table_name = 'TEST';
    INDEX_NAME                     LEAF_BLOCKS
    ------------------------------ -----------
    TEST_I                                  99
    
    SQL> select segment_name, blocks from dba_segments where segment_name = 'TEST_I';
    SEGMENT_NAME             BLOCKS
    -------------------- ----------
    TEST_I                      112
    
    
    SQL> DELETE FROM TEST WHERE a > 100;
    49900 rows deleted.
    SQL> commit;
    Commit complete.
    
    SQL> exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST');
    PL/SQL procedure successfully completed.
    
    SQL> select index_name, leaf_blocks from dba_indexes where table_name = 'TEST';
    INDEX_NAME                     LEAF_BLOCKS
    ------------------------------ -----------
    TEST_I                                   1
    
    SQL> select segment_name, blocks from dba_segments where segment_name = 'TEST_I';
    SEGMENT_NAME             BLOCKS
    -------------------- ----------
    TEST_I                      112
    Regards
    Stefan
  • 10. Re: Enq: TX - index contention - blevel / leaf_blocks
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    >
    Can you please tell us in which cases this can happen
    (that oracle makes an illegal choice)?
    No - as I pointed out above, I haven't been able to reproduce the effect on demand. This means I don't know why it happens, because if I did know I would be able to make it happen. The best comment I got from an SR was that it's "something to do with picking a leaf block that is under the same higher level branch block as the block you are trying to split" - but since every leaf block is under the root block that doesn't really clarify the situation much.
    If you have done a massive delete, you could find
    that one process COULD work through a couple of
    hundred "not quite free enough" blocks before finding
    one it can use.

    Why - Oracle knows the filling degree of an index
    block... so why touching couple of blocks for
    finding? I am using ASSM.
    "not quite free enough" -- bearing in mind the sentences that preceded it - was intended to refer to a leaf block that was empty, on the free list, but was not legally usable by the current process.

    >
    Here is just a little proof, that the leaf_block
    value changes after some deletes, but the index size
    will be the same (leaf blocks are still allocated):
    I think I commented on this in Cost Based Oracle. The analyzed command used to scan the entire index segment for leaf blocks, the dbms_stats command analyzes the rowids - so misses blocks where every index entry has been deleted. (I think it's mentioned in the chapter on tablescans as introducing a costing anomaly in the cost of index fast full scans).

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