Performance Slowdown During Heavy Undo Segment Onlining (Doc ID 301432.1)

Last updated on DECEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.5 and later
Information in this document applies to any platform.
This problem can occur on any platform.
***Checked for relevance on 29-DEC-2016***

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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms