#!/bin/ksh
#=====================================================================
# File:		oratop.sh
# Type:		korn shell script (calling SQL*Plus)
# Author:	Tim Gorman, Evergreen Database Technologies, Inc.
# Date:		17-JAN-97
#
# Description:
#
#	This script displays information about the "top 10" consumers
#	of CPU.  It provides the identity of the user running it (i.e.
#	Oracle account name, Unix username), information identifying
#	the process (Oracle SID/Serial#, Server Unix PID) which may be
#	used while killing the process, information about what client
#	machine the process is running on, and whether or not the
#	Oracle process has been "orphaned" by the death of the client
#	process or machine.
#
#	NOTE:	For Windows and OS/2 clients running SQL*Net 7.1.4
#		and below, the TCP/IP hostname information is not
#		populated into the V$SESSION.MACHINE column.  As a
#		result, this script may be unable to "ping" PC
#		clients to determine if the Oracle process has
#		been "orphaned", if the PC client is running SQL*Net
#		v7.1.4 or below...
#
# Modifications:
#	17jan97	TGorman	written
#	30jan97	TGorman	adapted from "oratop.sh"
#
#=====================================================================
#
export PATH=$PATH:/etc		# ...to find the "ping" command...
#
#---------------------------------------------------------------------
# ...validate command-line arguments...
#---------------------------------------------------------------------
case $# in
	0)	integer _Arg=10
		;;

	1)	integer _Arg=$1
		if (( ${_Arg} < 1 || ${_Arg} > 20 ))
		then
			echo ""
			echo "Usage: oratopt.sh [ count ]"
			echo "where \"count\" is an integer between 1 and 20"
			echo ""
			exit 1
		fi
		;;

	*)	echo ""
		echo "Usage: oratopt.sh [ count ]"
		echo "where \"count\" is an integer between 1 and 20"
		echo ""
		exit 1
		;;
esac
#
_Tmp=/tmp/oratopt.$$
_ThisHost=`hostname`
integer i=0
#
#-----------------------------------------------------------------------------
# ...using PID and CLOCK TICKS info from "ps", construct SQL clauses to be
# used to find information about the "top 10" CPU processes inside SQL*Plus,
# querying against the V$SESSION and V$PROCESS views...
#-----------------------------------------------------------------------------
ps -eaf | \
	grep " oracle${ORACLE_SID} " | \
	sort -rn +3 | \
	head -${_Arg} | \
	awk '{print $2" "$4}' | \
while read _Spid _Tix _AnyOtherStuff
do
	#
	if [[ "${_WhereClause}" = "" ]]
	then
		_WhereClause="(${_Spid}"
		_OrderByClause="decode(p.spid,${_Spid},${i}"
		_DecodeTix="decode(p.spid,${_Spid},${_Tix}"
		
	else
		_WhereClause="${_WhereClause},${_Spid}"
		_OrderByClause="${_OrderByClause},${_Spid},${i}"
		_DecodeTix="${_DecodeTix},${_Spid},${_Tix}"
	fi
	#
	integer i=${i}+1
	#
done
#
_WhereClause="${_WhereClause})"
_OrderByClause="${_OrderByClause},999)"
_DecodeTix="${_DecodeTix},999)"
#
#-----------------------------------------------------------------------------
# ...use the SQL clauses just constructed to login to SQL*Plus as SYS and
# query the V$SESSION and V$PROCESS views...
#-----------------------------------------------------------------------------
sqlplus -s << __EOF__ > ${_Tmp} 2>&1
$SYS_UNPW
whenever oserror exit failure
whenever sqlerror exit failure
set echo off feedb off timi off pages 0 pause off verify off lines 200
col sid format a10
col username format a8
col osuser format a8
col machine format a8
col spid format 999990
col process format a10
col tix format 9990
select	to_char(s.sid) || ',' || to_char(s.serial#),
	s.username,
	s.osuser,
	nvl(s.machine, 'WinPC') machine,
	s.process,
	p.spid,
	${_DecodeTix} tix
from	v\$session	s,
	v\$process	p
where	s.paddr = p.addr
and	p.spid in ${_WhereClause}
order by ${_OrderByClause}
/
exit success
__EOF__
#
if (( $? != 0 ))	# ...if SQL*Plus failed...
then
	echo "SQL*Plus failed on ${ORACLE_SID} instance; aborting."
	echo "output saved in \"${_Tmp}\"..."
	cat ${_Tmp}
	exit 1
fi
#
#-----------------------------------------------------------------------------
# ...output the information returned from SQL*Plus.  Also, using the
# V$SESSION.MACHINE and V$SESSION.PROCESS information, check to see whether
# the "client-side" process is still running...
#-----------------------------------------------------------------------------
integer _Line=0
integer _OrphanCnt=0
while read _Sid _OraUser _OsUser _Host _Pid _Spid _Tix _AnyOtherStuff
do
	#
	if (( ${_Line} == 0 ))
	then
		echo ""
		uptime
		echo ""
		echo "Oracle   Oracle     Server Client   Client   CPU   "
		echo "Account  SID,Ser#   OS PID OS User  Hostname Ticks Orphan?"
		echo "-------- ---------- ------ -------- -------- ----- -------"
	fi
	#
	integer _Line=${_Line}+1
	#
	if [[ "${_ThisHost}" = "${_Host}" ]]
	then
		#
		if [[ "`ps -eaf | grep ${_Pid} | grep -v grep`" = "" ]]
		then
			_YorN="Yes(pid:${_Pid})"
			integer _OrphanCnt=${_OrphanCnt}+1
		else
			_YorN="No"
		fi
		#
	else
		#
		if [[ "${_OsUser}" = "OraUser" ]]
		then
			if [[ "${_Host}" = "WinPC" ]]
			then
				_YorN="cannot-determine"
			else
				if [ ping ${_Host} -n 1 > /dev/null 2>&1 ]
				then
					_YorN="No"
				else
					_YorN="Yes"
					integer _OrphanCnt=${_OrphanCnt}+1
				fi
			fi
		else
			if [[ "`remsh ${_Host} -n 'ps -eaf|grep '${_Pid}'|grep -v grep'`" = "" ]]
			then
				_YorN="Yes(pid:${_Pid})"
				integer _OrphanCnt=${_OrphanCnt}+1
			else
				_YorN="No"
			fi
		fi
		#
	fi
	#
	echo "${_OraUser} ${_Sid} ${_Spid} ${_OsUser} ${_Host} ${_Tix} ${_YorN}" | \
	awk '{printf("%-9s%-11s%-7s%-9s%-9s%5d   %s\n",$1,$2,$3,$4,$5,$6,$7)}'
	#
done < ${_Tmp}
#
if (( ${_OrphanCnt} > 0 ))
then
	echo ""
	echo "It is possible get a \"false positive\" on \"orphans\";  please make sure..."
fi
#
#-----------------------------------------------------------------------------
#-----------------------------------------------------------------------------
/bin/rm -f ${_Tmp}
#
exit 0