Pages

Tuesday, August 27, 2013

Current waiting events Summary

The first and most important script about OWI, is where current sessions waiting
SELECT 
a.SID, b.serial#, b.status, p.spid, b.logon_time, a.event, l.NAME latch_name, a.SECONDS_IN_WAIT SEC, 
b.sql_hash_value, b.osuser, b.username, b.module, b.action, b.program, 
a.p1,a.p1raw,  a.p2, a.p3,   --, b.row_wait_obj#, b.row_wait_file#, b.row_wait_block#, b.row_wait_row#, 
'alter system kill session ' || '''' || a.SID || ', '|| b.serial# || '''' || ' immediate;' kill_session_sql 
FROM v$session_wait a, v$session b, v$latchname l, v$process p 
WHERE a.SID = b.SID 
AND b.username IS NOT NULL 
AND b.TYPE <> 'BACKGROUND' 
AND a.event NOT IN (SELECT NAME FROM v$event_name WHERE wait_class = 'Idle') 
AND (l.latch#(+) = a.p2) 
AND b.paddr = p.addr 
--AND a.sid = 559 
--AND module IN ('JDBC Thin Client') 
--AND p.spid = 13317
--AND b.sql_hash_value = '4119097924'
--AND event like 'library cache pin%' 
--AND b.osuser = 'oracle' 
--AND b.username = 'APPS' 
ORDER BY a.SECONDS_IN_WAIT DESC;
Some useful explanations

sid, serial#, status, logon_time, osuser, username, module, action, program: comes from v$session information
kill_session_sql: is the sql statement for killing the session
spid: is the unix process id, in case you want to $>kill -9 spid
sql_hash_value: is the SQL_ADDRESS to identify the SQL statement that is currently being executed. You'll need it for explain plans, etc.
event, latch_name, sec: wait events and how much time is waiting!
p1, p1raw, p2, p3: arguments to find the object related to waiting

No comments :

Post a Comment