#!/usr/bin/ksh
#---------------------------------------------------------------------------------
#-- Copyright:	(c) 2001 Ixora Pty Ltd
#-- Author:	Steve Adams, adapted to smenu by B. Polarski
#---------------------------------------------------------------------------------
#
function help 
{
  cat <<EOF

  spx   -a -f 

EOF
exit
}
SBINS=$SBIN/scripts
HOST=`hostname`
HOST=`echo $HOST | awk '{ printf ("%-+15.15s",$1)  }'`
METHOD=DEFAULT

while [ -n "$1" ]
do
  case "$1" in
   -a ) ORDER="ORDER by address" ;;
   -s ) METHOD=SYSTAT ;;
   -d ) METHOD=DFO ;;
   -h ) help ;;
  esac
  shift
done
TMP=$SBIN/tmp
cd $TMP

. $SBIN/scripts/passwd.env
. ${GET_PASSWD} 
if [  "x-$CONNECT_STRING" = "x-" ];then
   echo "could no get a the password of $S_USER"
   exit 0
fi


if [ "$METHOD" = "DFO" ];then
SQL="
set lines 190
break on dfo_number on tq_id
SELECT dfo_number, tq_id, server_type, process, num_rows, bytes
FROM v\$pq_tqstat
ORDER BY dfo_number DESC, tq_id, server_type DESC , process;
"
elif [ "$METHOD" = "SYSTAT" ];then
SQL="col statistic format a50
SELECT * FROM v\$pq_sysstat  ;
"
else
SQL=" select x.server_name
	     , x.status as x_status
	     , x.pid as x_pid
	     , x.sid as x_sid
	     , w2.sid as p_sid
	     , v.osuser
	     , v.schemaname
	     , w1.event as child_wait
	     , w2.event as parent_wait
	from  v\$px_process x
	    , v\$lock l
	    , v\$session v
	    , v\$session_wait w1
	    , v\$session_wait w2
	where x.sid <> l.sid(+)
	and   to_number (substr(x.server_name,3)) = l.id2(+)
	and   x.sid = w1.sid(+)
	and   l.sid = w2.sid(+)
	and   x.sid = v.sid(+)
	and   nvl(l.type,'PS') = 'PS'
	order by 4,1,2;
"
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 linesize 80 termout on pause off embedded on verify off heading off

select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline ,
       'Report PQ slaves '
from sys.dual
/

set linesize 124 pagesize 66
set heading on
column name        format a24 heading "Latch type"
column child_wait  format a30 heading "child wait"
column event       format a24 heading "Event name"
column waits_holding_latch   format 99999999 heading "Wait     | holding latch"
column sleeps  format 99999999 heading "Number|Sleeps"
column sw      format 999999 heading "Seconds| Waiting"
column sid     format 9999 heading "Sid"

column child_wait  format a30
	column parent_wait format a30
	column server_name format a4  heading 'Name'
	column x_status    format a10 heading 'Status'
	column schemaname  format a10 heading 'Schema'
	column osuser  format a10 heading 'Osuser'
	column x_sid format 9990 heading 'Sid'
	column x_pid format 9990 heading 'Pid'
	column p_sid format 9990 heading 'Parent'

	break on p_sid skip 1
$SQL
EOF