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.
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.
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.
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.
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.
Do one of the following:
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
Ravindra Savaram is a Content Lead at Mindmajix.com. His passion lies in writing articles on the most popular IT platforms including Machine learning, DevOps, Data Science, Artificial Intelligence, RPA, Deep Learning, and so on. You can stay up to date on all these technologies by following him on LinkedIn and Twitter.