RE: Efficient way to detect lock contention/blocking sessions

  • From: "John Kanagaraj" <john.kanagaraj@xxxxxxx>
  • To: <jayaraj.rengarajan@xxxxxxxxx>, <Oracle-L@xxxxxxxxxxxxx>
  • Date: Thu, 6 Apr 2006 13:48:07 -0700

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

John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)
Co-Author: Oracle Database 10g Insider Solutions
** 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.




Other related posts: