Re: Optimal size for rollback segments

From: John P. Higgins <jh33378_at_deere.com>
Date: Thu, 29 Oct 1998 20:32:13 -0600
Message-ID: <3639252D.CDAE2F94@deere.com>

The whole point of the OPTIMAL has to do with the fact that your users sometimes execute a transaction that is 10 or 100 or 1000 ... times bigger than average. When this happens, Oracle extends the rollback segment again and again until the transaction commits or rolls back.

So far so good. But the space allocated to that rollback segment is never given back (without OPTIMAL). So maybe next time you run a large transaction, a different rollback segment is chosen. If there is not enough freespace in the tablespace, you'll get the 'unable to extend' error.

With OPTIMAL set, the extended rollback segment will eventually shrink back to its OPTIMAL size. This has the effect of holding a lot of freespace available for extending whichever rollback segment needs it for a big transaction.

On the other hand, you only need enough rollback segments to handle your maximum 'concurrent' transactions. If you can get by with a single rollback segment, there is no advantage to having it shrink. Let it consume the whole tablespace!

Serge Montet wrote:

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 Thu Oct 29 1998 - 20:32:13 CST