1 Reply Latest reply: Jan 27, 2013 12:02 PM by sb92075 RSS

    Monitoring Blocking locks (dba_blockers vs v$lock)


      This question relates to monitoring blocking locks on a database

      Origionally I have been monitoring bocking locks with every 5 mins using the following query:
      "select * from dba_blockers"

      I have recently implemented monitoring via grid control this is running an out of the box metric every 5 mins, the sql behind it is as follows:

      "SELECT blocking_sid, num_blocked
      FROM ( SELECT blocking_sid, SUM(num_blocked) num_blocked
      FROM ( SELECT l.id1, l.id2,
      MAX(DECODE(l.block, 1,l.sid,
      2,l.sid, 0 )) blocking_sid,
      SUM(DECODE(l.request, 0, 0, 1 )) num_blocked
      FROM v$lock l
      WHERE ( l.block!= 0 OR l.request > 0 ) AND
      GROUP BY l.id1, l.id2)
      GROUP BY blocking_sid
      ORDER BY num_blocked DESC)
      WHERE num_blocked != 0 "

      Now.. At one point today the alert using "select * from dba_blockers" fired where as the out of the box metric from gird control did not fire.... alert duration was around 5 - 10 mins

      At first i simply assumed that this could have been a brief lock and due to both 5 min intervals being out of sync, the lock had shown and cleared before the grid control interval run.

      now im a little more curious.

      Is there any significan difference in what these 2 different SQL's will alert on (different types of blocking locks / sessions?) , I was under the impression that DBA_BLOCKERS was simply querying a number of joined views, and Oracle had decided to use V$lock for their out of the box metric as it was more efficient.

      Any comments welcome