-- #############################################################################################
--
-- %Purpose: Show who is blocking whom
--
-- The waiters.sql script is the favourite when users are complaining of spasmodic application
-- performance. Sometimes it takes 3 seconds, ut other times it takes 20 minutes, they tell.
-- This type of behaviour is indicative of application locking conflicts. Running this script
-- as SYS allows us to pinpoint who is blocking whom
--
-- Run as SYS
--
-- #############################################################################################
--
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   v_$process P1,   v_$process P2,
       v_$session S1,   v_$session S2,
       dba_locks w,     dba_locks 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 (+)
/
-- #############################################################################################
--
-- %Purpose: Drill down to see what the locker is doing
--
-- The what.sql is used after the waiters.sql. Once you have determined who is blocking whom
-- via the waiters.sql script, we are now able to drill down and query what the offending user
-- is actually doing. By identifying who is causing the locking and the actual SQL statement
-- they are executing, we can define the application module that needs to be reviewed.
--
-- Run as SYS and enter SID from Blocker from above script
--
-- #############################################################################################
--
SELECT /*+ ORDERED */
       s.sid, s.username, s.osuser,
       nvl(s.machine, '?') machine,
       nvl(s.program, '?') program,
       s.process F_Ground, p.spid B_Ground,
       X.sql_text
FROM   v_$session S,
       v_$process P,
       v_$sqlarea X
WHERE  s.sid         like nvl('&SID','%')
AND    s.paddr          = p.addr
AND    s.type          != 'BACKGROUND'
AND    s.sql_address    = x.address
AND    s.sql_hash_value = x.hash_value
ORDER BY S.sid
/