首页 > Linux操作系统 > Linux操作系统 > Performance Slowdown During Heavy Undo Segment Onlining

Performance Slowdown During Heavy Undo Segment Onlining

原创 Linux操作系统 作者:NinGoo 时间:2019-07-08 10:39:06 0 删除 编辑

文档 ID: 注释:301432.1 类型: PROBLEM
上次修订日期: 12-JUN-2005 状态: MODERATED

Applies to:
Oracle Server - Enterprise Edition - Version:
This problem can occur on any platform.

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

Introduction of Automatic Undo Management may have been a recent change in some cases.
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 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.

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 2"

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.)

Bug 3577781 - Undo Onlining Causes Excessive Waits


来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录


  • 博文量
  • 访问量