Performance Slowdown During Heavy Undo Segment Onlining
(Doc ID 301432.1)
Last updated on JANUARY 07, 2025
Applies to:
Oracle Database - Enterprise Edition - Version 9.2.0.5 and laterOracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
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
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |