Oracle11gR2DatabaseUNDO表空間使用率居高不下

客戶的數(shù)據(jù)庫是Oracle Database 11.2.0.3.0 for AIX 6.1 64bit的單機(jī)數(shù)據(jù)庫。客戶查詢DBA_FREE_SPACE發(fā)現(xiàn)UNDO表空間的使用率高達(dá)

客戶的數(shù)據(jù)庫是Oracle Database 11.2.0.3.0 for AIX 6.1 64bit的單機(jī)數(shù)據(jù)庫。客戶查詢DBA_FREE_SPACE發(fā)現(xiàn)UNDO表空間的使用率高達(dá)98%以上。客戶的UNDO表空間已經(jīng)手動(dòng)擴(kuò)展到了25GB，且一直在增加，為了UNDO表空間能及時(shí)的被釋放，UNDO表空間對(duì)應(yīng)的所有數(shù)據(jù)文件自動(dòng)擴(kuò)展都被關(guān)閉。查詢DBA_UNDO_EXTENTS發(fā)現(xiàn)在UNDO表空間中當(dāng)前沒有ACTIVE的EXTENT存在，UNEXPIRED的占到總空間的60%，有30%是EXPIRED，但Oracle并沒有及時(shí)的釋放這些空間。

客戶的UNDO表空間并沒有設(shè)置成GUARANTEE模式，所以根據(jù)我們的知識(shí)都明白UNDO表空間中的EXPIRED和UNEXPIRED都是可能被重用的，但是這么高的UNDO表空間使用率看著讓人不踏實(shí)。

雖然我們在初始化參數(shù)中設(shè)置了UNDO_RETENTION等參數(shù)，但從Oracle 10gR2開始，默認(rèn)Oracle都開啟了UNDO表空間的自動(dòng)調(diào)整功能，查找V$UNDOSTAT.TUNED_UNDORETENTION發(fā)現(xiàn)最近一段時(shí)間該值都被自動(dòng)調(diào)整到了3500多分鐘，也就是說UNDO表空間中的數(shù)據(jù)要保留接近3天才會(huì)過期，正是因?yàn)檫@么長的數(shù)據(jù)未過期時(shí)間，且表空間又足夠的大，才導(dǎo)致了UNDO表空間的空間一致未被釋放，同時(shí)也找到了Oracle下面的一段解釋：

Why TUNED_UNDORETENTION is calculated so high making undo space grow fast ?

When non-autoextensible undo space is used, tuned_undoretention is calculated based on a percentage of the undo tablespace size. In some cases especially with large undo tablespace, This will make it to be calculated so large.

To fix this behaviour, Set the following instance parameter:

_smu_debug_mode=33554432

With this setting, 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.

簡單的說，就是當(dāng)UNDO表空間對(duì)應(yīng)的數(shù)據(jù)文件非自動(dòng)擴(kuò)展，，且UNDO表空間又比較大的時(shí)候，tuned_undoretention的值是根據(jù)UNDO表空間大小的百分比來計(jì)算的，在一些情況下會(huì)將tuned_undoretention的值調(diào)整得特別大。

解決辦法，如果設(shè)置_smu_debug_mode=33554432，那么Oracle的UNDO RETENTION自動(dòng)調(diào)整功能依然被開啟，但是計(jì)算tuned_undoretention是根據(jù)MAXQUERYLEN secs +300來計(jì)算，而不是根據(jù)UNDO表空間大小的百分比來計(jì)算，這樣就可以避免TUNED_UNTORETENTION出現(xiàn)特別大的值。

以上內(nèi)容摘自：《FAQ – Automatic Undo Management (AUM) / System Managed Undo (SMU) (Doc ID 461480.1)》。

同樣我們還參考了另一篇文章：

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 this at 11g

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

Symptoms

You have verified that Document 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. Whether the undo is automatically managed by the database by checking the following instance parameter:

UNDO_MANAGEMENT=AUTO

2. Whether the undo tablespace is fixed in size:

SQL> SELECT autoextensible
FROM dba_data_files
WHERE tablespace_name=''

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

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

5. 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=''
AND status IN ('ACTIVE','UNEXPIRED')) * 100)/
(SELECT SUM(bytes)
FROM dba_data_files
WHERE 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.

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com