Re: question about automatic undo management

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Wed, 22 Jan 2003 23:41:45 +1000
Message-ID: <I2wX9.30376$jM5.77828@newsfeeds.bigpond.com>

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:BykX9.29812$jM5.77141_at_newsfeeds.bigpond.com... <snip>.
>
> Without giving away your seminar secrets, this whole issue comes down (I
> think) to 'do big rollback segments cause unnecessary I/O'. And, of so,
how
> much.
>
> I'm all ears.
>

Hi Howard,

I'm really hurt :)

There we were a few months ago discussing why large rollback segments are a potential performance problem and you ask this question again ;)

Like many things in Oracle, sizing things too small is a tuning problem but going the other way and simply making things too big can also cause problems. Undo segments are no exception and have a "right" size.

If you look in v$filestat and have a peek at the PHYWRTS column for the undo datafile(s), they are potentially the number of physical I/O you can "save" by not throwing disks at the undo tablespace but by tuning and sizing things appropriately. The number of PHYWRTS - PHYRDS are the number of truly "wasted" I/Os (clue, "strangely", the number of writes always exceeds reads). If this number is a significant proportion of your I/Os, there could be room for improvement.

Look at our previous discussion, I covered it all them.

Cheers

Richard Received on Wed Jan 22 2003 - 07:41:45 CST