Re: Is it better to drop a rollback segment and recreate it instead of add additional rollback segment?

"K.P, Ng" <kpng_at_sywg.com.hk> wrote in message news:9nkbh6$5tc1_at_imsp212.netvigator.com...
> Ver: 8.1.5.0 Enterprise
> O.S: AIX 4.3.3.0
> Application: On-line security and trading system.
> Transactions: High
> Tablespace for Production: 32GB
> No. of rollback segment: 4 (R01-R04)
> Size of tablespace for rollback segment: 2GB
>
> Q: Due to poor programming, the rollback segment growth quickly for a
> particular rollback segment. e.g R01. Is it better to drop this rollback
> segment and recreate it, or just add additional rollback segment.

Growth of *any* segment unnecessarily is a problem. It means DML has to pause whilst the extent allocation is negotiated with the data dictionary. To avoid growth, drop this rollback segment, and re-create it with much larger extents (it doesn't need extra extents to do its work -about 6 extents will do fine).

Since Oracle round-robins the rollback segments (ie, distributes new transactions amongst them in an attempt to even out loading), all rollback segments should be of the same size. So if you've any odd-sized segments, drop them and re-create them to the same specification.

And finally, since you have 8i, make sure you put your rollback segments into locally managed tablespace, so that *if*, despite everything, you do get segment growth taking place, it will be handled much more speedily than it would be with dictionary-managed tablespace.

Regards
HJR
>
> Rgds,
>
>
>
>
>
Received on Wed Sep 12 2001 - 22:09:08 CEST