Undo data is a vital component of a Oracle database environment, and when something goes wrong with undo tablespaces where this data is stored, the database administrator (DBA) must take quick action to correct the problem. Otherwise, the database could become damaged, and valuable information might be permanently lost. This chapter explains how to manage some undo problems that may occur in real-world scenarios.
Oracle Undo tablespaces were introduced in Oracle Database 9i, but not until Oracle Database 11g was the undo management mode set to AUTO by default. Automatic undo management means that Oracle DBAs no longer need to manage rollback segments manually because the Oracle database itself will manage rollback segments in a single tablespace.
As soon as an Oracle database instance starts, it looks for the UNDO_TABLESPACE parameter to determine which undo tablespace should be used; if it does not find the specified undo tablespace, the database will not start and an error will be generated in the database’s alert log. If the UNDO_TABLESPACE parameter is not being used, the database will try to find any undo tablespace in the database, and if it cannot find one, it will start to use rollback segments stored in the SYSTEM tablespace. This is not an optimal situation for database performance and is definitely not recommended. Again, in this situation, an alert message will be written to the database instance’s alert log.
The Importance of UNDO_RETENTION
When a transaction begins, it is assigned a specific undo segment in the undo tablespace. That undo segment, like all segments in a tablespace, consists of several undo extents. Until the transaction is either committed or rolled back, the undo extents for a transaction have a status of ACTIVE. However, once the transaction is completed, if other statements need those extents to maintain a read-consistent view of the data as of the system change number (SCN) at which the statement began, then the related undo extents are marked as UNEXPIRED and are retained until they are no longer needed to maintain read consistency. If there are no related statements that need to maintain a read-consistent view once the transaction has completed, then the undo extents will be marked as EXPIRED and other new transactions can reuse them.
The UNDO_RETENTION initialization parameter specifies the time in seconds that Oracle will attempt to retain undo extents before they can be overwritten by transactions that need undo space. When a transaction has already been committed and its execution time is longer than the value of UNDO_RETENTION, the status of the transaction’s undo extent changes to EXPIRED so that other transactions can reuse them. Otherwise, if the transaction itself was of short duration, then the undo extents will remain in UNEXPIRED status until the amount of time since the transaction was committed exceeds the time specified by UNDO_RETENTION.
The UNDO_RETENTION parameter also interacts with the undo tablespace differently depending on whether the tablespace’s underlying datafiles are autoextensible. If the undo tablespace’s datafiles are set to a fixed size, then Oracle will ignore the UNDO_RETENTION parameter because it cannot guarantee the value specified for the retention period without having to increase the size of the undo tablespace. On the other hand, if the undo tablespace’s datafiles are set to AUTOEXTEND instead, then Oracle will try to gracefully honor the UNDO_RETENTION parameter value by increasing the size of the undo tablespace’s datafiles instead of overwriting unexpired undo transactions.
The ultimate impact of the UNDO_RETENTION parameter and its interaction with its tablespace can be verified by checking the value for the TUNED_UNDORETENTION column of the V$UNDOSTAT dynamic view. This value is calculated and adjusted on the fly. The way it’s calculated depends on the MAXQUERYLENGTH of the query captured within the time interval, as well as on the total space usage of the undo tablespace. If your database’s application workload consists of long-running report-like queries, and its undo tablespace’s datafiles are relatively large and of fixed sized, it is unlikely to encounter a very high value for TUNED_UNDORETENTION.
This situation can sometimes cause problems for database application performance. For example, if TUNED_UNDORETENTION is set relatively high, then depending on the database’s application workload, it is possible that a lot of UNEXPIRED undo extents will result, and the database will most likely reuse them during peak data manipulation language (DML) execution periods. Reusing UNEXPIRED undo extents is potentially more expensive in terms of latches and internal work than reusing EXPIRED segments. In this situation, it may be advantageous to disable autotuning of retention values by setting the hidden initialization parameter _UNDO_AUTOTUNE to a value of FALSE; alternatively, the _HIGHTHRESHOLD_UNDORETENTION parameter can be used to specify an upper bound value for TUNED_UNDORETENTION.
Note
Tuning UNDO_RETENTION
The DBMS_UNDO_ADV package provides some excellent feedback to help determine the best value for the UNDO_RETENTION parameter:
SELECT DBMS_UNDO_ADV.LONGEST_QUERY(SYSDATE-1/24, SYSDATE)
AS best_undo_time FROM dual;
BEST_U N DO_TIME
845This output indicates that the longest-running query on this instance took 845 seconds. Based on this output, then, 845 seconds is a good starting point for the undo retention parameter. Another query that can help identify an appropriate value for UNDO_RETENTION follows:
SELECT DBMS_UNDO_ADV.REQUIRED_RETENTION(SYSDATE-30, SYSDATE)
AS reqd_retn FROM dual;
REQD_RETN
1699
This query leverages the REQUIRED_RETENTION procedure of the DBMS_UNDO_ADV package to capture the execution time of the longest-running query in the last 30 days. If you use the same input parameter in the LONGEST_QUERY procedure of the first example, you will see that the same value will be returned.
Note
DBMS_UNDO_ADVISOR is an undocumented procedure. For more information about using it, see MOS Note 1580225.1, “What is the Undo Advisor and how to use it through the DBMS_UNDO_ADV package.” Much of this information is also available via the Undo Advisor in Enterprise Manager Database Console, Grid Control, and Cloud Control.
