Re: optimal size for rollback

"Igor Laletin" <ilaletin_at_usa.net> wrote in message news:f9226414.0209151917.521c2cc3_at_posting.google.com...
> "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.
>

One presumes we are dealing with trained, knowledgeable DBAs.

> > 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).
>

Optimal is never useful when there is a full-time DBA who knows what they're doing. It is extremely useful for part-time DBAs, or DBAs who have 58 databases to manage and can't spend proper time with any of them. It's also convenient for those "DBAs" who don't have a clue, and don't have the gumption to get a clue!

> 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.

Yes, I think we *know* how optimal works. The sting in the tail are your words "it will be shrinked back after some time". That "some time" is more accurately stated as "in the middle of a subsequent transaction", and I'd rather not have my transaction slowed down doing a shrink of a rollback segment that yours caused to grow, thanks all the same.

Your discussion about the "usual transaction size" is, incidentally, completely and utterly wrong, since it isn't the size of the transaction that causes rollback segments to grow (unless you really are a DBA who hasn't a clue, and can't size the things even approximately in the first place). It's the propensity of users to leave transactions uncommitted for any length of time that will cause growth, and there is nothing you can do to avoid that. You can exhort users to best practice all you want, but it only takes one to forget to commit, and you have a blocking transaction on your hands, and rollback segment growth will ensue.

That's bad enough, but it's something you just have to live with.

Shrinking is exactly the same problem in reverse -but you don't have to live with it, and not setting optimal cures the problem at a stroke.

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

Well, since your scenario is missing the essential problem, I dare say you would reach that conclusion. But in real life, unnecessary extent deallocation in the middle of a transaction can be a significant performance impactor, and accordingly optimal is a bad idea.

Growth *will* happen, whatever your transaction sizes (thank you, Users). Shrinkage need only happen at a time and place of your choosing.

Regards
HJR
> 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 Mon Sep 16 2002 - 03:56:00 CDT