Re: How do enqueue waits affect overall db performance?

"Anna C. Dent" <anacedent_at_hotmail.com> wrote in message news:<L4Bmc.75435$Jy3.35150_at_fed1read03>...
> Dave wrote:
> > Hey all,
> >
> > I have a DB with a large number of enqueue waits, taking up about 77%
> > of the wait time in a statspack snapshot of 25 minutes. After some
> > research its basically because of some distributed transactions over
> > db_links. All the blocking locks that I can see are DX.
> >
> > Event Waits Time (s) Ela Time
> > ------------- -------- ------- --------
> > enqueue 1,372 4,115 77.71%
> >
> >
> > How will this affect over all db performance? ie, I have another app
> > in the same database.. Will this affect them? If so, how can I
> > measure it..?
>
> It depends.
>
> It depends upon what is locked and if anyone else wants it too.
>
> Here is what can happen under "worse case" conditions.
> Session 1 has an exclusive lock and literally goes out to lunch
> without doing a commit. Session 2 grabs a lock or two, then wants
> the object locked by session #1 but there is no available ITL slot
> so it goes into enqueue wait (where NO deadlock detection occurs).
> Session #3 grabs a few locks but then wants a lock held by #2.
> etc, etc, etc.
> After about 30 minutes the whole DB is tied into knots.
> The ONLY recourse was to bounce the DB.

ONLY recourse?
kill them.
kill them all.
find the root session that is blocking and kill it. find the next root blocker and kill it.
continue until the blockers are dead.

no instance restart required.

I call it how I spent 3 hours on Monday - a missing index in a foreign key that went out in an app update on Saturday.

if you bounce the instance, you have no chance at determining what is causing the problem, one that is certain to return at some point.

take the hit and get the diagnostic info out of the sick instance. then kill the sessions.

just my opinion.

Pd Received on Fri May 07 2004 - 01:23:54 CDT