Re: optimal size for rollback

"Alex Filonov" <afilonov_at_yahoo.com> wrote in message news:336da121.0209160712.69e0659d_at_posting.google.com...
> 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.
>

Any tool that encourages its own misuse is probably intrinsically bad.

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

5 is not 58. You can manage 5 full-time, and properly. 10, even. More than that, some databases usually turn out to be rather more important to manage right than others.

[snip]
>
> 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.

Well, your experience aside for one moment: the size of transactions has got bugger all to do with a rollback segment's propensity to grow and shrink. We're not talking anecdotal evidence here, unique to you, but theory that applies to every single rollback segment on the planet.

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

You can tune the number of transactions per rollback segment to your heart's content, and arrive at a "perfect" ratio. And you'll *still* have trouble when one transaction out of thousands gets left uncommitted.

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

WOOOOOOSH! (That's the sound of the point flying over your head: size of transactions has nothing to do with rollback segment growth, assuming you've sized them properly in the first place. Growth happens because users do daft things).

I'll try and make it even simpler:

No DBA can prevent unexpected rollback segment growth. Every DBA can prevent unexpected rollback segment shrinkage. The cost of unexpected shrinkage is that performance suffers. The cost of not allowing shrinkage to happen is disk space.

Anecdotal evidence, and personal experiences aside, those are just plain, hard facts, and not matters of opinion. You weigh them and come up with your own response. Like every other DBA out there, you are free to opt for convenience over (what you may consider to be) a small performance hit. That's a valid choice, but it's not something I would do, nor encourage others to do without considerable testing and thought.

Regards
HJR Received on Mon Sep 16 2002 - 14:40:03 CDT