關於oracle的undo_retention

最近升級了兩個資料庫，從10.2.0.5到11.2.0。遇到了一個比較囧的問題，undo_retention的設定在兩個版本都一樣，表空間大小差不多，10.2.0.5裡能閃回的時間可以超過1天，使用率比較高。
但是11.2.0.3的使用率很少，閃回的時間控制在大概3小時。

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_in_memory_undo boolean FALSE
undo_management string AUTO
undo_retention integer 10800
undo_tablespace string UNDOTBS1

11.2.0.3 undo 使用情況：
Run Time
-----------------
25-Oct-2012 10:41

############## IN USE Undo Data ##############

PCT_INUSE
----------
7.77824668

TABLESPACE_NAME EXTENT_MAN ALLOCATIO SEGMEN RETENTION
------------------------------ ---------- --------- ------ -----------
UNDOTBS1 LOCAL SYSTEM MANUAL NOGUARANTEE

Sum of Free
----------------
19,211,091,968

Total Bytes
----------------
23,294,115,840

############## UNDO SEGMENTS ##############

Status Total Extents
--------- -------------
EXPIRED 3,574
UNEXPIRED 1,590
ACTIVE 3
-------------
sum 5,167

Status Undo Segments
---------------- -------------
ONLINE 49
OFFLINE 736
-------------
sum 785

後來發現原因如下，只是沒想到會是這樣的原因：
在原來10.2.0.5下，undo的資料檔案都是autoextend off的；
在新的11.2.0.3下，undo的資料檔案有一個autoextend on的（後來增加了一個autoextend off的）。

參考：
10g NEW FEATURE on AUTOMATIC UNDO RETENTION [ID 240746.1]

Automatic tuning of undo retention in 10g.
============================================
Up to 9i we don’t have that much control over ORA-01555 errors.  But from 10g onward we have this
new feature, automatic tuning of undo_retention, which help control ORA-01555 situation better.

Mandatory setting:
=================
UNDO_MANAGEMENT=AUTO   /* Defaults to MANUAL in 10gR2*/

How it works (Step to Test):
=============================
If UNDO_MANAGEMENT=AUTO (no matter the setting for UNDO_RETENTION) automatic undo tuning will be used.
MMON will calculate the MAXQUERYLEN every 30 sec interval.  Based upon the MAXQUERYLEN, MMON decides the
TUNED_UNDORETENTION. That means the new UNDO RETENTION will be set to TUNED_UNDORETENTION.

Note: For a fixed size undo tablespace, the database calculates the maximum undo retention period based
on database statistics and on the size of the undo tablespace. For optimal undo management, rather than
tuning based on 100% of the tablespace size, the database tunes the undo retention period based
on 85% of the tablespace size.

General calculation is
----------------------

TUNED_UNDORETENTION =  MAXQUERYLEN + 300 Sec.

If UNDO_RETENTION is set > 0, the value specified acts as a minimum setting.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/758322/viewspace-747951/，如需轉載，請註明出處，否則將追究法律責任。