Re: Efficient way to detect lock contention/blocking sessions

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_at_hds.com> 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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of jayaraj rengarajan
> Sent: Thursday, March 23, 2006 8:48 AM
> To: Oracle-L_at_freelists.org
> 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
>
>

--
http://www.freelists.org/webpage/oracle-l