Alejandro Vargas' Blog

This script provides a quick view of the wait events in the database.
Is ready to be run on Unix or Linux
Usage: chkwait <sid>
review the variables section to customize it for your environment.

==== script start on next line ====
#!/usr/bin/ksh
# set -x (open for dubuging)
# lib_cache_fast
# This script check main waits.
# Variables required = 1, Oracle SID
# External identified DBA user required
# Alejandro Vargas

# Check Input Variables
# ---------------------

if [ $# -lt 1 ]; then
   clear
   echo
   echo $0 check waits on a database,
   echo
   echo Please enter Oracle SID to connect to.
   echo
   exit
fi

# Variables
# ---------

v_ld=/tmp/scripts                              
# Local Directory
v_lg=$v_ld/logs                                
# Log Directory
v_x1=`cat
$v_ld/.151762`                       
# no description available
v_ts=`date
+%d%m%Y%H%M`                        
# Time Stamp
v_sd=$1                                        
# Oracle Sid to run on
v_ns=`echo $v_sd | tr '[a-z]' '[A-Z]'`          # Capitalized Oracle Sid

cd $v_ld
export v_ts

# Execute sqlplus
# ---------------

sqlplus -s /@$v_sd <<eof1
set pages 10000 lines 100
col sid for 9999
col event for a30
col name for a45
col p1raw for a15
col p1 for 99999999999
col p2 for 9999999999
col p3 for 9999999999
col seq# for 9999999
col wait_time for 9999

spool $v_lg/$0.$v_ns.log
select sysdate from dual;

prompt * Sessions with wait_time <> 0 (only wait_time=0 is waiting now)

select sid,event,p1,p2,p3,seq#,wait_time
from v$session_wait where event not like 'SQL*Net%'
and wait_time<>0
order by 2
/

prompt * Sessions Waiting Now

select sid,event,p1,p2,p3,seq#,wait_time
from v$session_wait where event not like 'SQL*Net%'
and  wait_time=0
order by 2
/

prompt * Total Main Waits

select count(*),event
from v$session_wait where event not like 'SQL*Net%'
group by event
order by 1
/

prompt * Sessions waiting now

select count(*),event
from v$session_wait where event not like 'SQL*Net%'
and wait_time=0
group by event
order by 1
/

prompt * Latches been waited for

select  *
from    v$latchname
WHERE   latch# in (select unique p2
                 
from  v$session_wait
                 
where event ='latch free')
/

prompt * Latch children

select  addr,
        latch#,
        gets,
        misses,
        sleeps
FROM    v$latch_children
WHERE   sleeps>0
and     latch# in (select unique p2
                 
from  v$session_wait
                 
where event ='latch free')
ORDER   BY sleeps
/

prompt * Latches Waits

SELECT  latch#,
        name,
        gets,
        misses,
        sleeps
FROM    v$latch
WHERE   sleeps>0
ORDER   BY misses, sleeps
/

spool off
exit
eof1

clear
more  $v_lg/$0.$v_ns.log

# eof chkwait
===== script finish on previous line =====

Performance Issues and Hangs

Is the problem due to a slow/hung query or is the entire database affected?  Try running the following SQL statement in SQL*Plus: 

select inst_id, sid, state, event, seconds_in_wait seconds

from gv$session_wait

where seconds_in_wait > 5

and event not in ('rdbms ipc message','smon timer','pmon timer',

'SQL*Net message from client','lock manager wait for remote message',

'ges remote message', 'client message', 'SQL*Net more data from client',

'pipe get', 'Null event', 'PX Idle Wait', 'single-task message',

'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue', 'listen endpoint status',

'slave wait','wakeup time manager');

If the query does not return, you may have a hung database.

If the query returns no rows, you may have database performance issues.

If the query returns rows, you may have hung sessions.

Checkup Reserved Size Usage