Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Is it neccessary to recreate rollback seg to the average size.
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
--
"Some days you're the pigeon, and some days you're the statue." Received on Tue Oct 19 1999 - 06:54:59 CDT