Query To Monitor Database Session Waits

Download(session_wait.sql)

REM +======================================================================+
REM                    
REM File Name: session_wait.sql
REM 
REM Description:
REM   Query To Monitor Database Session Waits
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @session_wait.sql 
REM   
REM +======================================================================+

clear columns
set lines 120
set pages 1000
col event for a30
select inst_id,sid, event, p1, p2, p3, p1raw from gv$session_wait
where event not like '%messag%' and event not in ('pipe get','PL/SQL lock timer','Streams AQ: qmn slave idle wait','Streams AQ: waiting for time management or cleanup tasks','Streams AQ: qmn coordinator idle wait')
and (wait_time=0 or state='WAITING')
order by 2;
/