Oracle数据库日志不断报undo segment onlined的建议解决方法

教程发布:风哥 教程分类:ITPUX技术网 更新日期:2022-02-12 浏览学习:37

最近客户有套数据库9.2.0.5 rac+aix,数据库日志不断报undo segment onlined,而且性能有时下降,
根据metalink说法,建议采用以下两种方法处理:

1、切换unto tablespace ,也就是新建一个临时undo tablespace,切换一下,待处理完毕后切换回来。
2、可以用event="10511 trace name context forever, level 1"来disable SMON去offline idle rollback segment的操作。

以下是metalink详细解释
Performance Slowdown During Heavy Undo Segment Onlining [ID 301432.1]
--------------------------------------------------------------------------------

修改时间 02-OCT-2009 类型 PROBLEM 状态 PUBLISHED
In this Document
Symptoms
Changes
Cause
Solution
References

--------------------------------------------------------------------------------

Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.5
This problem can occur on any platform.
Symptoms
Severe database performance slowdown.
Statspack report shows Wait Event "enqueue" with type "US" consuming a significant percentage of database time.
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
enqueue 39,641 70,172 87.61
Enqueue activity for DB: CSP Instance: CSP Snaps: 2716 -2723
-> Enqueue stats gathered prior to 9i should not be compared with 9i data
-> ordered by Wait Time desc, Waits desc
Avg Wt Wait
Eq Requests Succ Gets Failed Gets Waits Time (ms) Time (s)
-- ------------ ------------ ----------- ----------- ------------- ------------
US 2,688 2,620 0 48 1,240,534.88 59,546
Alert.log shows lots of Undo Segments being onlined during this time interval, e.g.
Fri Mar 11 08:39:46 2005
Undo Segment 11 Onlined
Fri Mar 11 08:41:20 2005
Undo Segment 12 Onlined
Fri Mar 11 08:41:20 2005
Undo Segment 13 Onlined
Fri Mar 11 08:41:31 2005
Undo Segment 14 Onlined
Fri Mar 11 08:43:44 2005
Undo Segment 15 Onlined

Changes
Introduction of Automatic Undo Management may have been a recent change in some cases.
Cause
Large numbers of Undo Segment onlines are being performed.
Undo Segment online and offline operations require Serialization on US enqueue.
In transactional databases where incoming workload varies with time it is common for idle Undo Segments to be offlined (or even dropped before 9.2.0.4) by SMON after a while, only to be onlined once again during the next "wave" of incoming transactions.
This repetitive offlining and onlining of Undo Segments causes performance problems due to the US enqueue operations needed.
Solution
Prevent SMON from offlining idle Undo Segments using event 10511.
Set the following parameter in init.ora/spfile
event="10511 trace name context forever, level 1"
Then restart the database.
After the first onlinining of an Undo Segment, it will never be offlined and re-onlined.
So we will only get the "US" serialization once (on the first online after a db restart.)

References
BUG:3577781 - UNDO ONLINING CAUSES EXCESSIVE WAITS

本文标签:
网站声明:本文由风哥整理发布,转载请保留此段声明,本站所有内容将不对其使用后果做任何承诺,请读者谨慎使用!
【上一篇】
【下一篇】