Automatic Tuning Of Undo_retention Causes Space Problems

Automatic Tuning Of Undo_retention Causes Space Problems

Check Whether the undo is automatically managed by the database by checking the following instance parameter: UNDO_MANAGEMENT=AUTO

Whether the undo tablespace is fixed in size:

SQL> SELECT autoextensible FROM dba_data_files WHERE tablespace_name='<UNDO_TABLESPACE_NAME>'

This returns "NO" for all the undo tablespace datafiles.
The undo tablespace is already sized such that it always has more than enough space to store all the undo generated within the undo_retention time, and the in-use undo space never exceeds the undo tablespace warning alert threshold (see below for the query to show the thresholds).
The tablespace threshold alerts recommend that the DBA add more space to the undo tablespace:

SQL> SELECT creation_time, metric_value, message_type, reason, suggested_action FROM dba_outstanding_alerts WHERE object_name='<UNDO_TABLESPACE_NAME>';

This returns a suggested action of: "Add space to the tablespace".

Or,

This recommendation has been reported in the past but the condition has now cleared:
SQL> SELECT creation_time, metric_value, message_type, reason, suggested_action, resolution FROM dba_alert_history WHERE object_name='<UNDO_TABLESPACE_NAME>';

The undo tablespace in-use space exceeded the warning alert threshold at some point in time. To see the warning alert percentage threshold, issue:

SQL> SELECT object_type, object_name, warning_value, critical_value FROM dba_thresholds WHERE object_type='TABLESPACE';

To see the (current) undo tablespace percent of space in use:

SQL> SELECT
         ((SELECT (NVL(SUM(bytes),0))
           FROM dba_undo_extents
           WHERE tablespace_name='<UNDO_TABLESPACE_NAME>'
           AND status IN ('ACTIVE','UNEXPIRED')) * 100)/       
         (SELECT SUM(bytes)
          FROM dba_data_files
          WHERE tablespace_name='<UNDO_TABLESPACE_NAME>')
         "PCT_INUSE"
     FROM dual;

Cause:

The cause of this problem has been identified in:
Bug:5387030 - AUTOMATIC TUNING OF UNDO_RETENTION CAUSING SPACE PROBLEMS
It is caused by a wrong calculation of the tuned undo retention value.
Bug:5387030 is fixed in RDBMS 11.1.

Solution:

To implement a solution for Bug:5387030, please execute any of the below alternative solutions:

Upgrade to 11.1 in which Bug:5387030 is fixed

OR

Apply patchset release 10.2.0.4 or higher in which Bug:5387030 is fixed.

OR

Download and apply interim Patch:5387030, if available for your platform and RDBMS release. To check for conflicting patches, please use the MOS Patch Planner Tool. If no patch is available, file a Service Request through My Oracle Support for your specific Oracle version and platform.

OR

Use any of the following workarounds:

1. Set the AUTOEXTEND and MAXSIZE attributes of each datafile of the undo tablespace in such a way that they are autoextensible and the MAXSIZE is equal to the current size (so the undo tablespace now has the AUTOEXTEND attribute but does not autoextend):
SQL> ALTER DATABASE DATAFILE '<datafile_flename>' AUTOEXTEND ON MAXSIZE <current_size>

With this setting, V$UNDOSTAT.TUNED_UNDORETENTION is not calculated based on a percentage of the undo tablespace size. Instead it is set to the maximum of (MAXQUERYLEN secs + 300) and UNDO_RETENTION.

2. Set the following instance parameter:
_smu_debug_mode=33554432

With this setting, V$UNDOSTAT.TUNED_UNDORETENTION is not calculated based on a percentage of the fixed size undo tablespace. Instead it is set to the maximum of (MAXQUERYLEN secs + 300) and UNDO_RETENTION.

3. Set the following instance parameter:
_undo_autotune = false

With this setting, V$UNDOSTAT (and therefore V$UNDOSTAT.TUNED_UNDORETENTION) is not maintained and the undo retention used is based on the UNDO_RETENTION instance parameter.

NOTE: This means you loose all advantages in having automatic undo management and is not an ideal long term fix.
NOTE: Even with the patch fix installed, the autotuned retention can still grow under certain circumstances. The fix attempts to throttle back how aggressive that autotuning will be. Options 2 and 3 may be needed to get around this aggressive growth in some environments.

Refernce metalink Doc ID 420525.1