Oracle "Top"???

[supermega]

Is there a way to run a "top" for oracle like you do in unix? My production 7.1 server on Novell has been giving users some problems and I am looking for a script to do a top like search.

Thanks in advance,
SM

[mallard]

Try this:

Break on sid on spid on tot_usg_mins on mins_per_hr

Select e.sid, e.spid,
to_char((e.cpu_usage - b.cpu_usage)/(6000), '9999.99') tot_usg_mins,
to_char( ((e.cpu_usage - b.cpu_usage)/(60100))/( (e.time_stamp - b.time_stamp)*24), '99.99') mins_per_hr,
hash_value,
num_occ
From stat$cpu_end e, stat$cpu_begin b, stat$session_hash c
Where b.sid = e.sid
And b.spid = e.spid
And e.sid = c.sid
And e.spid = c.spid
order by mins_per_hr;

[birdax1]

#!/bin/ksh
#=====================================================================
# File: oratop.sh
# Type: korn shell script (calling SQL*Plus)
#
# 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

[tekion]

mallard,
I've tried your query out, but stat$ doesn't seem to exist. Where are you getting stat$ from?

[mallard]

To create these tables, run the smptsi80.sql script. It can be found in \Oracle_Home\sysman\admin directory.

[ashley75]

Mallard,

the smptsi80.sql script does not exist in the mentioned directory. Could it be some other name ???

Please confirm

[mallard]

Sorry, my error. On my Oracle client install it is under \orawin95\sysman\admin.

[ashley75]

do you know its name in ORacle 8.1.7 ???

Thanks

[ashley75]

Anyone know what scripts I have to run in order to create the below tables ???


stat$cpu_end , stat$cpu_begin , stat$session_hash


Thanks

[mallard]

In order to create these tables you need to run the script smptsi80.sql. I do not find these in a directory path for my Oracle server version 8.1.7. It is found under the directory for the Oracle client install (my version is 8.0.5, but I'm sure it's the same for all. Under the directory for the Oracle client, go to sysman/admin and you will find the script.