UNDO_RETENTION behaviour in autoextend undo tabelspace vs. fixed-size undo tablespace

Introduction

In this post I'd like to clarify a common misconception regarding the UNDO_RETENTION initialization parameter but first, let's start with a short introduction.
The Undo Tablespace is a key component in Oracle Databases. It consists of undo segments which hold the "before" images of the data that has been changed by users running transactions. The undo is essential for Rollback operations, Data Concurrency, Read Consistency and is being used by several flashback features (Flashback Query, Flashback Table, Flashback Version Query, Flashback Transaction Query).
As you probably know, the UNDO_RETENTION initialization parameter specifies the low threshold (in seconds) for the undo retention period. Obviously, this parameter is important and you should bear in mind the different behaviour of this parameter in fixed-size undo tablespace and in autoextend undo tablespace.

Undo Extent Types - Active/Expired/Unexpired

Active undo extents are used by running transactions. These extents will never be overwritten as they are needed in order to perform ROLLBACK operations. 

Expired Undo Extents hold committed information that is older than the UNDO_RETENTION period.

Unexpired undo Extents hold committed information that its age is less than the UNDO_RETENTION period.

You can use the following query in order to calculate the distribution of the different extent types:

select status,
       ROUND (sum_bytes / (1024 * 1024), 0)  MB,
       ROUND ((sum_bytes / undo_size) * 100, 0)  "%"
  from (  select status, SUM (bytes) sum_bytes
            from dba_undo_extents
        group by status),
       (select SUM (a.bytes) undo_size
          from dba_tablespaces c
               join v$tablespace b
                  on (b.name = c.tablespace_name)
               join v$datafile a
                  using (ts#)
         where c.contents = 'UNDO')

Sample Output:

STATUS                                 MB        %
------------------------------    ----------  ----------
ACTIVE                               1040         70
EXPIRED                                27          3
UNEXPIRED                             418         27

If the ACTIVE percent is 100 it means that all the undo extents contain undo images of running transaction and needed for performing a ROLLBACK operation and none of them can be overwritten therefore you'll get "ORA-30036: unable to extend segment by string in undo tablespace" so you need to make sure that the ACTIVE percent is not close to 100.

Also, if the UNEXPIRED pecent is close to 100 it means that almost all of the undo extents hold data of committed information that its age is less than the UNDO_RETENTION period so these undo extents may be overwritten and then the chances of encountering "ORA-01555: Snapshot Too Old" will increase. Other option is that you just set you UNDO_RETENTION to be too high and you can verify it by comparing the UNDO_RETENTION to the longest running query (MAXQUERYLEN column in V$UNDOSTAT).

Fixed-Size Undo Tablespace

For fixed-size undo tablespaces this parameter is being ignored (unless retention guarantee is enabled) and Oracle will automatically tune for the maximum possible undo retention period, based on the undo tablespace size and undo usage history.

Autoextend Undo Tablespace

For autoextend undo tablespace this parameter specifies the minimum retention period the Oracle will attempt to honor. When space in the undo tablespace becomes low (due to running transactions which generate undo records) Oracle will increase the tablespace size (up to the MAXSIZE limit) and once it will reach to the upper limit of the MAXSIZE it will begin to overwrite unexpired undo information therefore the retention period defined in the UNDO_RETENTION period is not guaranteed. This is why the actual period might be lower or even higher. The actual undo retention period can be obtained by querying the TUNED_UNDORETENTION column in V$UNDOSTAT dynamic performance view.

Note: You can specify RETENTION GUARANTEE (in the CREATE UNDO TABLESPACE or ALTER TABLESPACE commands) and then Oracle never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace.

Below is an example of a database that I have configured with UNDO_RETENTION of 15 minutes (i.e. UNDO_RETENTION = 900) but because there are queries with higher elapsed time than 900 (in my case the longest query duration is ~23 minutes (=1358 seconds) and therefore V$UNDOSTAT will report on a TUNED_UNDORETENTION which is higher than 900 in many cases (558 to be more specific). Following is a screenshot of my example:

Useful Links:

• Oracle Documentation Reference - UNDO_RETENTION
• Oracle Documentation Reference - V$UNDOSTAT
• Oracle Documentation - Managing the UNDO Tablespace