URGENT: Rollback segment contention??
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: URGENT: Rollback segment contention??

  1. #1
    Join Date
    Jan 2000
    Posts
    387

    Exclamation

    Hi

    The following is the statistic from v$waitstat.

    CLASS COUNT TIME
    ------------------ ------------------- -------------------
    data block 24667428 0
    sort block 0 0
    save undo block 0 0
    segment header 34 0
    save undo header 0 0
    free list 0 0
    extent map 0 0
    bitmap block 0 0
    bitmap index block 0 0
    unused 0 0
    system undo header 0 0
    system undo block 0 0
    undo header 165633 0
    undo block 38002 0


    From the statistic, I noticed that the undo header figure is high. I have re-created my rollback segment to a bigger size, but the undo header figure did not drop, why is this so?


    The data block figure is also very high, any ways to reduce it?

    Please help!!! Many Thanks!

  2. #2
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    This is similar to what I have.

    The usual remedy for 'undo' waits is to add more rollbak segments. However you should query v$rollstats to work out your wait ratio.

    select gets,waits,waits/gets*100 wait_ratio,'%', wraps, extends, shrinks, extents, xacts
    from v$rollstat;


    For datablock waits you need to query v$session_waits for 'buffer busy wait' event.

    P1, P2, and P3 in V$session_wait will tell you File, Block and Reason for wait.

    data block waits usually mean there are not enough freelists per block, or initrans needs increasing.

    Metalink has lots of documentation on the subject.







    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  3. #3
    Join Date
    Jan 2000
    Posts
    387

    Unhappy

    Hi

    I have increased my number of rollback segments, but it doesnt seems to help when i check for the undo from the V$waitstat table. However, when I check for the waits from v$rollstat table, they are showing 0 or minimum. Therefore why is it that the number of undo are not decreasing ??

    Any help please?

  4. #4
    Join Date
    May 2002
    Location
    California, USA
    Posts
    175
    mooks,

    To get the clear picture of the rollback segments contention, you have to look at the ratio between the total number of request for data over the same period of time and the statistics in v$waitstat, not just the numbers in v$waitstat.

    Get the statistics with:

    select class, count
    from v$waitstat
    where class like '%undo%;

    Then get the number of consistent gets:

    select value
    from v$sysstat
    where name = 'consistent gets';

    The ratio of waits for any class should be less than 1% of the total number of requets. The solution is to add more rollback segments.

    For rollback segments sizing you can also check:
    http://www.dbaclick.com/cgi-bin/ib3/...=ST;f=20;t=279

    Hope that helps,



    --clio_usa
    Senior Oracle DBA




  5. #5
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Now, you know the count number won't Decrease don't you... It'll only reset to zero after instance shutdown and restart. If the undo count don't increase (or are increasing VERY slowly), you may have solved the issue.
    OCP 8i, 9i DBA
    Brisbane Australia

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