Re: optimal size for rollback

IMHO optimal, as any other tool, is just a tool. It can't be good or bad per se, it's how you use it. See my comments below.

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<3d859ca1_at_dnews.tpgi.com.au>...
> "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

You've ever seen any other DBAs? I mean, I never seen one managing less than 5. May be they exist in some perfect world, I want to live and work there.

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

As per my experience, it's huge transactions cause RBS to grow huge. When you have online users with uncommited transactions, those transactions are small. They would never cause rollback segments to grow big, unless you have to many (average) transactions per RBS. Yes, this is one more thing to worry about.

Of course, uncommitted transactions are bad, sometimes you spend lots of time looking why some transaction is waiting for lock and end up with chain of uncommited transaction locking different rows needed for this transaction.

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

If most of your transactions don't cause RBS grow above OPTIMAL, it's not a problem at all. So this is just a problem of proper choice for OPTIMAL.

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

My opinions can be (and sometimes are) wrong and have nothing to do with my employer.

Cheers,

Alex Received on Mon Sep 16 2002 - 10:12:44 CDT