Re: optimal size for rollback

"Igor Laletin" <ilaletin_at_usa.net> wrote in message news:f9226414.0209162108.451dfc8_at_posting.google.com... [snip]
> > 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?
>

You're simply incorrect on this. Sorry.

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

Still missing the point. The inadvertent growth of rollback segments has everything to do with uncommitted transactions, and nothing to do with the size of transactions.

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

Eh? Applications talk to databases. Users use applications. Ergo, users are not "isolated" from a database, whatever that's supposed to mean.

>Normally they can't connect to db
> and modify data directly.

Who cares whether they modify it directly or not? They modify data, they generate rollback. If that modification isn't committed, you've got rollback segment growth hovering in the wings.

>Although it happens sometimes but it's an
> exception, not a rule.

Says you.

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

Yeah, "c'mon". It happens.

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

I see. You think this is all just training room theory. Think again.

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

On that basis, let's not make any scientific assessments of how Oracle behaves, huh?

End of thread.
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 Tue Sep 17 2002 - 04:20:27 CDT