How to Tune and Setting the Undo Retention Period in Oracle DBA
Automatic tuning of Undo Retention:
Oracle database automatically tunes the undo retention period based on how the undo tablespace is configured.
- If the undo tablespace is fixed size, the database tunes the retention period for the best possible undo retention for that tablespace size and the current system load. This tuned retention period can be significantly greater than the specified minimum retention period.
- If the undo tablespace is configured with the autoextend option, the database tunes the undo retention period to be somewhat longer than the longest-running query on the system at that time. Again, this tuned retention period can be greater than the specified minimum retention period.
Automatic tuning of undo retention is not supported for lobs. This is because undo information for lobs is stored in the segment itself and not in the undo tablespace. For lobs, the database attempts to honor the minimum undo retention period specified by undo_retention. However, if space becomes low, unexpired lob undo information may be overwritten.
You can determine the current retention period by querying the tuned_undoretention column of the v$undostat view. This view contains one row for each 10-minute statistics collection interval over the last 4 days. (beyond 4 days, the data is available in the dba_hist_undostat view.) Tuned_undoretention is given in seconds.
Sql> select to_char(begin_time, ‘dd-mon-rr hh24:mi’) begin_time,
To_char(end_time, ‘dd-mon-rr hh24:mi’) end_time, tuned_undoretention
From v$undostat order by end_time;
Begin_time end_time tuned_undoretention
———————— ——————– ———————————–
04-feb-05 00:01 04-feb-05 00:11 12100
07-feb-05 23:21 07-feb-05 23:31 86700
07-feb-05 23:31 07-feb-05 23:41 86700
07-feb-05 23:41 07-feb-05 23:51 86700
07-feb-05 23:51 07-feb-05 23:52 86700
576 rows selected.
Undo retention tuning and alert thresholds:
For a fixed size undo tablespace, the database calculates the maximum undo retention period based on database statistics and on the size of the undo tablespace. For optimal undo management, rather than tuning based on 100% of the tablespace size, the database tunes the undo retention period based on 85% of the tablespace size, or on the warning alert threshold percentage for space used, whichever is lower. (the warning alert threshold defaults to 85%, but can be changed.) Therefore, if you set the warning alert threshold of the undo tablespace below 85%, this may reduce the tuned length of the undo retention period.
Setting the Undo Retention period
You set the undo retention period by setting the undo_retention initialization parameter. This parameter specifies the desired minimum undo retention period in seconds. The current undo retention period may be automatically tuned to be greater than undo_retention, or, unless retention guarantee is enabled, less than undo_retention if space is low.
To set the undo retention period:
- Do one of the following:
- Set undo_retention in the initialization parameter file.
- Undo_retention = 1800
- Change undo_retention at any time using the alter system statement:
- Alter system set undo_retention = 2400;
The effect of an undo_retention parameter change is immediate, but it can only be honored if the current undo tablespace has enough space.
For Further Posts please visit our blog
Enroll for Instructor Led Live OpenStack Training