Re: Optimal size for rollback segments

From: John P. Higgins <jh33378_at_deere.com>
Date: Mon, 02 Nov 1998 22:50:19 -0600
Message-ID: <363E8B8B.7B4C6253@deere.com>

You are right that the rollback segments are always used in a round robin fashion, transaction by transaction.

But I would explain OPTIMAL differently. Setting an OPTIMAL does not limit the size to which a rollback can extend. Any rollback segment that catches a large transaction can expand until the tablespace is full. Without OPTIMAL, that rollback segment will retain all the extents it added.

If all the subsequent large transactions use this expanded rollback segment, all is wonderful. The problem then is that some future large transaction will be caught by some other rollback segment (due to the round robin assignment of transactions to rollback segments). This is the source of the 'cannot extend ...' errors.

With OPTIMAL, a rollback segment that expanded will shrink back to the OPTIMAL size. This returns the added extents to freespace in the tablespace. Then future large transactions can find the room to expand the assigned rollback segment as needed.

So, to me, OPTIMAL is designed to make sure a single, large transaction can use the entire tablespace.

If you have control over the SQL, you can force all large transactions to use a single large rollback segment. If you can do this, it is better than using OPTIMAL.

However, in many cases, the SQL is ad hoc or third-party.

Lawrence wrote:

Now, my understanding is that rollback segments are always used in a round
robin fashion irrespective of whether you have OPTIMAL set or not. OPTIMAL
is used to ensure that the rollback segments do not extend to such a large
size that we have  too many old transactions.. It forces the segments to
shrink themselves to the OPTIMAL size.. otherwise all the tablespace would
be used by possibly one large transaction

Lawrence

Serge Montet wrote in message <71a4je$16o@work.wanadoo.com>...
>Hello
>
>I am not very clever, that's why I cannot understand the advantage to
>specify OPTIMAL size for rollback segment.
>Indeed, if we don't specify this storage clause, the rollback segment uses
a
>sort of 'round bind' algorithm. So what's the difference ?!
>
>Thanks...
>
>
>
>

  Received on Mon Nov 02 1998 - 22:50:19 CST