** UNDO Retention **

[quester]

Reagrding Undo Retention:

Long-running queries sometimes fail because undo information required for consistent read operations is no longer available. This happens when committed undo blocks are overwritten by active transactions.

Can someone explain. I didn't follow. When undo blocks (original data) is committed (changed data), I guess the Undo Segments get cleared. So when such is the case, how can Undo Blocks be overwritten by Active Transactions? Kindly someone explain.

[adewri]

Originally posted by quester
Reagrding Undo Retention:
Can someone explain. I didn't follow. When undo blocks (original data) is committed (changed data), I guess the Undo Segments get cleared. So when such is the case, how can Undo Blocks be overwritten by Active Transactions? Kindly someone explain.

Undo retention as the name says is the time period for which the grown rollback segment (with undo data) will retain its grown size. As growing and shrinking of rollback segments is a costly operation so Oracle has made this way to keep the grown segments in grown state for the time period mentioned in the UNDO_RETENTION.

After the time is out it will shrink to its original size, unless another active transaction starts overwritting it preventing it from shrinking.

You can always change the value of UNDO_RETENTION.

Originally posted by quester
Long-running queries sometimes fail because undo information required for consistent read operations is no longer available. This happens when committed undo blocks are overwritten by active transactions

http://asktom.oracle.com/pls/ask/f?p...:275215756923,

HTH

[SANJAY_G]

Undo retention as the name says is the time period for which the grown rollback segment (with undo data) will retain its grown size. As growing and shrinking of rollback segments is a costly operation so Oracle has made this way to keep the grown segments in grown state for the time period mentioned in the UNDO_RETENTION.

This concept of rollback segment is not true when Automatic Undo Management is used.

UNDO_RETENTION parameter specifies the time for which Oracle will try to retain the UNDO information.

Active transactions overwrite the UNDO information only if there is no free space left in the UNDO tablespace and the transaction needs it.

On the other hand even if the UNDO_RETENTION is specified but the transaction needs the UNDO space and no free space available it will overwrite the information, which was supposed to be retained as specified by UNDO_RETENTION.
So there should be enough space available in UNDO tablespace to support UNDO_RETENTION.

UNDO_RETENTION if properly set can reduce the risk of "Snapshot too old" error. It also is an important factor when Flshback Queries are used.

HTH

[Thomasps]

Hi

UNDO_RETENTION if properly set can reduce the risk of "Snapshot too old" error. It also is an important factor when Flashback Queries are used.

I think the "Snapshot too old" error based on the size of the undo space available. And UNDO_RETENTION is mainly for Flashback Queries. Or can we relate both in the same degree..? (Definitely it is related, but is it in the same level?)


Thomas

[quester]

THanks a lot guys.

[adewri]

i think i have mixed up uncommited data with the size of the rollback segment... Sanjay is correct.

you can specify the retention period of the committed undo data by using the parameter UNDO_RETENTION. For example, if UNDO_RETENTION is set to 30 minutes, all committed undo information in the system will be retained for at least 30 minutes. This ensures that all queries running for thirty minutes or less will not get the OER (snapshot too old) error, under normal circumstances.

Thanks Sanjay for pointing that out

and yes UNDO_RETENTION is in seconds, default in 900 secs.

HTH

[SANJAY_G]

Thomas,
It is difficult to say at what level but they are closely related.
And UNDO_RETENTION plays an important role in possibility of getting "snapshot too old".
http://www.csis.gvsu.edu/GeneralInfo...undo.htm#10746

[guru_heaven]

Originally posted by Thomasps
Hi



I think the "Snapshot too old" error based on the size of the undo space available. And UNDO_RETENTION is mainly for Flashback Queries. Or can we relate both in the same degree..? (Definitely it is related, but is it in the same level?)


Thomas

just think a bit

what causes snapshot too old? it happens when the undo blocks are overwritten

what does undo retention do? it tell Oracle NOT to overwrite undo blocks for a certain time

how does undo retention fix snapshot tool old?

if I have several batch jobs running, if I want to avoid snapshot too old for one of them which takes 30 minutes for example I would set undo retention to 30 minutes so the process can finishes in 30 minutes and none of its undo blocks would be used/overwritten by other processes