This question relates to monitoring blocking locks on a 220.127.116.11 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,
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.