10201版本上UNDOTS的一个BUG

zlc158 · 发表于 2007-2-2 09:33

请参考一下METALINK 给的解决方案,其实差不多

CAUSE DETERMINATION
===================
Bug 5387030

CAUSE JUSTIFICATION
===================
In 10.2 the calculation of the tuned undo retention period was modified for non-autoextend undo tablespaces: instead of basing the tuned undo re
tention value on the length of the currently longest running query or the undo_r
etention param, the tuned undo retention is based on how many seconds of undo (a
t the most recent rate of undo generation) can be retained in 85% of the fixed s
ize of the undo tablespace. This change in behavior was made to maximize the uti
lization of the undo tablespace space and so provide better protection against q
ueries potentially failing with ORA-1555 "snapshot too old".
This change in behavior should have no negative impact, so the changes were implemented unconditio
nally (see NOTE below) and the change in behavior will apply even to those custo
mers
who (use non-autoextend undo tablespaces in 10.2 and) are not vulnerable to and/or concerned about ORA-1555's.
If this change in behavior is shown to introduce new problems such as a performance degradation or failing SQL statements
, then these would need be investigated as bugs and possibly resolved by bug fi
xes. But so far I have found no evidence of any such bugs caused by these chang
es.

PROPOSED SOLUTION(S)
====================
1.sql>alter system set undo_retention=900;

2.sql>alter system set "_smu_debug_mode" = 33554432;

3.Monitor the space usage of the tablespace now and let us know the results.

OR

Use autoextensible datafiles

PROPOSED SOLUTION JUSTIFICATION(S)
==================================
The Bug 5387030 may be relevant to your issue and closely matches your scenario.There is currently no patch available for the bug and is currentl
y worked upon by the development

The above mentioned bug is only relevant if you are using fixed size undo a tablespace, if you are
ok to use autoextensible datafiles there is no need to set the parmeter

SOLUTION / ACTION PLAN
======================
-- To implement the solution, please execute the following steps::
Use autoextensible datafiles

zlc158 · 发表于 2007-2-2 09:39

最初由 Fenng 发布
[B]SELECT tablespace_name, retention FROM dba_tablespaces where tablespace_name='UNDOTBS';

10g 这个所谓的新特性是有一点意思的 [/B]

已经设置 retention :GUARANTEE ,

看ORACLE 官方文挡说:"the database can overwrite unexpired undo when space is low" ,搞不明白,他说的SPACE IS LOW ,难道达到99% 了才算是LOW

Retention Guarantee
To guarantee the success of long-running queries or Oracle Flashback operations, you can enable retention guarantee. If retention guarantee is enabled, the specified minimum undo retention is guaranteed; the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace. If retention guarantee is not enabled, the database can overwrite unexpired undo when space is low, thus lowering the undo retention for the system. This option is disabled by default.

ZALBB · 发表于 2007-2-2 11:15

最初由 zlc158 发布
[B]

已经设置 retention :GUARANTEE ,

看ORACLE 官方文挡说:"the database can overwrite unexpired undo when space is low" ,搞不明白,他说的SPACE IS LOW ,难道达到99% 了才算是LOW

Retention Guarantee
To guarantee the success of long-running queries or Oracle Flashback operations, you can enable retention guarantee. If retention guarantee is enabled, the specified minimum undo retention is guaranteed; the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace. If retention guarantee is not enabled, the database can overwrite unexpired undo when space is low, thus lowering the undo retention for the system. This option is disabled by default. [/B]

这里的SPACE IS LOW，我理解是 FREE SPACE IS LOW。
以ORACLE牛哄哄的最优的做法，是优先使用空闲的，过期的，再到非过期的。

ORACLE对固定的回滚尺寸上尽量保留老的快照，想法是好的，可
惜出了BUG，每每都是99%的使用率，搞得大家不爽，就像老二，
时时硬顶着，谁受得了，而且还造成ORA-01555，本来是想尽量避
免，却发而造成该错误，丢！

zlc158 · 发表于 2007-2-2 13:24

为了让自己心脏少承受点压力,决定还是不用ORACLE 的优化了, 修改了tablespace autoexent enable ,

biti_rainy · 发表于 2007-2-3 19:29

一直对oracle的这个问题感觉很不爽，它默认计算最长会保留到4天，搞的我们监控表空间容易报警，ft

stacyruirui · 发表于 2007-2-8 01:38

Cause: Oracle change the automated undo space management algorithm in 10.2 to ignore the undo_retention setting when using fixed size undo tablespace datafiles (i.e. not using autoextend). The new algorithm is supposed to retain expired extents as long as possible, regardless of undo_retention setting, while still maintaining a pool of 15% free undo space. However, the implementation is flawed.

Work Around: Set the _undo_autotune paramter to false. May be set using alter system. ex: alter system set "_undo_autotune"=false; Note that setting this parameter causes v$undostat to stop reporting properly - no new rows are created - all stats go into the most recent row.