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?

No. Oracle does not necessarily use expired extents, and has other issues that can be set off simply from code it doesn't like. See not- -bug 4244922 on metalink, and browse around "all sources" in the knowledgebase for the term 30036 (not that you are approaching that, just it finds interesting discussions as a search term - though you might if you maxsize). Also, asktom.oracle.com has some interesting clarifications - Tom's general feeling is to size the db for as big as it needs to be and forget about it. He may support your concept, I'm not sure as it's been a while since I've looked at that there in detail.

Some things you might consider:
What exact version are you on?
What exactly is your long-running code doing? Are you mass-deleting anything? Mass-updating indices? Committing in a loop? Considering committing in a loop?
Collecting info from dba_undo_extents, v$undostat and v$transaction?

http://www.akadia.com/services/ora_optimize_undo.html may be helpful.

Deletes are going to use up way more undo than anything else, as it has to keep all the before image data.

jg

--
@home.com is bogus.
http://en.wikipedia.org/wiki/Rubber-hose_cryptanalysis