Re: How much undo is too much?

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Sat, 15 May 2004 16:02:43 +1000
Message-ID: <40a5b276$0$3036$afc38c87@news.optusnet.com.au>

Hans Forbrich wrote:

> rjqjunk_at_vt.edu wrote:
>
>

>>because the management of said [undo] space fills up the buffer

>
>
> Can you explain or expand on this part of your question? Or perhaps provide
> a reference (date, subject, etc) to the article in this forum?
>
> This statement seems totally contraditory with the idea that we can keep a
> significant amount of undo space for flashback-query related retention.
>
> I'm wondering whether we have a new myth-in-waiting
>
> /Hans

I didn't make the original comment, but it's hinting at the biggest mistake I've ever made with Oracle.

Believing automatic undo to be the best thing since sliced bread, and also believing the marketing hype surrounding it, it seemed to me that it would do no harm to have an enormous undo tablespace, so you had near-infinite flexibility with regard to undo_retention.

However, the algorithm Oracle uses for automatic undo goes something like this: "For each new transaction, create a new undo segment. Until some mysterious point is reached where you think there's enough undo segments, and you start making new transactions share existing segments".

One of the factors influencing Oracle's decision as to when to start sharing, and when to stop creating, is the tablespace free space available to it. If there is massive amounts of free space, Oracle postpones the point of sharing and keeps on creating new undo segments. Which means that if you create a humungously large undo tablespace, Oracle rarely gets to the point of sharing segments between transactions, but keeps creating new ones.

The principle consequence of such behaviour is that a block from an undo segment is seldom re-used. Meaning it's likely a candidate for physical I/O instead of logical I/O. A single undo segment, by contrast at the extreme other edge, would be continually re-used. Re-used blocks stay in the buffer cache, and are thus subject to a high level of logical I/O and not too much physical.

Guess which is better for performance?

However, I still contend that flashback is such a brilliant invention that I don't care very much about the physical I/O. I'd infinitely prefer not to have to perform an incomplete recovery than to have to deal with excess physical I/O. So I'm still on the side of larger-than-you-might-have-thought undo tablespaces. But I've definitely been cured of the "it really doesn't matter so go beserk" schoold of thought.

Jonathan and Richard a long time ago put me wise to the fact that this isn't a cost-free decision. And it's another nail in the 'does no harm' school of DBA management. This one (too large an undo tablespace) definitely does.

Regards
HJR Received on Sat May 15 2004 - 01:02:43 CDT