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?
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.
Life is a journey, not a destination!
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?
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)
How can I have the optimal clause set to some value (so it can shrink to that size)?
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.
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 ;)
Click Here to Expand Forum to Full Width