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 =====