RE: SQL tuning help

From: Stephen Lee <slee_at_dollar.com>
Date: Tue, 26 Nov 2002 15:05:30 -0800
Message-ID: <F001.0050CDDF.20021126150530@fatcity.com>

 

> 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: slee_at_dollar.com

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: ListGuru_at_fatcity.com (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).

Received on Tue Nov 26 2002 - 17:05:30 CST