RE: Efficient way to detect lock contention/blocking sessions

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