POOR PERFORMANCE DUE TO HUGE WAITS ON US ENQUEUE (Doc ID 463136.1)

Last updated on FEBRUARY 08, 2008

Applies to:

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

Symptoms

Database Performance is slow as large no.of sessions are waiting for US-enqueue.

It seems that the sessions which are waiting for US-enqueue are trying to make the undo segment online.

But from dba_rollback_segs, it is seen that the undo segment is offline.

Statspack report shows the significant time is spent waited for US-enqueue.

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
enqueue 229,742 71,598 90.35
db file sequential read 325,089 2,576 3.25
CPU time 1,264 1.60
buffer busy global cache 33,351 1,101 1.39
global cache null to x 42,701 929 1.17
-------------------------------------------------------------


Enqueue activity for DB: PIDV Instance: pidv2 Snaps: 102365 - 102366
-> 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 80,287 78,868 0 57,019 1,411.62 80,489  <==More time is spent waiting for US-enqueue
TX 204,772 204,775 0 11,872 157.83 1,874
XR 672 672 0 672 1.61 1
CF 4,703 4,702 1 687 1.51 1
TA 12 12 0 7 1.29 0
TD 4 4 0 4 1.25 0
FB 4 4 0 3 1.67 0
DR 1 1 0 1 1.00 0
TM 230,058 230,053 0 1 1.00 0

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