enq: SQ waits, processes & load

From: jason arneil <jason.arneil_at_gmail.com>
Date: Fri, 4 Mar 2011 11:55:19 +0000
Message-Id: <0EB37891-166E-4C71-AF40-EFD60616D467_at_gmail.com>



Hello,

I am trying to gain an understanding of what actually happens when sessions are waiting on an enqueue.

I see the following issue (This is the highest TotalTime wait event) that was causing long application delays:

Event Waits Total Wait Time (s) Avg( wait ms) %DB Time

enq: SQ Contention 2133 29,971 14,051 25.23

So my understanding is that this wait event is sessions attempting to replenish the sequence cache.

Does 2,133 waits for this represent 2,133 distinct attempts at replenishment? Or can the same attempt be accounted multiple times?

During the time period of these waits we saw a very high load average on the system. I'm trying to understand the cause and effect behind this. I.e. did the high load increase the wait time for this enqueue or did the unusually large number of sessions waiting on this ramp up the load.

My understanding is that enqueues are satisfied first come first served, does anyone know if sessions waiting on this enqueue would show up as higher load average on a system? Or would they all be just in a sleep state waiting to be posted so not contributing to higher load I see.

We also see from this time a lot of latching as well:

Event Waits Total Wait (s) Avg( wait ms) %DB Time

latch: shared pool 10,630 13,220 1244 11.13

I'm wondering if this latching is more likely to have ramped up the load, having a knock on effect on the enqueue wait.

What I'm driving at, is that I'm hoping increasing the sequence cache will (under the same conditions) reduce the application delays, but my worry is if these enqueue waits are just a symptom then it may have less effect than I'd hope.

Comments most welcome.

This is all on 11gR2 on linux.

jason.

--

http://jarneil.wordpress.com

--

http://www.freelists.org/webpage/oracle-l Received on Fri Mar 04 2011 - 05:55:19 CST

Original text of this message