RE: Efficient way to detect lock contention/blocking sessions

From: "John Kanagaraj" <john.kanagaraj@xxxxxxx>
To: "jayaraj rengarajan" <jayaraj.rengarajan@xxxxxxxxx>
Date: Thu, 6 Apr 2006 17:07:40 -0700

Correct - this will change if you are on 10g though. I haven't updated
the article :(

John

________________________________

From: jayaraj rengarajan [mailto:jayaraj.rengarajan@xxxxxxxxx] 
Sent: Thursday, April 06, 2006 5:06 PM
To: John Kanagaraj
Cc: Oracle-L@xxxxxxxxxxxxx
Subject: 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@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

RE: Efficient way to detect lock contention/blocking sessions

Other related posts: