查看前五个最占用CPU的Oracle会话进程

作者: ntsiase  出自:

很多时候数据库突然出现性能问题,做statspack又得等一段时间,我们可以通过操作系统脚本获取现在最占资源的进程及正在执行的SQL.

大家可以参考一下：

QUOTE:

#!/bin/bash

ps -e -o pcpu -o pid -o user -o
args | grep oraclemktdb | sort -k 1| tail -5r
spid=`ps -e -o pcpu -o pid -o user -o
args | grep oraclemktdb | sort -k 1| tail -5r | awk '{print $2}'`
for i in $spid
do
sqlplus -S /nolog << EOF
conn / as sysdba
set feedback off
set linesize 200
set pagesize 70
column spid format 99999
column sid format 99999
column module format a20
column username format a8
column sql_text format a60
select distinct c.spid,b.sid,b.username,
a.module,a.hash_value,sql_text
from v$sql a,v$session b,v$process c
where a.hash_value=b.sql_hash_value and
a.address=b.sql_address and b.paddr=c.addr and c.spid =$i;
exit
EOF
done

－－－－

Select a.Username, a.SID, a.SERIAL# ,b.Sql_Text,c.spid
From V$Session a, V$Sqlarea b,v$process c
Where a.Username 'HYJ'
And a.Username Is Not Null
And (a.Sql_Address = b.Address Or a.Prev_Sql_Addr = b.Address)
and a.PADDR=c.ADDR
and c.SPID='860464'