Re: Efficient way to detect lock contention/blocking sessions
- From: "jayaraj rengarajan" <jayaraj.rengarajan@xxxxxxxxx>
- To: "John Kanagaraj" <john.kanagaraj@xxxxxxx>
- Date: Thu, 6 Apr 2006 20:05:53 -0400
John:
Thank you for the response. Interesting article, let me go through.
Can we safely assume that 'enqueue' is the key to focus for identifying
blockers ? (from the following snippet describing 'enqueue' in this paper).
Thanks
Jay
'enqueue'
Enqueues are local locks that serialize access to various resources. This
wait event indicates that one or more sessions are waiting for a lock on
some resource that is held by another session (or sessions) in an
incompatible mode to the requested mode. These are generally caused by user
transactions holding 'TX' type locks – the waiting session posts this event
and waits indefinitely. See Note 34566.1 for an in-depth analysis of this
event. I would suggest a value of 1 for this trigger – an alert usually
means that at least one session has locked an object (row or table) and one
or more sessions are waiting for this previous session to COMMIT and thus
release the lock. The V$LOCK, DBA_BLOCKERS and DBA_WAITERS views will
provide adequate information for troubleshooting problems related to
enqueues.
On 4/6/06, John Kanagaraj <john.kanagaraj@xxxxxxx> wrote:
>
> Jay,
>
> My apologies this answer is late - I was catching up with a lot of
> backlog. The easiest method is to summarize V$SESSION_WAIT by eventa nd
> look for 'enqueue' therein (other interesting events as well are
> available). I wrote this up as an article titled "Low Overhead Database
> Monitoring Using Session Waits" that you find on my website at
> "http://www.geocities.com/john_sharmila/links.htm";.
>
> Hth,
> John Kanagaraj <><
> DB Soft Inc
> Phone: 408-970-7002 (W)
>
> Co-Author: Oracle Database 10g Insider Solutions
> http://www.amazon.com/exec/obidos/tg/detail/-/0672327910/
>
> ** The opinions and facts contained in this message are entirely mine
> and do not reflect those of my employer or customers **
>
>
> ________________________________
>
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of jayaraj rengarajan
> Sent: Thursday, March 23, 2006 8:48 AM
> To: Oracle-L@xxxxxxxxxxxxx
> Subject: Efficient way to detect lock contention/blocking sessions
>
>
>
> I would like to implement proactive/notification (possibly to the pager
> and mail) process whenever we see session blockers holding locks. In
> the worst case scenario, lock contention contributes huge latch wait and
> application activities become almost hang status.
>
> I would appreciate if anyone in this list implemented a script which
> runs efficiently as a schedule helping DB administration.
>
> Thanks
>
> Jay
>
>
Other related posts:
