Re: Undo segments and disk usage

On May 21, 12:22 pm, "David.E.M...._at_gmail.com" <David.E.Mur..._at_gmail.com> wrote:
> I have had a couple problems with undo in the past I want to make sure
> I'm on the right track. I'm using 9i with AUM and my retention is set
> to 900. First of all, I have read that 900 is just a guideline and
> oracle will overrule it if it needs to. It doesn't really matter to
> me as we don't use flashback queries.
>
> Secondly, we had a serious issue where the undo tablespace grew to
> over 12 gig and filled up a hard drives at some sites. From what I
> understand, oracle keeps 10 segments in the undo area and selects
> expired segments almost randomly for transactions. We have a weekly
> transaction that runs for about an hour and generates over a gig of
> undo. Since we have our maxsize set to unlimited, you could
> theoretically have all 10 segments grow to over a gig after a couple
> months or so. My solution to this is to split up and optimize the
> long running weekly transaction. Also, I am putting a 4 gig maxsize
> on the undo.
>
> If my concept of undo segments is correct then one could calculate the
> maximum amount of hard drive space used by your undo tablespace by
> multiplying the largest-amount-of-undo-used-per-period * number-of-
> segments. Where the 'period' in the previous equation would be the
> larger of the following: undo_retention or longest running query. In
> my case it would be 1.2gig * 10 = 12 gig of disk needed.
>
> Do you guys think this sounds reasonable?

Oh yeah, I forgot to note Note:268870.1 where it points out that if you use autoextend, the undo is much more profligate.

jg

--
@home.com is bogus.
Nah, I'm just sittin' here thinkin':
http://www.signonsandiego.com/uniontrib/20070521/news_mz1b21konrad.html