If you wish to change the amount of time the database should retain the undo information, you
can dynamically change the UNDO_RETENTION parameter as follows:
SQL> ALTER SYSTEM SET UNDO_RETENTION = 7200 /* two hours
There is no one ideal UNDO_RETENTION time interval. Your retention time interval will depend
on how long you estimate your longest transactions may run. Based on the information about the
maximum length of transactions in your database, you can arrive at an approximate time to assign
for the UNDO_RETENTION parameter.
The V$UNDOSTAT table provides an indicator for helping figure out the undo retention
interval. Query the V$UNDOSTAT view as follows:
SQL> SELECT MAX(maxquerylen) FROM v$undostat;
MAX(MAXQUERYLEN)
----------------
210
The maxquerylen column of the V$UNDOSTAT view tells you the length of the longest executed
query (in seconds) during the past 24 hours. The time set in the UNDO_RETENTION parameter should be
at least as long as the time indicated in the maxquerylen column. This, by itself, won’t guarantee that
a new long-running query will definitely be accommodated, but you’ll have a reasonable chance that
your longest transactions will have read consistency when using the undo tablespace
=============
Oracle provides the following guidelines for setting the undo retention interval for a new
database:
• OLTP: 15 minutes
• Mixed: 1 hour
• DSS: 3 hours
• Flashback Query: 24 hours
=========
Guaranteed undo retention simply means that Oracle will retain undo data for the entire length
of the undo retention period you specify, no matter what. That is, if you specify half an hour as the
undo retention interval, Oracle will retain the undo data for the full 30 minutes, under all circumstances.
If you run out of room for recording the undo information generated by new transactions,
any new DML transactions will fail, since Oracle won’t be able to store the undo information for
those changes. Thus, there is a trade-off between guaranteeing undo information and the potential
failure of some DML statements.
■Tip By default, Oracle doesn’t guarantee undo retention; the default retention time is 900 seconds (15 minutes)
if you decide to guarantee undo retention.
SQL> SHOW PARAMETER UNDO
NAME TYPE VALUE
----------------- ------- -------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS_01
SQL>
No comments:
Post a Comment