Re: Is it neccessary to recreate rollback seg to the average size.

Thanks a Lot Connor.
It's really helped me a lot.
John Duraisingh

Connor McDonald wrote:

> duraisingh wrote:
> >
> > Fellow Dba's
> >
> > In one of our production database which I support, There are 30
> > rollback segments with Initial 15m ,next 15m ,optimal 30m ,minextents 2
> > and maxextents 249.When I run the tuning scripts, I saw the average size
> > of the rollback segments are around 5megs. Except 2 rollback segments
> > which have the average size around 12mb and the HWM is 61mb.(due to some
> > long running batch jobs)
> > There is no shrinks or extends(except the 2 RB's).
> > So I have planned to recreate all the rollback segments to the new
> > storage (initial 540k,next 540K ,optimal 5400k minextents 10) in the
> > next maintenance window to reflect the average size of the rollback.
> >
> > My questions are
> > 1. Is it really neccessary to recreate all the rollbacksegemnts to the
> > average size to get a better I/O performance.(offline,drop,create &
> > online)
> > 2. Is it possible to recreate the rollbacksegements when users are
> > online. (since it is very difficult to get a maint. window)
> > 3. I already have 30 rollback segments . I got the advice from my senior
> > dba that to increase further 10 Rollback segments. (total to 40)
> > Is it really a needful thing?
> >
> > Thanks in advance...
> > Note: Oracle 7.3.3/Aix4.2/65gb total size/Tuxedo app
>
> The key thing here is MINEXTENTS. So your 540k with minextents 10 will
> be most probably better then minextents 2 (since rollbacks work in a
> cyclic nature).
>
> You can drop/recreate rollback segments whilst the database is active.
> Use:
> alter rollback segment xxx offline;
> drop rollback segment xxx;
> create rollback segment xxx ...;
> alter rollback segment xxx online;
>
> More rollback segments are required if you have header contention -
> check with your DBA that this is what they are monitoring.
>
> HTH
> --
> ===========================================
> Connor McDonald
> "These views mine, no-one elses etc etc"
> connor_mcdonald_at_yahoo.com
>
> "Some days you're the pigeon, and some days you're the statue."
Received on Tue Oct 19 1999 - 14:54:08 CDT