Identify waiting sessions

Summary
The first and most important script about Oracle Wait Interface (OWI), is where current sessions waiting:

  SELECT A.INST_ID,
         A.SID,
         B.MODULE,
         A.SEQ#,
         A.EVENT,
         A.P1TEXT,
         A.P1,
         A.P1RAW,
         A.P2TEXT,
         A.P2,
         A.P2RAW,
         A.P3TEXT,
         A.P3,
         A.P3RAW,
         A.WAIT_TIME,
         A.SECONDS_IN_WAIT,
         A.STATE,
         B.SERIAL#,
         B.USERNAME,
         B.OSUSER,
         B.PADDR,
         B.LOGON_TIME,
         B.PROCESS,
         B.SQL_HASH_VALUE,
         B.SADDR,
         B.ROW_WAIT_OBJ#,
         B.ROW_WAIT_FILE#,
         B.ROW_WAIT_BLOCK#,
         B.ROW_WAIT_ROW#,
            'alter system kill session '
         || ''''
         || B.SID
         || ', '
         || B.SERIAL#
         || ''''
         || ' immediate;'
            KILL_SESSION
    FROM GV$SESSION_WAIT A, GV$SESSION B
   WHERE     A.INST_ID = B.INST_ID
         AND A.SID = B.SID
         AND B.USERNAME IS NOT NULL
         AND B.TYPE <> 'BACKGROUND'
         AND A.EVENT IN
                ('db file sequential read',
                 'db file scattered read',
                 'latch free',
                 'direct path read',
                 'direct path write',
                 'enqueue',
                 'library cache pin',
                 'library load lock',
                 'buffer busy waits',
                 'latch: cache buffers chains',
                 'SQL*Net message from dblink',
                 'free buffer waits',
                 'cache buffers chains',
                 'library cache lock',
                 'global cache open x',
                 'global cache cr request',
                 'queue messages')
ORDER BY A.SECONDS_IN_WAIT DESC;

Some useful explanations
sid, serial#, status, logon_time, osuser, username, module, action, program: come from gv$session information
kill_session_sql: is the sql statement for killing the session
spid: is the unix process id on the database server, 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, p2, p3: arguments to find the object related to waiting

Tip: The query filters only the most important wait events!

IO wait events
------------------------------
db file sequential read
db file scattered read
direct path read
direct path write

Memory wait events
-------------------------------
Buffer Cache wait events
latch free
buffer busy waits
cache buffers chains
free buffer waits

Library Cache wait events
library cache lock
library cache pin
library load lock

Transaction wait events
----------------------------
enqueues

RAC wait events
---------------------------
global cache open x
global cache cr request
queue messages