shrinking roll back segments

When will you consider the best time to shrink roll back segments? I mean what signs do you have to see to consider shrinking your roll back segments?

dorothy

Hi,

Set optimal and let ORACLE do it for you. It can also be done when the segment is not in use.

Baliga

Only time I had to shrink manually is to resize my datafiles, normally if you set optimal Oracle will do it automatically, anyway during transaction intensive times roll back segment shouldnt be shrink and extended all the time to avoid perfomance issues

Best bet is setting up optimal parameter, it will grow per transaction requirement ans shrink to optimal once the transaction is done.

Thanks for the responses. But my question has not been answered yet. The question is why should a roll back segment be shrunk(either manually or setting the optimal parameter). I mean what are the advantages?


thanks,
dorothy

you asked when are the time to do it and I think most of us tried to answered that question, now you wish to know the advantages, there arent any. Setting optimal is simply to avoid potential errors

For example you have 4 RBS of 5MB each with minextents set to 5, maxextents set to unlimited, initial extent 1MB and next extent to 1MB, and the datafile that composes tablespace RBS is 100MB:

for whatever reason, a huge transaction for example,

RBS1 extends from 5 extents to 40 extents, 40MB in size
RBS2 extends from 5 extents to 40 extents as well, 40MB in size
RBS3 and RBS4 stayed at 5MB size

So basically now 90MB of datafile is filled, if there is another transaction coming in which is pretty large and you have not started the transaction by using

set transaction using rollback segment XXX command

then the transaction could be assigned by Oracle to use RBS3, RBS4 or RBS1 or RBS2 depending on number of concurrent transactions in each RBS, imagine that this transaction is assigned to RBS3.
Since the transaction is big and RBS3 is relatively small you will get errors before this transaction finishes because the datafile can not extend (of course if you have not set autoextend on in datafile) and you might think "Oh this cant be true, I have 100MB assigned for RBS purposes, this transaction cant be so large"
Right this is true, RBS1 and RBS2 have more than enough spaces to accomodate this transaction however the time you started the transaction there might be a few small transactions going in RBS1, RBS2 and RBS4, and RBS3 none and that's why Oracle assigned RBS3 to your transaction. Imagine if you set optimal in RBS, once those big transactions have finished in RBS1 and RBS2 they would be shrink back to 5MB and in that case you would not get errors as above since they would be space in the datafile for RBS3 to extend.

Does this help?



[Edited by pando on 02-04-2001 at 04:14 PM]

thanks pando. Very clear now

Ok, but what about forcing Oracle to shrink and getting ora-01550: snapshot to old? I mean the case when Oracle shrinks a large transaction which has query on it.
Or optimal has nothing to do with it?

well that comes in the tuning of your Rollback Segments no?

may be during tuning, but then it looks like 'set transaction use rollback segment' is quite important and probably the only way to have some control of large transactions.

pando,
thanks very much for the reply on the shrinking of RBSs, was very educative, for me too
cheers
soren