关于oracle UNDO表空间自动管理自动调优的原则介绍,在Oracle 10gr2后面的版本中添加了[font=\5B8B\4F53]UNDO信息最短保留时间段自动调优的特性，不再仅仅依据参数[font=\5B8B\4F53]UNDO_RETENTION的设定，其调优原则如下：

1 当[font=\5B8B\4F53]UNDO TABLESPACE为 [font=\5B8B\4F53]fixed-size，[font=\5B8B\4F53]Oracle将根据表空间的大小和历史使用情况，自动调整[font=\5B8B\4F53]undo信息保存时间，同时忽略 [font=\5B8B\4F53]undo_retention的值除非 [font=\5B8B\4F53]undo_retention的[font=\5B8B\4F53]guarantee 特性被启用。

2 当[font=\5B8B\4F53]UNDO TABLESPACE为[font=\5B8B\4F53]AUM时，[font=\5B8B\4F53]Oracle将动态调整撤销信息最短保留时间为该时段最长查询时间[font=\5B8B\4F53](MAXQUERYLEN)加上[font=\5B8B\4F53]300秒或参数[font=\5B8B\4F53]UNDO_RETENTION间的较大者，即[font=\5B8B\4F53]MAX((MAXQUERYLEN+300),UNDO_RENTION)；

在自动调整启用的情况下，实际的撤销信息最短保留时间可以通过查询[font=\5B8B\4F53]V$UNDOSTAT视图上的[font=\5B8B\4F53]TUNED_UNDORETENTION列获得。往往最短保存时间远远大于设定的[font=\5B8B\4F53]UNDO_RETENTION。在无法就[font=\5B8B\4F53]UNDO TABLESPACE做相应修改的情况，可以通过修改隐式参数”[font=\5B8B\4F53]_UNDO_AUTOTUNE”为[font=\5B8B\4F53]FALSE关闭该自动调优特性。以上设定生效后，[font=\5B8B\4F53]V$UNDOSTAT视图上[font=\5B8B\4F53]TUNED_UNDORETENTION列不再更新，且撤销信息最短保留时间固定为参数[font=\5B8B\4F53]UNDO_RETENTION的设定值。该参数可以不用重启数据库而动态设置生效。

下面做实验说明[font=\5B8B\4F53]undo自动调整的功能以及其弊端：注：实验环境中无他事务。
进行一个[font=\5B8B\4F53]dml 语句不提交，查看[font=\5B8B\4F53]dba_undo_extents 关于回滚段的信息，$ora-rac> show parameter undoNAME TYPE VALUE------------------------------------ ----------- ------------------------------undo_management string AUTOundo_retention integer 60undo_tablespace string UNDOTBS1
$ora-rac> update bind set status='VALID' where wner='test';5 rows updated.
$ora-rac> select segment_name ,tablespace_name,extent_id,status from dba_undo_extents where status='ACTIVE';SEGMENT_NAME TABLESPACE_NAME EXTENT_ID STATUS-------------------- --------------- ---------- ---------_SYSSMU3_2097677531$ UNDOTBS1 2 ACTIVE$ora-rac> commit; Commit complete.
$ora-rac> --等待一分钟之后$ora-rac> select segment_name ,tablespace_name,extent_id,status from dba_undo_extents where status='ACTIVE';SEGMENT_NAME TABLESPACE_NAME EXTENT_ID STATUS-------------------- --------------- ---------- ---------_SYSSMU3_2097677531$ UNDOTBS1 2 UNEXPIRED
$ora-rac> --等待一分钟之后$ora-rac> select segment_name ,tablespace_name,extent_id,status from dba_undo_extents where status='ACTIVE';

SEGMENT_NAME TABLESPACE_NAME EXTENT_ID STATUS-------------------- --------------- ---------- ---------_SYSSMU3_2097677531$ UNDOTBS1 2 UNEXPIRED
可以看到提交一分钟之后，回滚段_SYSSMU3_2097677531$的状态依然为UNEXPIRED，尽管undo_retention 设置为60s。本应该释放的undo却未被及时释放。其实这也是为什么生产环境中undo表空间总是接近100%的原因。由之前的介绍oracle提供的undo自动调优技术，只是将undo_retention做为一个参考值，而实际设置的undo_retention时间有v$undostat.tuned_undoretention 而定，查看其信息；
$ora-rac> ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS' ;Session altered.
$ora-rac> SELECT begin_time, end_time, tuned_undoretention FROM v$undostat;BEGIN_TIME END_TIME TUNED_UNDORETENTION------------------- ------------------- -------------------2013/10/16 19:46:29 2011/10/16 19:56:29 1412 --进行查询时候的保留时间明显大于60s2013/10/16 19:36:29 2011/10/16 19:46:29 2017 2013/10/16 19:26:29 2011/10/16 19:36:29 14162013/10/16 19:16:29 2011/10/16 19:26:29 20182013/10/16 19:06:29 2011/10/16 19:16:29 14182013/10/16 18:56:29 2011/10/16 19:06:29 20222013/10/16 18:46:29 2011/10/16 18:56:29 14212013/10/16 18:36:29 2011/10/16 18:46:29 20262013/10/16 18:26:29 2011/10/16 18:36:29 14252013/10/16 18:16:29 2011/10/16 18:26:29 2028。464 rows selected.
every coin has two sides,UNDO自动优化功能能够最大限度的使用undo表空间,满足大部分的sql执行，但是也带来一个问题：很多事务执行完毕之后，发现UNDO表空间会在很长时间都一直保持着使用率是接近100%的状态，active 状态的很少。
这种接近状态还无法手工的收缩，甚至于重启数据库实例也无法缓解，而此时常常会收到undo表空间的监控报警。
可以通过修改隐式参数”_UNDO_AUTOTUNE”为FALSE关闭该自动调优特性。以上设定生效后，V$UNDOSTAT视图上TUNED_UNDORETENTION列不再更新，且撤销信息最短保留时间固定为参数UNDO_RETENTION的设定值。该参数可以不用重启数据库而动态设置生效。
禁用UNDO自动优化之后，Oracle不再的每十分钟记录一次当前UNDO使用情况了，在动态视图V$UNDOSTAT中也只保留禁止undo自动调优之前的数据：$ora-rac> conn /as sysdbaConnected.mailto:SYS@testdb-rac]SYS@testdb-rac> alter system set "_undo_autotune"=false;System altered.$ora-rac>conn test/testConnected.$ora-rac>--10分钟之后
$ora-rac>SELECT count(1) FROM v$undostat;COUNT(1)----------464 --还是之前的个数$ora-rac>SELECT begin_time, end_time, tuned_undoretention FROM v$undostat where rownum =1;BEGIN_TIME END_TIME TUNED_UNDORETENTION------------------- ------------------- -------------------2013/10/16 19:56:29 2011/10/16 20:08:11 1592 ---
$ora-rac> SELECT begin_time, end_time, tuned_undoretention FROM v$undostat where rownum<2;BEGIN_TIME END_TIME TUNED_UNDORETENTION------------------- ------------------- -------------------2013/10/16 19:56:29 2011/10/16 21:08:53 1592 上面两个是在不同时间进行的查询， v$undostat的记录不足每十分钟进行一次统计。 再次做一个实验：这次事务提交一分钟之后，undo段的状态变为EXPIRED $ora-rac> UPDATE bind set status ='INVALID' where status='VALID';72747 rows updated.$ora-rac> ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS' ;Session altered.$ora-rac> select sysdate from dual;SYSDATE-------------------2013/10/16 20:13:41$ora-rac> commit;Commit complete.--提交时立即做查询：$ora-rac> select segment_name ,tablespace_name,extent_id,status from dba_undo_extents where segment_name='_SYSSMU3_2097677531$' or status='ACTIVE';
SEGMENT_NAME TABLESPACE_NAME EXTENT_ID STATUS-------------------- --------------- ---------- ---------_SYSSMU9_1424341975$ UNDOTBS1 0 ACTIVE --活动状态_SYSSMU9_1424341975$ UNDOTBS1 1 ACTIVE_SYSSMU9_1424341975$ UNDOTBS1 2 ACTIVE_SYSSMU9_1424341975$ UNDOTBS1 3 ACTIVE_SYSSMU9_1424341975$ UNDOTBS1 4 ACTIVE_SYSSMU9_1424341975$ UNDOTBS1 5 ACTIVE_SYSSMU9_1424341975$ UNDOTBS1 6 ACTIVE_SYSSMU9_1424341975$ UNDOTBS1 7 ACTIVE_SYSSMU9_1424341975$ UNDOTBS1 8 ACTIVE_SYSSMU9_1424341975$ UNDOTBS1 9 ACTIVE_SYSSMU9_1424341975$ UNDOTBS1 10 ACTIVE_SYSSMU9_1424341975$ UNDOTBS1 11 ACTIVE

16 rows selected$ora-rac> SELECT count(1) FROM v$undostat;COUNT(1)----------464
--过1分钟多一些时间，再次查看undo回滚段的状态：$ora-rac> SELECT begin_time, end_time, tuned_undoretention FROM v$undostat where rownum =1;BEGIN_TIME END_TIME TUNED_UNDORETENTION------------------- ------------------- -------------------2013/10/16 19:56:29 2011/10/16 20:14:50 1592

$ora-rac> select segment_name ,tablespace_name,extent_id,status2 from dba_undo_extents3 where segment_name='_SYSSMU9_1424341975$' or status='ACTIVE';

SEGMENT_NAME TABLESPACE_NAME EXTENT_ID STATUS-------------------- --------------- ---------- ---------_SYSSMU9_1424341975$ UNDOTBS1 0 EXPIRED_SYSSMU9_1424341975$ UNDOTBS1 1 EXPIRED_SYSSMU9_1424341975$ UNDOTBS1 2 EXPIRED_SYSSMU9_1424341975$ UNDOTBS1 3 EXPIRED_SYSSMU9_1424341975$ UNDOTBS1 4 EXPIRED_SYSSMU9_1424341975$ UNDOTBS1 5 EXPIRED_SYSSMU9_1424341975$ UNDOTBS1 6 EXPIRED_SYSSMU9_1424341975$ UNDOTBS1 7 EXPIRED_SYSSMU9_1424341975$ UNDOTBS1 8 EXPIRED_SYSSMU9_1424341975$ UNDOTBS1 9 EXPIRED_SYSSMU9_1424341975$ UNDOTBS1 10 UNEXPIRED_SYSSMU9_1424341975$ UNDOTBS1 11 EXPIRED12 rows selected.
可见，修改隐式参数” _UNDO_AUTOTUNE”为FALSE关闭该自动调优特性，可以解决表空间的使用率总是100%的问题。附上DBA_UNDO_EXTENTS，STATUS的值及其意义：ACTIVE 活动状态，说明当前这个数据区被某个正在进行的事务使用。EXPIRED 已过期，说明已分配的数据区已经完成了它的使命，随时可以被分配给其它新的事务使用。UNEXPIRED 未过期，说明分配的数据区已经不属于任何的活动事务，但是由于UNDO RETENTION设置的需要，一般情况下不会被回收重用。
相关文章：FAQ – Automatic Undo Management (AUM) / System Managed Undo (SMU) [ID 461480.1]Ora-30036 On Undo Tablespace After Upgrade To 10.2.0.4 [ID 856344.1]