查看前五个最占用CPU的Oracle会话进程
来源：岁月联盟
时间：2008-02-02
很多时候数据库忽然出现性能问题,做statspack又得等一段时间,我们可以通过操作系统脚本获取现在最占资源的进程及正在执行的SQL.大家可以参考一下：
#!/bin/bashps -e -o pcpu -o pid -o user -o args | grep oraclemktdb | sort -k 1| tail -5rspid=`ps -e -o pcpu -o pid -o user -o args | grep oraclemktdb | sort -k 1| tail -5r | awk '{print $2}'`for i in $spiddosqlplus -S /nolog << EOFconn / as sysdbaset feedback offset linesize 200set pagesize 70column spid format 99999column sid format 99999column module format a20column username format a8column sql_text format a60select distinct c.spid,b.sid,b.username,a.module,a.hash_value,sql_textfrom v$sql a,v$session b,v$process cwhere a.hash_value=b.sql_hash_value and a.address=b.sql_address and b.paddr=c.addr and c.spid =$i;exitEOFdone－－－－Select a.Username, a.SID, a.SERIAL# ,b.Sql_Text,c.spidFrom V$Session a, V$Sqlarea b,v$process cWhere a.Username 'HYJ'And a.Username Is Not NullAnd (a.Sql_Address = b.Address Or a.Prev_Sql_Addr = b.Address)and a.PADDR=c.ADDRand c.SPID='860464' |
