[脚本] 查询wait event的session以及对应的OS进程

#!/bin/sh
6Vv du]g;E w0##################################################################ITPUB个人空间a?9aWw2WvWk\!o
# script. name    :test.shITPUB个人空间V3]!D!]P-E(m2O go
# author         :copy from YongHuang's script
_7NTX1Xxw0#                http://yong321.freeshell.org/computer/Perfmon.html#beginITPUB个人空间B3rV*@y)AW*A R
# input parameter:
`K:Ua~*Tg0#
7S*?e3|Ic4mS0# modify history : create at 2008-03-31 for testITPUB个人空间2nc?;xt
#                  2008-04-01   1.Add top section
L6i+d)Y8a#a0#                               2.Tuning ps performanceITPUB个人空间W/e$T'k-k6H t~ J
##################################################################

rm -rf tmp.txtITPUB个人空间 a6T'AF/X
rm -rf tmp.log

echo "Date:">>tmp.logITPUB个人空间 Cl'B7iW2}*^)VH
date "+%Y%m%d%H%M%S" >> tmp.logITPUB个人空间{O f%R.~
echo "================================================" >>tmp.logITPUB个人空间f,b]guW3U
G
echo " Top process:">>tmp.log
g#Uc,p \8c!U0echo "================================================" >>tmp.log
fd Phi Du-J1t0top -b | head -15 | sed 's/  *$//' | grep -v ' top$' >> tmp.log

echo "================================================" >>tmp.log
9@3F'T/NLa7A0echo "Top session:" >> tmp.logITPUB个人空间X E/[~#__2r:P
echo "================================================" >>tmp.logITPUB个人空间3xqIm&v4Z3k{]
sqlplus -S /nolog <<EOF>> tmp.logITPUB个人空间%zS+z WuM(n5M;iZ
connect /as sysdbaITPUB个人空间9Pe2f*t9^[3B@.k
set pages 1000 lines 200 feedb 0
0UXD c:N0col i for 9
p;r,T4FY8b MH0col evnt for a30ITPUB个人空间*h uiGbs
col usr for a15ITPUB个人空间Z(O-Fm4Be ZVq,E7o
col mach for a20ITPUB个人空间2DN0p+](Za
col prog for a20ITPUB个人空间3\:oO"O1R#~O
spool tmp.txtITPUB个人空间
XGRM Q-rNA
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 pgaITPUB个人空间+D*WNi%M:Ys
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;ITPUB个人空间 bh@hf0h3i
spool off
-T3Y/T&f4WO0exitITPUB个人空间9C,swl2X%STf c"O:W
EOFITPUB个人空间K#}?&Sa@ c
echo "================================================">>tmp.logITPUB个人空间#T#yj,J7a5`7kF
echo "Session's OS process:" >>tmp.log
pX@9Y8n"]h0echo "================================================">>tmp.log

awk 'ITPUB个人空间NqFKk
{ITPUB个人空间:zF?
B I3k"Z
        if ($1 ~ /^[0-9]+$/)
m1o)Xy}9T
MV&n0        {ITPUB个人空间-l$r(\wx(`@KwX
                SPID=SPID$1",";ITPUB个人空间3T
r+`^fSz*A
        }ITPUB个人空间Z+y g8X I1]e
}END{print SPID;}' tmp.txt

ps -fp $SPID >>tmp.log
'M0fy;Mu9B0    
){,EX:]Rs,~0exit 0