[指令碼] 查詢wait event的session以及對應的OS程式

#!/bin/sh
##################################################################
# script. name    :test.sh
# author         :copy from YongHuang's script
#                 http://yong321.freeshell.org/computer/Perfmon.html#begin
# input parameter:
#
# modify history : create at 2008-03-31 for test
#                  2008-04-01   1.Add top section
#                               2.Tuning ps performance
##################################################################

rm -rf tmp.txt
rm -rf tmp.log

echo "Date:">>tmp.log
date "+%Y%m%d%H%M%S" >> tmp.log
echo "================================================" >>tmp.log
echo " Top process:">>tmp.log
echo "================================================" >>tmp.log
top -b | head -15 | sed 's/  *$//' | grep -v ' top$' >> tmp.log

echo "================================================" >>tmp.log
echo "Top session:" >> tmp.log
echo "================================================" >>tmp.log
sqlplus -S /nolog <> tmp.log
connect /as sysdba
set pages 1000 lines 200 feedb 0
col i for 9
col evnt for a30
col usr for a15
col mach for a20
col prog for a20
spool tmp.txt
select b.spid,a.sid,substr(a.username,1,15) usr,substr(a.machine,1,20) mach,substr(a.program,1,20) prog,a.sql_address,a.prev_sql_addr,substr(c.event,1,30) evnt,c.p1,c.p2,c.p3,b.pga_alloc_mem pga
from v\$session a,v\$process b, v\$session_wait c where a.paddr=b.addr and a.sid=c.sid and event not like 'SQL*Net%' order by 1,3;
spool off
exit
EOF
echo "================================================">>tmp.log
echo "Session's OS process:" >>tmp.log
echo "================================================">>tmp.log

awk '
{
        if ($1 ~ /^[0-9]+$/)
        {
                SPID=SPID$1",";
        }
}END{print SPID;}' tmp.txt

ps -fp $SPID >>tmp.log
    
exit 0

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/81/viewspace-220369/，如需轉載，請註明出處，否則將追究法律責任。