How to check Blocking Locks on version >= 10g
We can check lock in v$lock and many Systems write SQL to monitor about Blocking Locks.Example:
A. select INST_ID, SID, TYPE, ID1, ID2, LMODE, REQUEST, CTIME, BLOCK
from gv$lock where (ID1,ID2,TYPE) in
(select ID1,ID2,TYPE from gv$lock where request>0);
B. select * from dba_blockers;
C. select 'Session ID=' ||SID ||'(Inst '||INST_ID||') IS BLOCKED BY Session ID='|| BLOCKING_SESSION || '(Inst '||BLOCKING_INSTANCE||')' from gv$session where BLOCKING_SESSION is not null;
D. ....
What is your SQL to monitor about Blocking Locks?
On 11gR2 v$lock ... wait many with 'direct path read temp', so I use choice C.
A. select INST_ID, SID, TYPE, ID1, ID2, LMODE, REQUEST, CTIME, BLOCK
from gv$lock where (ID1,ID2,TYPE) in
(select ID1,ID2,TYPE from gv$lock where request>0);
B. select * from dba_blockers;
C. select 'Session ID=' ||SID ||'(Inst '||INST_ID||') IS BLOCKED BY Session ID='|| BLOCKING_SESSION || '(Inst '||BLOCKING_INSTANCE||')' from gv$session where BLOCKING_SESSION is not null;
D. ....
What is your SQL to monitor about Blocking Locks?
On 11gR2 v$lock ... wait many with 'direct path read temp', so I use choice C.
0