Automatic Tuning of Undo_retention Causes Space Problems [ID 420525.1]

Automatic Tuning of Undo_retention Causes Space Problems [ID 420525.1]

--------------------------------------------------------------------------------
 
  수정 날짜 09-FEB-2011     유형 PROBLEM     상태 PUBLISHED  

In this Document
  Symptoms
  Cause
  Solution
  References

 

--------------------------------------------------------------------------------

 

Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.3 - Release: 10.2 to 10.2
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 10.2.0.1 to 10.2.0.3 -- fixed by patchset 10.2.0.4 and no issues on this at 11g
Symptoms
You have verified that Note 413732.1 is not applicable and the problem is not a misunderstanding in the way EXPIRED/UNEXPIRED are used and reused over time.

Look for:

1.) System (Automatic) Managed Undo
undo_management=auto in init.ora file

2.) Fixed size undo tablespace
SQL> select autoextensible from dba_data_files where tablespace_name='<current_undo_tablespace >'

returns "No" for all the undo tablespace datafiles.

3.) 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).

4.) 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='<current_undo_ts>';

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='<current_undo_ts>';

5.) The undo tablespace in-use space exceeded the warning alert threshold at some point in time:

To see the warning alert percentage threshold:

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='<current_undo_ts>'
and status in ('ACTIVE','UNEXPIRED')) *100) /
(select sum(bytes)
from dba_data_files
where tablespace_name='<current_undo_ts>')
"PCT_INUSE"
from dual;

Cause
This is due to Bug 5387030 that is fixed in 10.2.0.4 patchset of RDBMS server.

 

Solution
Apply the patch for the Bug 5387030. Check the Oracle Support Portal for patch availability for your platform and RDBMS version.

Workaround

There are 3 possible alternate workarounds (any one of these should resolve the problem of the alerts triggering unnecessarily):

1.) Set the autoextend and maxsize attribute of each datafile in the undo ts so it is autoextensible and its maxsize is equal to its 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 v$undostat.tuned_undoretention is set to the maximum of (maxquerylen secs + 300) undo_retention specified in init.ora file.

2.) Set the following hidden parameter in init.ora file:
_smu_debug_mode=33554432

or

SQL> Alter system set "_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 v$undostat.tuned_undoretention is set to the maximum of (maxquerylen secs + 300) undo_retention specified in init.ora file.

3.) Set the following hidden parameter in init.ora:
_undo_autotune = false

or

SQL> Alter system set "_undo_autotune" = false;

With this setting, v$undostat (and therefore v$undostat.tuned_undoretention) is not maintained and the undo_retention used is the one specified in init.ora file.   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.

References
NOTE:413732.1 - Full UNDO Tablespace In 10gR2

 관련 자료

 

--------------------------------------------------------------------------------
제품
--------------------------------------------------------------------------------

•Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition
키워드
--------------------------------------------------------------------------------
TABLESPACE; UNDO_MANAGEMENT; UNDO_RETENTION; UNDO TABLESPACE