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挂起）

   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 undots so it is autoextensible and its maxsize is equal to its current sizeso the undo tablespace now has the autoextend attribute but does notautoend:
SQL> alter database datafile '<datafile_flename>'
autoextend on maxsize <current_size>;

Withthis setting, v$undostat.tuned_undoretention is not calculated based ona percentage of the undo tablespace size, insteadv$undostat.tuned_undoretention is set to the maximum of (maxquerylensecs + 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;

Withthis setting, v$undostat.tuned_undoretention is not calculated based ona percentage of the fixed size undo tablespace, insteadv$undostat.tuned_undoretention is set to the maximum of (maxquerylensecs + 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的。因为只有当undotablespace不够存放undo_retention时间段内的数据的时候，才会发生unexpired undo extentsstealing。再去查看stats$rollstat，发现但是RSSIZE和undo tablespace大小是一样的，这就说明当时undotablespace确实不够用了。

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

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

出问题前一天，数据库做维护被重启过，因为刚起来数据库很空闲，所以v$undostat.tuned_autoretention很大，undotablespace被撑满，虽然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;}-->

关于10gUndo_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 UndoRetention 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!CDrB 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 ALTERTABLESPACE undotbs01 RETENTION GUARANTEE;然后可以查询 DBA_tablespaces 的 retention 列. undo 表空间的属性和普通表空间的属性是不一样的．

 

 

 

Oracle Database 10gautomatically tunes undo retention by collecting database use

statistics and estimating undo capacity needs for the successfulcompletion of the

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

that the system retains the undo for at least the time specifiedin the parameter,

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

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

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

 

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

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

GUARANTEE clause of the CREATE UNDO TABLESPACE and CREATEDATABASE

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

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

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

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

guarantee a time window in which the execution of Flashbackfeatures will succeed