| DBA_BLOCKERS - Too much time to detect blocking locks. [message #64176] |
Mon, 27 December 2004 23:27  |
patrick
Messages: 83
Registered: December 2000
|
Member |
|
|
Hi,
Could someone explain me why when querying this view (dba_blockers) for blocking locks it takes so much time!!??
I'm using Oracle 8i, and I would like to add in my monitoring script some checks that quickly detects blocking locks!
I would like to quickly determine who is blocking and who is waiting for this blocker, so I can, eventually, alter session and kill the blocker (and not the blockee(s)).
Thank you for your help.
Best regards,
Patrick.
|
|
|
|
| Re: DBA_BLOCKERS - Too much time to detect blocking locks. [message #64196 is a reply to message #64176] |
Sat, 01 January 2005 19:23  |
Jeremiah Wilton
Messages: 1
Registered: January 2005
|
Junior Member |
|
|
Patrick,
Only you can say why DBA_BLOCKERS is slow on your system. What are the top wait events for your session during a query on this view?
DBA_BLOCKERS is a view that joins several memory structures in the SGA. Depending on your version, this approach may not be the most efficient way.
The quickest way to find out if there are any blocking locks is to look for any rows in v$lock where block = 1.
If you want to know who is blocking whom, find the session in v$session_wait that is waiting on enqueue, and whose P1 and P2 are equal to id1 and id2 from the blocker's row in v$lock.
If you want to know what object they are waiting for, look at the row_wait_% column sin v$session for the waiting session.
Hope this helps,
--
Jeremiah Wilton
ORA-600 Consulting
Emergencies - Seminars - Hiring
http://www.ora-600.net
|
|
|
|
| Re: DBA_BLOCKERS - Too much time to detect blocking locks. [message #64204 is a reply to message #64196] |
Sun, 02 January 2005 22:03 |
patrick
Messages: 83
Registered: December 2000
|
Member |
|
|
Hi Jeremiah,
Thank you very much for your answer!
I have also tried a solution that seems to work quite well too and I receive a respons in a very short amount of time (1-2 seconds).
I use the V$LOCKED_OBJECT using xidusn column to check blocking locks and I join this view with V$SESSION (session_id - sid) to get more info about the sid... What do you think about it?
It's a good idea to retreive also which row is the blocking lock issue by retreiving also row_wait_row column form V$SESSION, thank you for this good tips too!!
I wish you a happy new year!
Best regards,
Patrick.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
