Oracle/Unix Top Process Script

Trying to figure out what process is hogging all the CPU on a *ix environment according to top can be a awkward

Here is a quick little script which can help you quickly determine what the top running  process is doing in your Oracle DB

#!/usr/bin/sh

#copyright © Feb 1st 2012 GRAHAM BOYLE
#This code is free to use, so long as the original author is recognised

if [ $# -eq 1 ]
then
       PARAM1=$1
else
       PARAM1=nothing
fi

SCRIPT_DIR=/home/graham_b/top_ora_proc
TMPFILE1=$SCRIPT_DIR/TOP_ORA_PROC.txt
TMPFILE2=$SCRIPT_DIR/TOP_ORA_PROC2.txt
TMPFILE3=$SCRIPT_DIR/TOP_ORA_PROC3.txt

echo Oracle SID set to: $ORACLE_SID

# Get the top Process ID for your Oracle SID
top -f $TMPFILE1 -d 1 -n 400 -s 1
if [ $PARAM1 = "sleepers" ]
then
       echo Including sleeping processes
       cat $TMPFILE1 | grep -i oracle$ORACLE_SID > $TMPFILE2
else
       echo Excluding sleeping processes
       cat $TMPFILE1 | grep -i oracle$ORACLE_SID | grep -i run > $TMPFILE2
fi

echo Found `wc -l $TMPFILE2 | awk '{print $1}'` processes for $ORACLE_SID

TOP_UNIX_PROCESS=`awk '{print $3}' $TMPFILE2 | head -1`

if [ `wc -l $TMPFILE2 | awk '{print $1}'` -eq 0 ]
then
       echo exiting
else
echo Top Unix process for $ORACLE_SID is $TOP_UNIX_PROCESS
# Now we need to do some fancy SQL to get the SQL to find out what the hell that process is doing.

sqlplus -s / as sysdba >/dev/null 2>&1 <<ENDREAD
set pages 10000
set lines 150
set trimspool on
spool $TMPFILE3

select sesion.sid,
       sql_text as "SQL TEXT",
       substr(sesion.username,1,16) as "USERNAME" ,
       substr(sesion.osuser,1,22) as "OS USER"
  from v\$sqltext sqltext, v\$session sesion, v\$process process
 where process.SPID=$TOP_UNIX_PROCESS
   and sesion.PADDR=process.ADDR
   and sesion.sql_hash_value = sqltext.hash_value
   and sesion.sql_address    = sqltext.address
 order by sesion.sid, sqltext.piece
/
ENDREAD

cat $TMPFILE3
rm $TMPFILE3
fi

rm $TMPFILE1 $TMPFILE2

Output looks like this:

graham_b $ ./top_oracle_proc.sh

Oracle SID set to: MYDB001
Excluding sleeping processes
Found 1 processes for MYDB001

Top Unix process for  MYDB001  is 6716

       SID SQL TEXT                                                         USERNAME         OS USER

---------- ---------------------------------------------------------------- ---------------- ----------------------

       559 SELECT count(*) from my_tab1                                     SYS              graham_b

       

1 rows selected.

Please note however that this script will only bring back a result if there are running process's

running the script with parameter "sleepers" will include sleeping processes EG:
graham_b $ ./top_oracle_proc.sh sleepers

copyright © Feb 1st 2012 GRAHAM BOYLE