what are rollback segments for?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: what are rollback segments for?

  1. #1
    Join Date
    Jul 2000
    Posts
    70

    Question

    I do regular updates on the entire database and I was told that increasing the rollback segment could speed up the process.
    Is that true?
    What exactly does the rollback segment do and what is it used for?
    Is it true that increasing the rbs datafile size would increase the rollback segment size or do I need to specify elsewhere of the rollback segment.
    And does increasing the rbs datafile size mean increasing the rollback segment size?
    Thanks.

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Answering to your firest question, yes RBS would improve performance. RBS is used to save the old value when a process changes data in a database. i.e It stores the location of the data and the data as it existed before being modified.

    The header of the RBS contains a transaction table where information about the current transactions using the RBS is stored. In the mean time, transcation can use only one RBS to store all of its rollback(undo) records. These are used when for example when an instance fails in the middle of a transaction. Then oracle would use the RBS to rollback any uncommited changes when the database is to be reopen next time.

    For more information do a search on this forum, and there are a number of other valuable informations on RBS.

    Good luck,
    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Jul 2000
    Posts
    70
    So my other question is, how does commit affect the rbs datafile?
    If I have an sql script that has a series of update commands that run over 3 million records, would adding commit statements in between the update commands, help maintain the rbs datafile size to a normal size or not?

  4. #4
    Yes, but only if the next transaction uses the same rollback segment (you can explicitly say it) AND the rollback segment has the optimal clause set to some value (so it can shrink to that size)
    Ramon Caballero, DBA, rcaballe@yahoo.com

  5. #5
    Join Date
    Jul 2000
    Posts
    70
    How can I have the optimal clause set to some value (so it can shrink to that size)?

  6. #6
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Have optimal size clause at the time of creating the rollback segment. RBS grows as big as that transaction needs and shrink back to optimal size once the transaction is done. Make sure it should be minimum your inital+next extent size.
    Reddy,Sam

  7. #7
    SQL>alter rollback segment R01 storage (optimal ??M);
    Where ?? is the size of 20 extents or less.
    i.e. initial 1M next 1M => optimal 20M
    initial 20M next 10M => optimal 60M
    Just a guess ;)
    Ramon Caballero, DBA, rcaballe@yahoo.com

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