enq: SQ waits, processes & load
- From: jason arneil <jason.arneil@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 4 Mar 2011 11:55:19 +0000
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » enq: SQ waits, processes & load - jason arneil
