undo表空间使用率过高分析解决

近期，在客户的某套库中，发现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%状态。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30430420/viewspace-1792355/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30430420/viewspace-1792355/