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

对于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;

 

<!-- /* Font Definitions */ @font-face {font-family:宋体; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-alt:SimSun; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 135135232 16 0 262145 0;} @font-face {font-family:"/@宋体"; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 135135232 16 0 262145 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; mso-pagination:none; font-size:10.5pt; mso-bidi-font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:宋体; mso-font-kerning:1.0pt;} /* Page Definitions */ @page {mso-page-border-surround-header:no; mso-page-border-surround-footer:no;} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} -->

关于 10g Undo_retention 及 Automatic Undo Retention Tuning

 

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

)^6i^ { ^A;~/G$X#Q0 Oracle 10g 有自动 Automatic Undo Retention Tuning 这个特性．设置的 undo_retention 参数只是一个指导值 , Oracle 会自动调整 Undo ( 会跨过 undo_retention 设定的时间 ) 来保证不会出现 Ora-1555 错误 . 通过查询　 V$UNDOSTAT 的 tuned_undoretention 字段可以得到 Oracle 根据事务量 ( 如果是文件不可扩展，则会考虑剩余空间 ) 采样后的自作主张的 retenton 时间 . 这样对于一个 事务量分布不均匀的数据库来说 , 就会引发潜在的问题 -- 在批处理的时候可能 Undo 会用光 . 而且这个状态将一直持续 , 不会释放 .
}/A&s.~ i‑d4/(W0 ITPUB 个人空间 )R4X,B [1] S+M!CDr B g8k/Z
10g 中 , 如果数据文件的属性是 autoextensible , 则 undo_retention 参数必须设置 . 如果 undo 表空间的文件属性是不可扩展的 , 则　 undo_retention 将不会起到什么作用 ( 修正的办法是把该 Undo 表空间属性设置为 GUARANTEE ). ITPUB 个人空间 [

---

do.tD} G^

:k*/9^?O'I.i2E0 通过如下命令 (undotbs01 是 undo 表空间名字 ) ： ITPUB 个人空间 [.g z-z;~%qR&Z

&[&N3[!Cx? }0 ALTER TABLESPACE undotbs01 RETENTION GUARANTEE; 然后可以查询 DBA_tablespaces 的 retention 列 . undo 表空间的属性和普通表空间的属性是不一样的．

 

 

 

Oracle Database 10g automatically tunes undo retention by collecting database use

statistics and estimating undo capacity needs for the successful completion of the

queries. You can set a low threshold value for the UNDO_RETENTION parameter so

that the system retains the undo for at least the time specified in the parameter,

provided that the current undo tablespace has enough space. Under space

constraint conditions, the system may retain undo for a shorter duration than that

specified by the low threshold value in order to allow DML operations to succeed.

 

In order to guarantee the success of queries even at the price of compromising the

success of DML operations, you can enable retention guarantee. The RETENTION

GUARANTEE clause of the CREATE UNDO TABLESPACE and CREATE DATABASE

statements ensures that undo information is not overwritten. This option must be

used with caution, because it can cause DML operations to fail if the undo

tablespace is not big enough. However, with proper settings, long-running queries

can complete without risk of receiving the "snapshot too old" message, and you can

guarantee a time window in which the execution of Flashback features will succeed