Re: How do enqueue waits affect overall db performance?

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. Received on Thu May 06 2004 - 19:51:54 CDT