Re: optimal size for rollback

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<3d81e650_at_dnews.tpgi.com.au>...
> That's what dbms_job or cron is for.
>
> Optimal is only slightly worse as an idea than PCTINCREASE.
 

PCTINCREASE was an attempt to help DBA with space management. It wasn't successful but intention was right.

> Any time Oracle does anything for you automatically, there's a price to pay:
> and the price is performance.

Any time you do it manually, there's a price to pay as well: it's more difficult to support and (depending on person) chance to make a mistake.  

> Don't set optimal.
>
> Period.
>
> (Or full stop, depending on your longitude).

Never say never :) Optimal is very useful when the most of transactions are about one size and there are some larger transactions (and that's OP's situation).

If optimal is higher than usual transaction size no deallocation will happen. 99% of transactions will just happily live in the preallocated extents. The rollback segment extended by the large transaction will be shrinked back after some time, giving space for the next big transaction.

Also I wouldn't overestimate a performance impact in such scenario.

Cheers,
Igor  

> Regards
> HJR
>
>
> "Daud" <daud11_at_hotmail.com> wrote in message
> news:f0bf3cc3.0209130205.2cd2db2_at_posting.google.com...
> > Hi
> >
> > I have been reading quite a bit about rollback segments and I kinda
> > agree that setting optimal size is not quite a good idea. That shows
> > that a dba has not done his job to find out what the correct size of
> > the rollback segment should be.
> > This is what I am thinking of doing and let me know if it does not
> > make sense.
> >
> > initial 1M
> > next 1M
> > minextents 6
> > optimal 6M
> >
> > The reason I want to set optimal is because occasionally I have some
> > big jobs that cause a rollback segment to grow. However, I do not want
> > to have to manually go in and re-set its size once the jobs are done.
> > So, I thought setting optimal will take care of it. What do you
> > experts think?
> >
> > rgds
> > Daud
Received on Sun Sep 15 2002 - 22:17:00 CDT