RE: Efficient way to detect lock contention/blocking sessions

From: John Kanagaraj <john.kanagaraj_at_hds.com>
Date: Thu, 6 Apr 2006 17:07:40 -0700
Message-ID: <BEE6A332AA61424EAE305CF89D6F75C84BB282@USSCCEVS101.corp.hds.com>

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

John

---

From: jayaraj rengarajan [mailto:jayaraj.rengarajan_at_gmail.com] Sent: Thursday, April 06, 2006 5:06 PM
To: John Kanagaraj
Cc: Oracle-L_at_freelists.org
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_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

Received on Thu Apr 06 2006 - 19:07:40 CDT