Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Excessive Logical and Physical I/O

Re: Excessive Logical and Physical I/O

From: Howard J. Rogers <>
Date: Fri, 26 Mar 2004 05:09:56 +1100
Message-ID: <40632076$0$31907$>

"Brian Peasland" <> wrote in message
> > 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
> > 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,
> > rollback segments are associated with more physical I/O than smaller
> > And therein lies the real performance issue with rollback segments that
> > 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?


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


>The following
> example comes to mind...
> Perform DML operation
Hello 1555!

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


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

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

Original text of this message