10G UNDO 表空间居高不下

oracle 10g undo表空间使用率居高不下bug

对于UNDO表空间大小定义需要考虑UNDO_RETNETION参数、产生的UNDO BLOCKS/秒、UNDO BLOCK的大小。
undo_retention：
1、对于UNDOTBS属性为autoextensible,则undo_retenion参数必须设置，UNDO信息将至少保留至undo_retention参数设定的值内，但UNDO表空间将会自动扩展。
2、对于固定UNDO表空间，将会通过表空间的剩余空间来最大限度保留UNDO信息。如果FIXED UNDO表空间没有对保留时间作GUARANTEE（alter tablespace xxx retention guarantee;），则undo_retention参数将不会起作用。（警告：如果设置UNDO表空间为retention guarantee，则未过期的数据不会被复写，如果表空间不够则会导致DML操作失败或者transation挂起）

           Oracle10g有自动Automatic Undo Retention Tuning这个特性。设置的undo_retention参数只是一个指导值,，Oracle会自动调整Undo (会跨过undo_retention设定的时间)来保证不会出现Ora-1555错误.。通过查询V$UNDOSTAT（该视图记录4天以内的UNDO表空间使用情况，超过4天可以查询DBA_HIST_UNDOSTAT视图） 的tuned_undoretention（该字段在10G版本才有，9I是没有的）字段可以得到Oracle根据事务量（如果是文件不可扩展，则会考虑剩余空间）采样后的自动计算出最佳的retenton时间.。这样对于一个事务量分布不均匀的数据库来说,，就会引发潜在的问题--在批处理的时候可能Undo会用光，而且这个状态将一直持续， 不会释放。

如何取消10g的auto UNDO Retention Tuning，有如下三种方法：

from metalink 420525.1：Automatic Tuning of Undo_retention Causes Space Problems

1.) Set the autoextend and maxsize attribute of each datafile in the undo ts so it is autoextensible and its maxsize is equal to its current size so the undo tablespace now has the autoextend attribute but does not autoend:
SQL> alter database datafile ''
autoextend on maxsize ;

With this setting, v$undostat.tuned_undoretention is not calculated based on a percentage of the undo tablespace size, instead v$undostat.tuned_undoretention is set to the maximum of (maxquerylen secs + 300) undo_retention specified in init.ora file.

2.) Set the following hidden parameter in init.ora file:
_smu_debug_mode=33554432
or
SQL> Alter system set "_smu_debug_mode" = 33554432;

With this setting, v$undostat.tuned_undoretention is not calculated based on a percentage of the fixed size undo tablespace, instead v$undostat.tuned_undoretention is set to the maximum of (maxquerylen secs + 300) undo_retention specified in init.ora file.

3.) Set the following hidden parameter in init.ora:
_undo_autotune = false
or
SQL> Alter system set "_undo_autotune" = false; 可动态调整

关于网友的一个案例：

但奇怪的地方在于当时该数据库并不是特别繁忙，并不是在波峰时段。当查看stats$undostat时，一个参数吸引了我的注意力：UNXPSTEALCNT

我们来看看UNXPSTEALCNT的解释：

UNXPBLKREUCNT: Number of unexpired undo blocks reused by transactions

一般来说该参数都应该等于0，但是这个参数在那段时间是大于0的。因为只有当undo tablespace不够存放undo_retention时间段内的数据的时候，才会发生unexpired undo extents stealing。再去查看stats$rollstat，发现但是RSSIZE和undo tablespace大小是一样的，这就说明当时undo tablespace确实不够用了。

那么为什么在系统不是很繁忙的时候会出现undo不够用的情况呢，如果说不够用，那在波峰时段应该问题更加严重才对。

查看stats$undostat.tuned_undoretention参数发现了问题所在。从10.2版本开始，oracle默认采用自动调整undo retention的方法，根据你undo tablespace的大小以及系统的繁忙程度(v$undostat中信息）自动调整undo_retention参数，所以在10g的数据库上你会经常发现undo tablespace永远是满的，因为当你undo tablespace有空闲空间时，系统自动调大undo_retention来保留更多的undo blocks。这一方法有利于时间长的查询，但是对于典型的OLTP系统来说不太适用，因为OLTP上不太可能跑如此长时间的查询，而且在很繁忙的OLTP上还会导致上面所遇到的问题。oracle真是吃力不讨好。

出问题前一天，数据库做维护被重启过，因为刚起来数据库很空闲，所以v$undostat.tuned_autoretention很大，undo tablespace被撑满，虽然tuned_autoretention一直在降，但是还是没有赶上系统warm up的速度，导致数据库出现了问题。

该功能可以通过_undo_autotune参数被disable，disable后v$undostat不在更新。

_undo_autotune : enable auto tuning of undo_retention

该参数可以在线修改：

alter system set “_undo_autotune” = false;

 

http://www.dbafan.com/blog/?p=170

http://www.itpub.net/viewthread.php?tid=717070&extra=page%3D1%26amp%3Bfilter%3Dtips