Re: How to calculate the optimal size for undo segments

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Fri, 7 Mar 2003 23:03:05 +1000
Message-ID: <8A%9a.61974$jM5.158911@newsfeeds.bigpond.com>

"Yvonne" <yeoen_at_sdu.nl> wrote in message news:724e079c.0303060219.14a75528_at_posting.google.com...
> Does anybody know how to calculate the optimal size for undo segments
> ( oracle9 i)

Hi Yvonne,

Calculating the appropriate size (and number) of undo segments is primarily a game of trial and error. Determining first the contention, then the growth and shrinks (if optimal is set), then the occurrences of ora-1555s is basically the process.

However, with 9i, an option is simply to let Oracle figure it out with it's auto undo management mechanism. Now it's simply a process of creating a large enough undo tablespace to cope with your desired undo retention period such that the tablespace doesn't fill up and ora-1555s are not occurring.

v$undostat/v$rollstat has all the necessary info for you to tune as necessary either way. Also checking the I/O rates on the undo data files (v$filestat) gives you a pretty good indication on how well you're going.

Cheers

Richard Received on Fri Mar 07 2003 - 07:03:05 CST