Oracle 11gR2 Database Undo Table Space usage is high-reproduced

The customer's database is a stand-alone database for Oracle databases 11.2.0.3.0 for AIX 6.1 64bit. Customer query Dba_free_space found that the undo table space is used up to 98%. Customer's undo table space has been manually extended to 25GB, and has been increasing, in order to undo table space can be released in a timely manner, the undo table space corresponding to all data file Auto-extension is closed. Query dba_undo_extents found that there is currently no active extent present in the UNDO table space, unexpired accounts for 60% of the total space, and 30% is expired, but Oracle does not release the space in a timely manner.

The customer's undo table space is not set to guarantee mode, so it is clear from our knowledge that the expired and unexpired in the undo table space are likely to be reused, but this high rate of undo tablespace usage is not reassuring.

Although we set parameters such as undo_retention in the initialization parameters, starting with Oracle 10GR2, the default Oracle turns on the auto-adjust feature of the undo tablespace to find V$undostat. Tuned_undoretention found that the value has been automatically adjusted to more than 3,500 minutes in the most recent period, that is to say, the data in the undo Tablespace is kept close to 3 days before it expires, because so long the data does not expire, and the tablespace is large enough, The undo tablespace was not freed, and the following explanation was found for Oracle:

Why tuned_undoretention are calculated so high making undo space grow fast?

When non-autoextensible undo space is used, tuned_undoretention are calculated based on a percentage of the undo Tablespace Size. In the some cases especially with large undo tablespace, this would make it to is calculated so large.

To fix this behaviour, Set the following instance parameter:

_smu_debug_mode=33554432

With this setting, tuned_undoretention are not calculated based on a percentage of the fixed size undo tablespace. Instead It is set to the maximum of (maxquerylen secs +) and undo_retention.

Simply put, when the undo tablespace corresponds to a data file that is not automatically extended, and the undo table space is larger, the value of tuned_undoretention is calculated based on the percentage of the undo tablespace size, and in some cases the Tuned_ The value of the undoretention is adjusted particularly large.

Workaround, if you set _smu_debug_mode=33554432, then Oracle's Undo retention auto-tuning feature is still turned on, but the calculation tuned_undoretention is based on maxquerylen secs + The 300来 calculation, rather than the percentage of undo tablespace size, avoids a particularly large value for tuned_untoretention to occur.

The above is excerpted from: "Faq–automatic Undo Management (AUM)/System Managed undo (SMU) (Doc ID 461480.1)".

We also refer to another article:

Automatic Tuning of Undo_retention causes Space problems (Doc ID 420525.1)

In this Document

Symptoms

Cause

Solution

References

Applies To:

Oracle database-enterprise edition-version 10.2.0.4 to 10.2.0.4 [Release 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 11 G

Checked for currency:13-sep-2012 * * * *

Symptoms

You are verified that Document 413732.1 are not applicable and the problem are not a misunderstanding EXPIRED is used and reused over time.

Look for:

2. Whether the undo is automatically managed by the database by checking the following instance parameter:
   Undo_management=auto
4. Whether The undo tablespace is a 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.
6. The undo tablespace is already sized such this it always have 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 F or the query to show the thresholds).
8. The tablespace threshold alerts recommend, 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 have been reported in the past but the condition have now cleared:
   Sql> SELECT creation_time, Metric_value, message_type, Reason, Suggested_action, resolution
   From Dba_alert_history
   WHERE object_name= ' <undo_tablespace_name> ';
9. 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 "undo tablespace percent of space" 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 have 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 a 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, use the MOS Patch Planner Tool. If no patch is available, the file a Service Request through My Oracle support for your specific Oracle version and platform.
  
  OR

• Use any of the following workarounds:
  
  2. Set the autoextend and MAXSIZE attributes of each datafile of the undo tablespace in such a by that they is autoextensib Le and the MAXSIZE are equal to the current size (so the the undo tablespace now have the Autoextend attribute but does not auto Extend):
     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 +) and undo_retention.
  4. 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 +) and undo_retention.
  6. 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 are based on the Undo_retention instance parameter.
     
     Note:this means you loose all advantages in have automatic undo management and is a 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 would be. Options 2 and 3 May is needed to get around this aggressive growth in some environments.

References

Bug:5387030-automatic TUNING of undo_retention causing SPACE problems
Note:413732.1-full UNDO tablespace in 10gr2 and above


This article was originally intended to solve the bug in 10gr2, but it also works in 11gr2, stating that the same bug exists in 11GR2. There are 3 solutions available in the article:

1). Adjust the data file corresponding to the undo tablespace to Auto-expand and set a maximum value for it.
sql> ALTER DATABASE datafile ' <datafile_flename> ' autoextend on MAXSIZE <current_size>

It is in this way that the customer solves the problem, after the adjustment space is quickly released, V$undostat. The tuned_undoretention value is immediately smaller, which is exactly the same as the previous explanation of the article, when the undo tablespace corresponds to the data file is automatically expanded, then V$undostat. The calculation of the Tuned_undoretention value is no longer dependent on the percentage of undo Tablespace (the Undo table space itself is larger).

2). Set _smu_debug_mode hidden parameters.
_smu_debug_mode=33554432
We have explained this parameter before, and here we verify it again.

3). Set _undo_autotune hidden parameters.
_undo_autotune = False
The previous two methods did not turn off Oracle's Undo auto-tuning retention function, set _undo_autotune to false, and turned off the auto-adjust undo retention function, Then the retention time of undo is completely dependent on the value of the initialization parameter undo_retention, which is 900 seconds by default.

Any of the three methods above can solve the problem that customers are facing.

This article is a useful addition to my other article: "Monitoring and managing the use of Oracle Undo table Space": http://blog.itpub.net/23135684/viewspace-1065601/

We learn a lot of knowledge points over a period of time may be forgotten, but through specific cases we are easier to remember.

--end--

Reprint: http://blog.itpub.net/23135684/viewspace-1406011/

Oracle 11gR2 Database Undo Table Space usage is high-reproduced