Re: Excessive Logical and Physical I/O

"Brian Peasland" <dba_at_remove_spam.peasland.com> wrote in message news:4062F9C2.D854684F_at_remove_spam.peasland.com...
> > I don't normally disagree with Brian, but I think he's missed the point
>
> Feel free to disagree with me at any time. I don't mind. ;)
>
> If I need to be corrected, then I need to be corrected.

Oh well, in that case... do something about that paper showing 50% improvements in speed because of an index rebuild!!! ;-)

> > The problem is rather that if a rbs keeps extending, it keeps acquiring
new
> > blocks instead of re-using old ones. And new blocks have to be read from
> > disk, rather than old ones being over-written in memory. Therefore,
large
> > rollback segments are associated with more physical I/O than smaller
ones.
> > And therein lies the real performance issue with rollback segments that
keep
> > growing.
>
> Point taken. However, this is starting to show that there are many
> factors at work here. What if the blocks were in memory?

Then it probably means your buffer cache is too big! I'm only being semi-serious. You're right that there is a lot at work in these matters.

>Of course
> memory access is quicker than disk I/O and with larger rollback
> segments, you increase your chances that a block will not be in memory,
> but then we have to weigh in the algorithms that keep blocks in memory
> and how that contributes to your chances that what you need will be in
> memory when you need it.
>
> And what if the rollback segments really needed to be that large to
> support the transaction?

Ah, well stop right there then. Oracle course notes have a line in them that says "developers should code shorter transactions" to try and minimise rollback use. And I've always seen red when coming across it. In my book, a transaction needs to be as big and as long as it needs to be, and if that's very big and very long because that's the logic of what a transaction is in your business, then so be it.

So no, I don't advocate extra commits just to keep rollback segment sizes down. And I don't even advocate using optimal to do the same thing. Just pointing out where the performance issues *could* lie.

>Do we force smaller rollback segments and force
> the transaction to commit more often just to help performance?

Nope.

>Won't
> frequent commits *typically* have a negative impact on performance than
> having a larger rollback segment and committing once?

Yep.

>The following
> example comes to mind...
>
> LOOP
> Perform DML operation
> COMMIT;
> END LOOP;
Hello 1555!

> So in that case, couldn't one argue that being forced to use a smaller
> rollback segment is actually hurting performance?

Absolutely.

> Lots and lots of factors at play here. And too much to spend time
> worrying about if no one is complaining about a problem.

Precisely. I've never actually quantified it either: How big does a rollback segment have to get before the additional physical I/O it causes becomes measurable and noticeable? I suspect the answer is in the multi-gigabyte range (which is why I have no problem not using and not recommending optimal). But I ought to test it some day.

Regards
HJR
>
> > Where I do agree with Brian is that you don't really have a performance
> > problem unless someone is noticing (and complaining about) it.
>
> Thanks,
> Brian
>
>
>
> --
> ===================================================================
>
> Brian Peasland
> dba_at_remove_spam.peasland.com
>
> Remove the "remove_spam." from the email address to email me.
>
>
> "I can give it to you cheap, quick, and good. Now pick two out of
> the three"
Received on Thu Mar 25 2004 - 12:09:56 CST