Contention for Undo Header
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Contention for Undo Header

Hybrid View

  1. #1
    Join Date
    Sep 2000
    Posts
    362
    Hi,
    I am having a high contention for undo header. I used the following queries to find that out.

    Do i need to increase the rollback segment size or increase the number of rollback segments to tune this.

    Also what is Undo Header??


    select class, count
    2 from v$waitstat
    3 where class in ('system undo header', 'system undo block',
    4 'undo header', 'undo block' ) ;

    CLASS COUNT
    ------------------ ----------
    system undo header 0
    system undo block 0
    undo header 62579
    undo block 2

    select 'Contention for undo header = '||
    2 (round(count/(&xxv1+0.00000000001),4)) * 100||'%'
    3 from v$waitstat
    4 where class = 'undo header' ;
    old 2: (round(count/(&xxv1+0.00000000001),4)) * 100||'%'
    new 2: (round(count/( 85473+0.00000000001),4)) * 100||'%'

    Contention for undo header = 73.21%


    Thanks
    Anurag

  2. #2
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    An undo header is the segment header for an rbs. I believe you may have to increase the # rbs. Plz confirm this with someone. Also, run this script and post the output here:
    -
    SELECT name, waits, gets, waits/gets "Ratio"
    FROM v$rollstat a, v$rollname b
    WHERE a.usn = b.usn;

  3. #3
    Join Date
    Apr 2001
    Posts
    2

    Lightbulb

    Check out your rollback work (v$rollstat/rollanme join using USN & see for extents in dba_rollback_segs) if things are not bright, increase number of rollback segs either small or large but do analysis before you'd create 'em

  4. #4
    Join Date
    Sep 2000
    Posts
    362
    Hi,
    Thanks for the reply.

    The output of the script is below.Please note that I added rollback segments RB6 and RB7 just a few minutes ago.

    Thanks again.
    Anurag


    SQL> SELECT name, waits, gets, waits/gets "Ratio"
    2 FROM v$rollstat a, v$rollname b
    3 WHERE a.usn = b.usn;

    NAME WAITS GETS Ratio
    ------------------------------ ---------- ---------- ----------
    SYSTEM 0 12382 0
    RB0 4918 1324201 .003713938
    RB1 5121 2584849 .00198116
    CTXROL 13474 1808441 .007450616
    RB2 4880 2333087 .002091649
    RB3 5080 1481145 .003429779
    RB4 4684 2132723 .002196253
    RB5 4782 1620563 .002950826
    RB6 0 23 0
    RB7 0 18 0

  5. #5
    Join Date
    Sep 2000
    Posts
    362
    Hi Ravi,

    Can you please give me a script to analyse the rollback segment work.

    Thanks
    Anurag

  6. #6
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    Well, the good part is that there does not seem to be any contention for the rbs.

  7. #7
    Join Date
    Sep 2000
    Posts
    362
    I have read on the metalink site for oracle that if a non zero value existed for 'undo header ' under the class column this means that there is a contention for rollback segment header blocks. Please refer Note: 69464.1

    And my values for the undo header is very high. see below

    select class, count
    2 from v$waitstat
    3 where class in ('system undo header', 'system undo block',
    4 'undo header', 'undo block' ) ;

    CLASS COUNT
    ------------------ ----------
    system undo header 0
    system undo block 0
    undo header 62579
    undo block 2

    I am a little confused. Please suggest.

    Thanks
    Anurag

  8. #8
    Join Date
    Feb 2001
    Posts
    389
    What is ur database block size.
    what are the values for consistent gets and db block gets.
    what is the current and max value for transactions in v$resource_limit.

    how many write,get and waits per rollback segment.
    What is the size of ur rollback segments.

    Problem seems to be the number of transactions hitting single rollback segment, if transactions are high , then u need to have more rollback segments available .

    Take Care
    GP

  9. #9
    Join Date
    Sep 2000
    Posts
    362
    Hi,

    DB_BLOCK_SIZE = 8192
    consistent gets = 16592427
    db block gets = 3088987


    SQL> select RESOURCE_NAME ,CURRENT_UTILIZATION,MAX_UTILIZATION from v$resource_limit;

    RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION
    ------------------------------ ------------------- ---------------
    processes 20 22
    sessions 21 24
    lm_procs 0 0
    lm_ress 0 0
    lm_locks 0 0
    enqueue_locks 12 17
    enqueue_resources 15 34
    dml_locks 2 21
    temporary_table_locks 0 9
    transactions 55 56
    sort_segment_locks 0 1

    RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION
    ------------------------------ ------------------- ---------------
    max_rollback_segments 8 8
    distributed_transactions 0 1
    mts_max_servers 0 0
    parallel_max_servers 0 0


    I am not sure how to get the waits,gets and writes per rollback segments.Please let me know how to get them.


    Thanks
    Anurag

  10. #10
    Join Date
    Feb 2001
    Posts
    389
    U query v$rollstat,
    Ur details tell that u have enough rollback segments,
    now do the queries or procedures use particular rollback segments.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width