Top 20 CPU Utilized Session


#!/bin/ksh 
# 
# Find Highest CPU used Oracle processes and get the Username 
# and SID from oracle 
# Only 3 character SIDNAME is displayed - Adjust the script according to your need. 
# 
date 
echo " Top 20 CPU Utilized Session from `hostname`" 
echo " ============================================" 
echo "O/S Oracle Session Session Serial UNIX Login Ora CPU Time" 
echo "ID User ID Status ID No ID MMDD:HHMISS SID Used" 
echo "-------- ----------- -------- ------- ------- ------- ----------- --- --------" 
ps -ef|grep LOCAL|cut -c1-15,42-79|sort -rn +2 | head -20 | while read LINE 
do 
SIDNAME=`echo $LINE | awk '{ print $4 }' | cut -c7-14` 
CPUTIME=`echo $LINE | awk '{ print $3 }'` 
UNIXPID=`echo $LINE | awk '{ print $2 }'` 
#echo $SIDNAME $CPUTIME $UNIXPID 
export ORACLE_SID=$SIDNAME 
SIDNAME=`echo $ORACLE_SID | cut -c4-6` 
export ORACLE_HOME=`/dba_script/bin/find_ohome.sh ${ORACLE_SID}` 
export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib 
export TMPDIR=/tmp 
export LD_LIBRARY_PATH=$ORACLE_HOME/lib 
export SQLPLUS="$ORACLE_HOME/bin/sqlplus -s / " 
$SQLPLUS >> $wlogfile <<EOF 
set pages 0 lines 80 trims on echo off verify off pau off 
column pu format a8 heading 'O/S|ID' justify left 
column su format a11 heading 'Oracle|User ID' justify left 
column stat format a8 heading 'Session|Status' justify left 
column ssid format 999999 heading 'Session|ID' justify right 
column sser format 999999 heading 'Serial|No' justify right 
column spid format 999999 heading 'UNIX|ID' justify right 
column ltime format a11 heading 'Login|Time' justify right 
select p.username pu, 
s.username su, 
s.status stat, 
s.sid ssid, 
s.serial# sser, 
lpad(p.spid,7) spid, 
to_char(s.logon_time, 'MMDD:HH24MISS') ltime, 
'$SIDNAME $CPUTIME' 
from v\$process p, 
v\$session s 
where p.addr=s.paddr 
and p.spid=$UNIXPID 
union all 
select a.username, 'Kill Me', 'NoOracle', a.pid, a.serial#, 
lpad(a.spid,7) spid, 'KILL UNIXID', '$SIDNAME $CPUTIME' 
from v\$process a 
where a.spid = $UNIXPID 
and not exists (select 1 from v\$session s 
where a.addr=s.paddr); 
EOF 
done 
echo "-------- ----------- -------- ------- ------- ------- ----------- --- --------" 
date 
# 
# End of Script
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: