Friday, October 30, 2009

Script Monitor CPU usage

#usr/bin/sh!
test=`ps -eaf -o pid|sort -r |head -200`
test=`echo $test | tr " " ","`;

tme=`date +'%d-%m-%y %H:%M'`
echo $tme

${ORACLE_HOME}/bin/sqlplus -s << @@EOF@@
/ as sysdba
SELECT n.username,s.SID,
TRUNC(((s.VALUE * 100) / (SELECT VALUE
FROM v\$sysstat
WHERE NAME = 'CPU used by this session')
),
2
) "% CPU Usage",
a.sql_text
FROM v\$sesstat s, v\$statname t, v\$session n, v\$process p, v\$sqlarea a
WHERE p.spid IN ($test)
AND s.statistic# = t.statistic#
AND n.SID = s.SID
AND DECODE (n.sql_address,
n.sql_address, n.prev_sql_addr,
n.sql_address
) = a.address
AND DECODE (n.sql_hash_value,
n.sql_hash_value, n.prev_hash_value,
n.sql_hash_value
) = a.hash_value
AND n.paddr = p.addr
AND t.NAME = 'CPU used by this session'
AND s.VALUE <> 0
ORDER BY s.VALUE DESC;

exit

No comments: