oracle 10g undo表空间使用率居高不下bug-优快云博客

本文探讨了Oracle 10g中UNDO表空间管理的关键参数UNDO_RETENTION及其自动调整特性Automatic Undo Retention Tuning。讨论了如何禁用此特性，并分析了在特定场景下该特性可能导致的问题。

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

Oracle 10g 有自动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 '<datafile_flename>'
autoextend on maxsize <current_size>;

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;

关于 10g Undo_retention 及 Automatic Undo Retention Tuning

Undo_retention Oracle 9i 开始出现新的初始化参数． Oracle 9i 的 Undo 管理特性使得 Ora-1555 错误大大减少 , 但是 Undo_retention 的设置仍然会个别的时候出现问题． ITPUB 个人空间