#!/usr/bin/ksh

#-------------------------------------------------------------------------------
#--
#-- Script:	waiters.sql
#-- Purpose:	to count the waiters for each event type
#-- For:		8.0 and higher
#--
#-- Copyright:	(c) 2000 Ixora Pty Ltd
#-- Author:	Steve Adams
#--
#-------------------------------------------------------------------------------

HOST=`hostname`
HOST=`echo $HOST | awk '{ printf ("%-+15.15s",$1)  }'`
SBINS=$SBIN/scripts
S_USER=SYS
. $SBINS/passwd.env
. ${GET_PASSWD} $S_USER $ORACLE_SID
if [  "x-$CONNECT_STRING" = "x-" ];then
      echo "could no get a the password of $S_USER"
      exit 0
fi

sqlplus -s "$CONNECT_STRING" <<EOF

ttitle skip 2 'MACHINE $HOST - ORACLE_SID : $ORACLE_SID '  right 'Page:' format 999 sql.pno skip 2
column nline newline
set pagesize 66
set linesize 80
set termout on
set embedded on
set verify off
set heading off pause off
 
select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline ,
       'Show session waits events : Short grouped version' nline
from sys.dual
/   
set head on pause off feed off
set linesize 110


column event format a29
column t0 format 999
column t1 format 999
column t2 format 999
column t3 format 999
column t4 format 999
column t5 format 999
column t6 format 999
column t7 format 999
column t8 format 999
column t9 format 999

select /*+ ordered */
  substr(n.name, 1, 29)  event,
  t0,
  t1,
  t2,
  t3,
  t4,
  t5,
  t6,
  t7,
  t8,
  t9
from
  v\$event_name  n,
  (select event e0, count(*)  t0 from v\$session_wait group by event),
  (select event e1, count(*)  t1 from v\$session_wait group by event),
  (select event e2, count(*)  t2 from v\$session_wait group by event),
  (select event e3, count(*)  t3 from v\$session_wait group by event),
  (select event e4, count(*)  t4 from v\$session_wait group by event),
  (select event e5, count(*)  t5 from v\$session_wait group by event),
  (select event e6, count(*)  t6 from v\$session_wait group by event),
  (select event e7, count(*)  t7 from v\$session_wait group by event),
  (select event e8, count(*)  t8 from v\$session_wait group by event),
  (select event e9, count(*)  t9 from v\$session_wait group by event)
where
  n.name != 'Null event' and
  n.name != 'rdbms ipc message' and
  n.name != 'pipe get' and
  n.name != 'virtual circuit status' and
  n.name not like '%timer%' and
  n.name not like 'SQL*Net message from %' and
  e0 (+) = n.name and
  e1 (+) = n.name and
  e2 (+) = n.name and
  e3 (+) = n.name and
  e4 (+) = n.name and
  e5 (+) = n.name and
  e6 (+) = n.name and
  e7 (+) = n.name and
  e8 (+) = n.name and
  e9 (+) = n.name and
  nvl(t0, 0) + nvl(t1, 0) + nvl(t2, 0) + nvl(t3, 0) + nvl(t4, 0) +
  nvl(t5, 0) + nvl(t6, 0) + nvl(t7, 0) + nvl(t8, 0) + nvl(t9, 0) > 0
order by
  nvl(t0, 0) + nvl(t1, 0) + nvl(t2, 0) + nvl(t3, 0) + nvl(t4, 0) +
  nvl(t5, 0) + nvl(t6, 0) + nvl(t7, 0) + nvl(t8, 0) + nvl(t9, 0)
/
prompt
prompt
EOF