Current waiting events
                                                 Last update (2012-09-07 09:02:55)
                                                                                                                   Date added (2008-02-26 14:24:22)

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

Tip: The query for Oracle10g and Oracle11g, filters only the non idle wait events! If you are using Oracle 9i, then the column wait_class = 'Idle' does not exists.
IO wait events
------------------------------
 1.db file sequential read, 
 2.db file scattered read 
 3.direct path read 
 4.direct path write

Memory wait events
-------------------------------
 a.Buffer Cache wait events
 5.latch free
 6.buffer busy waits 
 7.cache buffers chains
 8.free buffer waits

 b.Library Cache wait events
 9.library cache lock
 10.library cache pin
 11.library load lock 

Transaction wait events
----------------------------
 12.enqueues

RAC wait events
---------------------------
 13.global cache open x 
 14.global cache cr request 

 15.queue messages

Parallel processing wait events
----------------------------
 16.PX Deq wait events
Other links
Average wait times for all events
Reviews
Categories
Filters
Search