How to Tune and Setting the Undo Retention Period - Oracle DBA

Oracle Database automatically tunes the undo retention period based on how the to undo tablespace is configured. In this article, you will learn how to tune and setting the undo retention period in oracle dba.

1. If the undo tablespace is fixed size, the database tunes the undo retention period for the best possible undo retention for that tablespace size and the current system load. This tuned undo retention period can be significantly greater than the specified minimum retention period.

2. If the undo tablespace is configured with the auto-extend 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.

If you would like to Enrich your career with a Database Administrator(DBA) and get Oracle certified professional, then visit Mindmajix - A Global online training platform: “Oracle DBA Training”  Course.  This course will help you to achieve excellence in this domain.

Note:

Automatic tuning of undo retention is not supported for lobs. This because undoes 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 undoes 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.

 Related Article: What is Oracle Apps DBA Training?

Automatic tuning of Undo Retention

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.

Related Article: Managing the Redo Log

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

Related Articles:

Oracle DBA Tutorial

Oracle DBA Interview Questions

Transportable Tablespace