Re: Excessive Logical and Physical I/O

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

> 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? 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? Do we force smaller rollback segments and force the transaction to commit more often just to help performance? Won't frequent commits *typically* have a negative impact on performance than having a larger rollback segment and committing once? The following example comes to mind...

LOOP
  Perform DML operation
  COMMIT;
END LOOP; 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.

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