X

Alejandro Vargas' Blog

  • May 16, 2006

Check Waits Script

Alejandro Vargas
Technical Leader, ACS Global Delivery, Infrastructure & BigData

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

Join the discussion

Comments ( 1 )
  • Sanjeev Wednesday, July 26, 2006
    Excellent information.
    Quick question though
    Do you know of any way to identify latch holder session using any of the views? Please suggest.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.