Re: optimal size for rollback

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

...

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

Doesn't matter, everybody makes mistakes. In any case it's unnecessary administrative overhead.  

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

"DBA who knows" has better things to do than supporting re-implementation of the standard feature.

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

You probably should choose between "Don't set optimal. Period." and "extremely useful for part-time DBAs ..." :)

Also 58 is not something outstanding and I worked on the sites with twice as many dbs.

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

It's a few transactions out of thousands. OP says "occasionally I have some big jobs". If occasional deallocation is so big problem you're probably on a wrong hardware. In real life it wouldn't be even noticed. OK, some user _maybe_ will notice that one of a thousand clicks he/she makes during a day took slightly longer. So?

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

??? OK, it's size of _large_ transaction.

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

I presume we're talking about production systems. Users are isolated from a database by an application. Normally they can't connect to db and modify data directly. Although it happens sometimes but it's an exception, not a rule.  

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

Users leaving transactions uncommitted? It's an exception in a production environment. May happen but "essential problem"? C'mon.

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

Real life is somewhat different from a training room.  

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

Just choose wisely. Db usage patterns may be different for, say, different days of the week. Also business is changing. A perfectly good time may become perfectly bad.  

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 Tue Sep 17 2002 - 00:08:44 CDT