> This week it began to hang and I can't figure out why.
The first thing I would check are locks. The statement is trying to update
a table. Try something like the following while the statement appears to be
hung. These are two different ways (and certainly not the only ways) of
checking for lockers and waiters.
------------------------
SELECT substr(s1.username,1,12) "WAITING User",
substr(s1.osuser,1,8) "OS User",
substr(to_char(w.session_id),1,5) "Sid",
P1.spid "PID",
substr(s2.username,1,12) "HOLDING User",
substr(s2.osuser,1,8) "OS User",
substr(to_char(h.session_id),1,5) "Sid",
P2.spid "PID"
FROM sys.v_$process P1, sys.v_$process P2,
sys.v_$session S1, sys.v_$session S2,
sys.dba_lock w, sys.dba_lock h
WHERE h.mode_held = 'None'
AND h.mode_held = 'Null'
AND w.mode_requested != 'None'
AND w.lock_type (+) = h.lock_type
AND w.lock_id1 (+) = h.lock_id1
AND w.lock_id2 (+) = h.lock_id2
AND w.session_id = S1.sid (+)
AND h.session_id = S2.sid (+)
AND S1.paddr = P1.addr (+)
AND S2.paddr = P2.addr (+)
/
-------------------------
set lines 150
set pages 600
col mode_held for a12
col mode_requested for a12
select /*+ all_rows */ a.osuser waiter, nvl(b.osuser,'NOBODY') blocker,
w.lock_type, h.mode_held, w.mode_requested
-- w.lock_id1, w.lock_id2
from dba_locks w, dba_locks h, v$session a, v$session b
where h.blocking_others = 'Blocking' and h.mode_held != 'None'
and h.mode_held != 'Null' and w.mode_requested != 'None' and w.lock_type =
h.lock_type
and w.lock_id1 = h.lock_id1 and w.lock_id2 = h.lock_id2
and w.session_id in (select sid from v$session where last_call_et > 100 and
sid > 10 and osuser is not null)
and w.session_id = a.sid and h.session_id = b.sid;
----------------------------------
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephen Lee
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
