Undo rentention time with autoextend on and autoextend off

In this article I will show you how undo retention time is set when we have an Undo tablespace with autoextend enabled and also disabled. I will give you rules in order to remember this easily:

Rule-algorithm #1:

if undo_management=auto

if (autoextend=on)

undo_retention is tuned based on statistics of the database.

else

undo_retention is set to the maximum possible based on the tablespace size.

else

set undo_management=auto 🙂

Remember, the undo tablespace can have many datafiles, but if it has at least 1 datafile with autoextend=on then the tablespace will use the first approach, it will set the undo retention time based on statistics of the database gathered. MMON will calculate the value of "tuned_undoretention" based on the "maxquerylen" that checks every 30 sec interval.

Oracle recommends do not have few datafiles with autoextend enabled and disabled in others on Undo tablespaces because this could make the database miscalculate the undo retention time.

I will show you few examples where we can see this deeper. 

Example 1: Autoextend = on

The undo tablespace is "UNDOTBS1" and it has only 1 datafile with autoextend=on.

SQL>  select file_name, autoextensible from dba_data_files where tablespace_name='UNDOTBS1'

FILE_NAME                        AUTOEXTENSIBLE
——————————– ————–
+DATA/orcl/undotbs01.dbf           YES

Using this approach, I can see that the current undo retention time calculated is the following:

TUNED_UNDORETENTION
——————-
1005

Example 2: Autoextend = off

But, when we have the tablespace with autoextend off, the database will set the undo retention as the maximum value possible, so you could see a value so high if you have a large undo tablespace (don't be afraid of it), and this will result in a lot of unexpired extents. If you are seeing that your undo tablespace is almost 100% used and most of that percentage is used by "unexpired" segments, don't worry, Oracle will reuse all these unexpired extents unless you have retention guarantee enabled, but usually this is not enabled.

SQL> alter database datafile '+DATA/orcl/undotbs01.dbf' autoextend off;

Database altered.

SQL> select file_name, autoextensible from dba_data_files where tablespace_name='UNDOTBS1';

FILE_NAME                   AUTOEXTENSIBLE
————————— ————–
+DATA/orcl/undotbs01.dbf    NO

Checking the retention time:

SELECT BEGIN_TIME , TUNED_UNDORETENTION FROM V$UNDOSTAT ORDER BY BEGIN_TIME;

TUNED_UNDORETENTION
——————-
4268

As you can see now the value is higher. The value is not too high because the datafile has only 5MB.

So remember, regardless if you are using autoextend=on or autoextend=off, what it is really important is if you are using auto undo management (undo_management=auto). If you are using it then ALWAYS your parameter "undo_retention" will be ignored, ALWAYS. Oracle will use the calculated value based on statistics (autoextend=on) or based on the whole size of the tablespace (autoextend=off), and undo_retention will work only as the "minimum" time, and oracle will do the best to honor it. So if you want to know what is the real undo retention time run the following query:

SELECT BEGIN_TIME , TUNED_UNDORETENTION FROM V$UNDOSTAT ORDER BY BEGIN_TIME;

What about if I want that Oracle uses the undo_retention value?

Well, if you are using auto undo management, you can force that Oracle uses this parameter. In order to do this you have to set the hidden parameter "_undo_autotune"=false. I will show you an example:

Example 1:

_undo_autotune = FALSE
undo_management = AUTO
undo_retention  = 60

Lets focus in the time that the sentences were executed:

04:17:05 SQL> update dgomez.t1 set col1=’A’ where col2=’B’;
04:17:43 SQL> select status from dba_undo_extents 
where segment_name=(Select segment_name from dba_rollback_segs where segment_id=<segment_id from v$transaction> )
and extent_id=<Extend_id from v$transaction>
group by status order by status;
04:17:50

STATUS 
——— 
ACTIVE

04:18:00 SQL> commit; (the undo retention time starts here)
04:18:02 SQL>
STATUS 
——— 
UNEXPIRED

04:18:04 SQL> 
STATUS 
——— 
UNEXPIRED

04:18:09 SQL> 
STATUS 
——— 
UNEXPIRED

04:18:59 SQL> 
STATUS 
——— 
UNEXPIRED

04:19:03 SQL> 
STATUS 
——— 
EXPIRED (The extend expired after 1 minute and the undo_retention=60 was honored.)

Example 2: Now we will see what happen normally (by default _undo_autotune is set to true).

_undo_autotune = TRUE
undo_management  = AUTO
undo_retention = 60

SELECT TUNED_UNDORETENTION FROM V$UNDOSTAT ;

TUNED_UNDORETENTION
——————-
12084

04:24:30 SQL> update dgomez.t1 set col1=’A’ where col2=’B’;
04:25:01 SQL> select status from dba_undo_extents 
where segment_name=(Select segment_name from dba_rollback_segs where segment_id=<segment_id from v$transaction> )
and extent_id=<Extend_id from v$transaction>
group by status order by status;

STATUS 
———
ACTIVE

04:25:27 SQL> commit; (The undo retention time starts here)
04:25:37 SQL> 
STATUS
——— 
UNEXPIRED

04:26:49 SQL> 
STATUS 
——— 
UNEXPIRED (undo_retention=60).

04:28:30 SQL>
STATUS 
——— 
UNEXPIRED (it's been already more than 60 secs, undo_retention was not honored.)

The value "12084" should be in place. As I said with a fixed undo tablespace size the following is more frequent and the cause if because the retention time will be the highest possible:

• Undo tablespace will get full faster
• Most of the used space will be used by unexpired extents.

rule-algorithm #2:

if undo_management=true

undo_retention works as the minimum value
real undo retention time: SELECT BEGIN_TIME , TUNED_UNDORETENTION FROM V$UNDOSTAT;

else

set undo_management=true (Recommended by Oracle)

Note: undo_retention works as the minimum value but it could not be honored by Oracle. Oracle will do the best to honor it but Oracle will overwrite unexpired extents if it is needed.