RE: Undo Usage and Read consistency - ORA-1555 Correction
Date: Tue, 14 Jul 2009 16:35:33 -0400
Message-ID: <E6D4C8023B9643229766C6AB243EFE2E_at_rsiz.com>
That is an interesting way to look at it, and not invalid, but it does omit one case.
That case is when the query that ultimately becomes the victim of the evaporated read consistent UNDO would have seen the changed value because the more aggressive commits would have occurred before the query started. That is the sense in which a commit helps the situation.
So, continuing this viewpoint, it can been seen that depending on how very long in duration change transactions are, having more frequent commits might spare some queries because there would tend to be fewer outstanding blocks in UNDO when each query starts.
But it may also be the way you see it - at least the uncommitted transaction continues to pin the undo blocks so a subsequent query dependent on just that transaction's blocks of UNDO for read consistency cannot fall victim to 1555. The transaction might break, in which case it will be rolled back, but the UNDO cannot disappear while it might still be needed to roll back or present a read consistent "snapshot" (as in snapshot too old).
The fundamental problem is a mismatch between the triplet of transaction duration and undo volume, query duration, and the amount of undo available to support a sufficient UNDO retention.
When there is sufficient UNDO space and retention is specified appropriately to support the transaction and query volumes and duration, the more frequent commits will not actually cause 1555.
Unless it is plausible to tightly tune UNDO space to keep it in cache and the amount of UNDO you are left with rarely generates 1555 in your particular situation, I have long held that a gigantic over-availability of UNDO and a long retention time will serve you well. Tuning UNDO tightly is usually a mistake. Reasonably frequent commits, as long as compatible with transaction restarts are usually a good idea. As you've written, EXCESSIVE commits is usually a mistake as well.
I think I know what I mean, and I hope I wrote it well enough to be understandable.
Regards,
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Baumgartel, Paul
Sent: Tuesday, July 14, 2009 3:48 PM
To: oracle-l_at_freelists.org
Subject: RE: Undo Usage and Read consistency - ORA-1555 Correction
I fail to see how a commit helps this situation. Usually it's excessive commits (like periodic commits in a long-running operation) that contribute to this problem, by freeing up undo blocks to be re-used and overwritten.
<snip>
==
Please access the attached hyperlink for an important electronic communications disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jul 14 2009 - 15:35:33 CDT