Rollback segments waits
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Rollback segments waits

  1. #1
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Hi Friends..

    I issued the following sql :

    select gets,waits,waits/gets*100 wait_ratio,'%'
    from v$rollstat

    The results were :


    GETS WAITS WAIT_RATIO '
    ---------- ---------- ---------- -
    898 0 0 %
    21800 0 0 %
    20550 0 0 %
    19363 0 0 %
    18700 0 0 %
    20153 0 0 %
    18809 0 0 %
    20080 0 0 %
    19423 0 0 %
    18899 0 0 %
    21022 0 0 %

    GETS WAITS WAIT_RATIO '
    ---------- ---------- ---------- -
    20640 1 .004844961 %
    19875 0 0 %
    20053 0 0 %
    19873 0 0 %
    19588 0 0 %
    20170 0 0 %
    21167 1 .004724335 %
    19917 0 0 %
    20122 0 0 %
    19503 0 0 %
    19346 0 0 %

    GETS WAITS WAIT_RATIO '
    ---------- ---------- ---------- -
    19476 0 0 %
    20348 0 0 %
    21198 0 0 %


    2 rollback segments have had a wait.
    does this mean that I should consider adding another rollback segment or 2 ?

    Thanks in advance
    Suresh

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

  2. #2
    Join Date
    Oct 2000
    Posts
    467
    hi,
    The 2 waits are minimal and you could countinue with the same rollback segs. However since there are only 2 rollback segs, you can go ahead and create 1 or 2 more segs .
    Have you checked the wraps column of the rollback segs ?

    cheers

    Vinit

  3. #3
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    check XACTS field, this will give you an idea of how
    many processes are utilizing the ROLLBACK SEGS in a
    certain point in time.
    If more than 1 then you might need to create new Rbs.

  4. #4
    Join Date
    Oct 2000
    Posts
    467
    Hi Rey,
    Pls note that at a time many processes/transactions can get hold of a rollback segment. This does not indicate a problem at all. As Sureshy had pointed out..only if there are more waits you may create new rollback segs.

    vinit
    Vinit

  5. #5
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    hi vinit,
    this is a parallel occurence anyway, one of the reason why you gets waits is when there are more than one processes utilizing the same rbs.
    The point that I want to made is that If all your rbs have more than 1 XACTS most of the time then this surely means that you need to add more rbs.


  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by reydp

    The point that I want to made is that If all your rbs have more than 1 XACTS most of the time then this surely means that you need to add more rbs.
    you must be joking?
    a rbs supports more than one transaction, actually the extents inside a RBS supports more than one transaction, XACTS means active transaction going in each RBS, if I have 40 extents in a RBS I can perfectly have more than 1, 2, 3, 40, 80 or even more transactions concurrently depending on transaction size

    If we base on strategy in a system with 200 concurrent transactions we would have to create 200 RBS?

    If you see values > 0 in WAITS then you may consider adding more RBS but from the statistics pasted the wait is minimal

  7. #7
    Join Date
    Oct 2000
    Posts
    467
    hi,
    IF you consider adding a rollback seg for no. of values in xacts then i wonder how many rollback segs would you create if your DB has transactions in thousands.
    Secondly the wait is just 1 and not 1% of the gets , which as pando pointed out is quite minimal.

    Cheers
    Vinit

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    just want to add a note, you should rely on v$waitstat to tune your RBS not v$rollstat

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    The point that I want to made is that If all your rbs have more than 1 XACTS most of the time then this surely means that you need to add more rbs.
    I do suggest you look once again for the "Rule of the four" :-)


  10. #10
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    What did I miss....
    Oh....you guys are taking a beating on me....heehhee...

    So thus this means that if all of your rollback segments
    lets say just 8 and not 80 XACTS most of the time you will
    not consider adding RBS?
    Cause I believe if this happens it will hit your GETS and WAITS ratio down.

    Anyway, I just want to consider the XACTS factors aside from GETS and WAITS......

    If I am wrong then CHEERS to you guys.......

    Keept it cool.

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