近期，在客户的某套库中，发现undo表空间使用率过高，释放的空间少。其中原因可能是由于Oracle 10g的新特性undo_retention自动化调整造成。具体内容如下：
Oracle 10g中引入了一个新的自动调整undo_retention的特性，其目的是为了减少ORA-01555错误出现的概率，Oracle会忽略UNDO_RETENTION参数设置的阀值，而是根据UNDO表空间的大小和使用率来自动调整UNDO信息的保留时间。会造成的影响是UNDO表空间的区（extent）中大部分都是未过期状态(unexpired)，这就会导致数据库在给事务分配UNDO块时，会优先使用UNDO表空间的的空闲空间分配，而不是覆盖已经分配的空间，这使得UNDO表空间的使用率保持在一个较高的水平。
Oracle数据库在为事务进行分配UNDO块时，会按照这样的算法和流程：
1． 如果当前区（extent）中还有空闲块，在需要空间时会继续使用本区（extent）中的空闲块。
2． 在当前区(extent)使用完后，如果下一个区（extent）是过期状态(expired)，那么就跳转到下一个区(extent)的第一个数据块。
3． 如果下一个区(extent)不是过期状态(expired)，就从UNDO表空间申请空间，如果UNDO表空间中存在空闲的空间，就分配新的区(extent)加入到undo segment，然后跳转到新区(extent)的第一个数据块。
4． 如果没有剩余空闲的区(extent),则会从OFFLINE状态的回滚段中窃取（STEAL）过期的区，加入当前的回滚段，并使用第一个数据块。
5． 如果OFFLINE状态的回滚段中没有过期的区，那么会从ONLINE状态的回滚段窃取（STEAL）过期的区加入当前的回滚段，并使用第一个数据块。
6． 如果UNDO表空间能够自动扩展，则会扩展UNDO表空间，并将新区加入到当前回滚段中。
7． 如果undo表空间数据文件不能扩展，调低10%的retention值，然后窃取（STEAL）在短保留时间的过期区，如果还未找到过期区，则继续以10%的速度减少回滚的保留时间。
8． 随机从其他OFFLINE状态的回滚段中窃取（STEAL）未过期的(unexpired)的区。
如果以上的尝试都失败，那么久会报ORA-30036错误。
从上面的步骤可以看出，事务会优先使用UNDO空闲空间、过期状态(expired)的UNDO区，然后会尝试扩展表空间的数据文件，只有在以上步骤都得不到获得UNDO表空间后，才会去使用未过期(unexpired)的UNDO区。
而Oracle数据库回收UNDO extent使其成为UNDO表空间的空闲空间，其方式有以下几种：
1. SMON进程周期性地回收过期状态的undo extent，对于OFFLINE的UNDO 段，会回收所有的过期的undo extent，而对于ONLINE的UNDO段，回收时会根据 undo_retention时间和undo块的使用率来确定回收的数量，undo_retention越大，能回收的undo空间就越少。
2. 进程在给事务分配undo块时，会回收多余的过期状态的undo extent。
因此，如果系统中未过期状态的undo exten 越多，那这些undo块就不会返回给undo表空间而成为空闲空间。造成的结果就是UNDO表空间的已分配空间一直很多，但是使用率却很高。
解决方案：
针对此问题，解决方案是关闭undo retention的自动优化特性，即将数据库参数_undo_autotune设置为FALSE,具体为：
Alter system set “_undo_autotune”=false;
该参数可以在线调整，不需要重启数据库，也不会影响系统额正常运行。
也可以设置隐藏参数_sum_debug_mode,具体为：
Alter system set “ _sum_debug_mode”=33554432;
如果undo_retention参数目前值在10800以下，建议设置为：10800
Alter system set undo_retention=10800；
分析过程：
1. 查看undo相关的信息
1） UNDOTBS2 表空间active、expired、unexpired状态比例
STATUS SUM(BYTES)/1024/1024 COUNT(*)
------------------------------------ -------------------- ----------
EXPIRED 103599.188 19580
ACTIVE 192 3
UNEXPIRED 4 4
可以看出在UNDOTBS2表空间的空间分配中，大部分属于过期(expired)状态
2） 表空间使用率状况
Tablespace Size (MB) Free (MB) % Free % Used
----------------------- ------------ ------------- ----------- --------------
UNDOTBS1 64190 62798 98 2
UNDOTBS3 128000 85717.5 67 33
UNDOTBS2 104000 74 0 100
UNDO表空间的使用率不均匀，UNDOTBS2的表空间使用率达到100%，而其他表空间为未40%。
3）_undo_autotune的设置情况
NAME VALUE
----------------------------- -----------------------------
_undo_autotune TRUE
3） undo_retention自动调整情况：
BEGIN_TIME ACTIVEBLKS UNEXPIREDBLKS EXPIREDBLKS TUNED_UNDORETENTION
------------------------------ ---------- ------------- ----------- -------------------
08/12/2015 22:22:46 3568 13300520 2176 5485009
08/12/2015 22:12:46 11760 13284008 2176 5469730
08/12/2015 22:02:46 11760 13284008 2176 5498797
08/12/2015 21:52:46 3568 13292072 2176 5493784
08/12/2015 21:42:46 3568 13292072 2176 5499555
08/12/2015 21:32:46 3568 13292072 2176 5497755
08/12/2015 21:22:46 3568 13292072 2176 5496681
08/12/2015 21:12:46 3568 13292072 2176 5488032
08/12/2015 21:02:46 3568 13292072 2176 5481300
08/12/2015 20:52:46 11760 13283880 2176 5472885
08/12/2015 20:42:46 3568 13291688 2304 5480721
BEGIN_TIME ACTIVEBLKS UNEXPIREDBLKS EXPIREDBLKS TUNED_UNDORETENTION
------------------------------ ---------- ------------- ----------- -------------------
08/12/2015 20:32:46 3568 13291688 2304 5480861
08/12/2015 20:22:46 3568 13291688 2304 5485670
08/12/2015 20:12:46 3568 13291688 2304 5483425
08/12/2015 20:02:46 8688 13286568 2304 5481056
08/12/2015 19:52:46 3568 13291688 2304 5479901
08/12/2015 19:42:46 3568 13275304 10496 5477658
08/12/2015 19:32:46 3568 13275304 10496 5477398
08/12/2015 19:22:46 3568 13274280 10496 5475244
08/12/2015 19:12:46 3568 13274280 10496 5469633
08/12/2015 19:02:46 3568 13274280 10496 5471628
08/12/2015 18:52:46 3568 13274280 10496 5463911
BEGIN_TIME ACTIVEBLKS UNEXPIREDBLKS EXPIREDBLKS TUNED_UNDORETENTION
------------------------------ ---------- ------------- ----------- -------------------
08/12/2015 18:42:46 3568 13273256 10496 5483522
08/12/2015 18:32:46 3568 13273256 10496 5492989
08/12/2015 18:22:46 3568 13265064 10496 5489900
08/12/2015 18:12:46 3568 13297832 10496 5488577
08/12/2015 18:02:46 3568 13297832 10496 5487795
08/12/2015 17:52:46 3568 13297832 10496 5481165
08/12/2015 17:42:46 3568 13297832 10496 5477806
08/12/2015 17:32:46 3568 13297832 10496 5466334
08/12/2015 17:22:46 3568 13297832 10496 5458939
08/12/2015 17:12:46 3568 13297832 10496 5454334
08/12/2015 17:02:46 16880 13291688 2304 5452345
BEGIN_TIME ACTIVEBLKS UNEXPIREDBLKS EXPIREDBLKS TUNED_UNDORETENTION
------------------------------ ---------- ------------- ----------- -------------------
08/12/2015 16:52:46 3568 13291816 10496 5381496
08/12/2015 16:42:46 19952 13278888 10496 5436678
08/12/2015 16:32:46 3568 13295272 10496 5617889
08/12/2015 16:22:46 3568 13295144 10496 5612595
08/12/2015 16:12:46 11760 13286696 10624 5642189
08/12/2015 16:02:46 28144 13278504 2304 5657143
08/12/2015 15:52:46 24688 13281832 2304 5669423
08/12/2015 15:42:46 11760 13286440 10496 5679942
08/12/2015 15:32:46 19952 13286440 2304 5703464
08/12/2015 15:22:46 19952 13286440 2304 5701016
08/12/2015 15:12:46 19952 13273512 10496 5716642
BEGIN_TIME ACTIVEBLKS UNEXPIREDBLKS EXPIREDBLKS TUNED_UNDORETENTION
------------------------------ ---------- ------------- ----------- -------------------
08/12/2015 15:02:46 3568 13288744 11392 5709225
08/12/2015 14:52:46 11760 13280296 10368 5717024
08/12/2015 14:42:46 11760 13280168 10496 5736215
08/12/2015 14:32:46 28144 13271848 2176 5648964
08/12/2015 14:22:46 19952 13263656 2176 5819434
08/12/2015 14:12:46 11760 13271592 2176 5866860
08/12/2015 14:02:46 11760 13271592 2176 5873904
08/12/2015 13:52:46 28144 13254056 2176 5886917
08/12/2015 13:42:46 11760 13270184 2176 5905094
08/12/2015 13:32:46 20976 13260840 2176 5909713
08/12/2015 13:22:46 11760 13270056 2176 5917541
BEGIN_TIME ACTIVEBLKS UNEXPIREDBLKS EXPIREDBLKS TUNED_UNDORETENTION
------------------------------ ---------- ------------- ----------- -------------------
08/12/2015 13:12:46 11760 13269928 2176 5909180
08/12/2015 13:02:46 11760 13269928 2176 5910021
08/12/2015 12:52:46 11760 13261608 2176 5910784
08/12/2015 12:42:46 12784 13252392 2176 5911076
08/12/2015 12:32:46 12656 13251240 2304 5896012
08/12/2015 12:22:46 3568 13252136 10368 5927543
08/12/2015 12:12:46 11760 13243944 10368 5942166
08/12/2015 12:02:46 11760 13243944 10368 5932299
08/12/2015 11:52:46 11760 13243944 10368 5932288
08/12/2015 11:42:46 19952 13243944 2176 5932406
08/12/2015 11:32:46 19952 13235624 2176 5928678
BEGIN_TIME ACTIVEBLKS UNEXPIREDBLKS EXPIREDBLKS TUNED_UNDORETENTION
------------------------------ ---------- ------------- ----------- -------------------
08/12/2015 11:22:46 11760 13243816 2176 5930833
08/12/2015 11:12:46 11760 13243688 2176 5921460
08/12/2015 11:02:46 11760 13243432 2176 5944782
08/12/2015 10:52:46 3568 13251624 2176 5953871
08/12/2015 10:42:46 3568 13251624 2176 5978579
08/12/2015 10:32:46 3568 13251624 2176 5978140
08/12/2015 10:22:46 3568 13242280 2176 5921444
08/12/2015 10:12:46 4592 13241256 2176 6034858
08/12/2015 10:02:46 4592 13241256 2176 6047408
08/12/2015 09:52:46 4592 13240232 2176 6049517
08/12/2015 09:42:46 4592 13240104 2176 6040410
BEGIN_TIME ACTIVEBLKS UNEXPIREDBLKS EXPIREDBLKS TUNED_UNDORETENTION
------------------------------ ---------- ------------- ----------- -------------------
08/12/2015 09:32:46 12784 13231912 2176 6054216
08/12/2015 09:22:46 20976 13223720 2176 6050894
08/12/2015 09:12:46 4592 13231912 2176 6072825
08/12/2015 09:02:46 4592 13231528 2304 6089504
08/12/2015 08:52:46 20976 13215016 2304 6108557
08/12/2015 08:42:46 5232 13230760 2304 6126483
08/12/2015 08:32:46 4592 13231400 2304 6133989
08/12/2015 08:22:46 4592 13231400 2304 6135342
08/12/2015 08:12:46 4592 13231400 2304 6126924
08/12/2015 08:02:46 4592 13231400 2304 6113724
08/12/2015 07:52:46 4592 13215016 10496 6025237
BEGIN_TIME ACTIVEBLKS UNEXPIREDBLKS EXPIREDBLKS TUNED_UNDORETENTION
------------------------------ ---------- ------------- ----------- -------------------
08/12/2015 07:42:46 4592 13215016 10496 6079561
08/12/2015 07:32:46 4592 13215016 10496 6067825
08/12/2015 07:22:46 4592 13296808 10496 6052985
08/12/2015 07:12:46 4592 13296808 10496 6033145
08/12/2015 07:02:46 4592 13296808 10496 6019906
08/12/2015 06:52:46 4592 13296808 10496 6017717
08/12/2015 06:42:46 4592 13295656 10496 6005087
08/12/2015 06:32:46 4592 13295656 10496 6010367
08/12/2015 06:22:46 4592 13295656 10496 6001669
08/12/2015 06:12:46 4592 13295656 10496 5980524
08/12/2015 06:02:46 4592 13295656 10496 5979327
BEGIN_TIME ACTIVEBLKS UNEXPIREDBLKS EXPIREDBLKS TUNED_UNDORETENTION
------------------------------ ---------- ------------- ----------- -------------------
08/12/2015 05:52:46 4592 13295656 10496 5948603
08/12/2015 05:42:46 4592 13295656 10496 5936140
08/12/2015 05:32:46 4592 13295656 10496 5916546
08/12/2015 05:22:46 4592 13295656 10496 5904206
08/12/2015 05:12:46 4592 13295656 10496 5888506
08/12/2015 05:02:46 4592 13295656 10496 5879717
08/12/2015 04:52:46 4592 13295656 10496 5874371
08/12/2015 04:42:46 4592 13295656 10496 5863919
08/12/2015 04:32:46 4592 13295656 10496 5854172
08/12/2015 04:22:46 4592 13295656 10496 5841001
08/12/2015 04:12:46 4592 13295656 10496 5829960
查看v$undostat.tunrd_undoretention发现，TUNED_UNDORETENTION该值被自动调整到平均97000分钟，也就是说undo表空间中的数据要保留60多天才会过期，正是因为这么长的数据未过期时间，且表空间充足，导致了undo表空间的空间释放程度。
按照Oracle文档介绍，可按以下几种方法解决。
解决方法1：设置_undo_autotune=FALSE,将会关闭自动调整undo_retention功能，那么undo的retention时间完全依赖初始化参数undo_retention的值，默认值为900s。
解决方法2：可设置_sum_debug_mode=33554432隐藏参数。设置后，Oracle的undo_retention自动调整功能依然被打开，但是计算tuned_undoretention是根据MAXQUENRYLEN secs +300来计算，而不是根据undo表空间大小的百分比来计算，可避免TUNED_UNDORETENTION值变很大。
总结：
Oracle 10g中引入了一个新的自动调整undo_retention的特性，在Oracle Database 10g中当自动undo管理被启用，总是存在一个当前的undo retention，Oracle Database尝试至少保留旧的undo信息到该时间。
数据库收集使用情况统计信息，基于这些统计信息和UNDO表空间大小来调整undo retention的时间。目前统计undo表空间使用率的时候，是把expired和unexpired还有active三种状态的extent的总和，
作为undo表空间正在使用的大小，这种会导致undo表空间使用率虚高的情况。而实际上expired和unexpired状态的extent并不能作为当前undo表空间正在使用的部分，因为这部分extent是可以回收重用的。
从Oracle 10g开始，为减少出现ORA-01555错误出现的概率，Oracle会忽略undo_retention参数设置的阀值，而是根据undo表空间的大小和使用率来自动调整undo信息的保留时间。
这就可能会造成undo表空间可能会由于回滚段保留时间自动调整而导致大量的undo extent处于非过期状态，
这会使得undo表空间的使用率处于100%状态。
