Re: Rollback Segment Trouble

There are several dependencies in managing rollback segments.

First, how many rollback segments do you need? Rule of thumb: 1 rollback segment for every 4 concurrent transactions. Better yet, monitor undo header waits and add rollback segments to keep this below 1% of transactions.

Second, how big must they be? If your largest transactions specify a special rollback segment, then this segment must be large enough to handle your largest transaction. Suppose this is 12 MB. If 3 large transactions run concurrently, you must triple the size of the large rollback segment to 36 MB.

If your large transactions do not specify a special rollback segment, then Oracle assigns rollback segments at random. This means every rollback segment must be able to handle the largest transactions. If you need 7 rollback segments and the largest transaction needs 12 MB, then your rollback tablespace must be at least 84 MB.

Optimal is a way to use less disk for the rollback tablespace while still allowing the largest transactions to use random assignment of a rollback segment. Each rollback segment would normally use only its optimal storage. Then, when a rollback segment catches a large transaction, it expands by adding extents. After the large transaction completes, it shrinks back to optimal (eventually). Thus, when some other rollback segment catches a large transaction, it is able to do the expand / shrink act.

If, as before, you need 7 rollback segments and the largest transaction needs 12 MB, then the tablespace must be at least 12 MB plus 6 times the optimal. If 90 % of the transactions require 1 MB or less, then the optimal should be 1 MB. So the total tablespace should be 18 MB.

If you need to execute 3 large transactions concurrently, then the tablespace would need to be 42 MB.

Initial should equal next. If you want your max extents to stay at 500, then the extent sizes need to be 75 KB.

HTH Arthur Merar wrote:

> Hello,
>
> I have a couple of questions revolving around rollback segments. Any
> help would be appreciated.
>
> First, on Thursday I received a call that users were getting messages
> of follback segments not being able to get any more extents. So, I
> first extended all the rollback segments by 15 extents. That did not
> help, so I extended the tablespace by 15, and that did not help
> either. So, finally I shrunk all the rollback segments to optimal and
> that worked.
>
> I used Desktop DBA to do some reverse engineering on the rollback
> segment to see how it was built. Interesting.....although the Optimal
> parameter was specified, there was no value. And, the Optimal column
> in v$rollstat is NULL. Why is this?
>
> What is a good size for the optimal on a rollback segment? These are
> short queries running against this database. Here is the code that
> made all the rollback segments in my database:
>
> CREATE ROLLBACK SEGMENT RB01
> TABLESPACE RBS
> STORAGE (INITIAL 262144
> NEXT 262144
> MINEXTENTS 10
> MAXEXTENTS 500
> OPTIMAL )
>
> What should optimal be?
>
> Also, what should my normal response be when a rollback segment runs
> out of room like that?
>
> Thanks for your help.
>
> Arthur
> amerar_at_unsu.com
Received on Sat Mar 06 1999 - 16:04:01 CST