首先写一个根据spid来或者其SQL的脚本get_by_spid.sh

[oracle@qht107 test]$ cat get_by_spid.sh
#!/bin/ksh
# creator:NinGoo
# function: get sql statement by spid
# parameter: spid
# useage: get_by_spid.sh spid

sqlplus -S /nolog <connect / as sysdba;
col SERIAL# format 999999
col sid format 99999
col username format a10
col machine format a12
col program format a32
col sql_text format a81
set lines 1000
set pages 1000
set verify off
col sql_hash_value new_value hash_value head hash_value
select sid,serial#,username,program,sql_hash_value,to_char(logon_time,'yyyy/mm/dd hh24:mi:ss')

as login_time from v$session where paddr in ( select addr from v$process where spid=$1);

select sql_text
from v$sqltext_with_newlines where hash_value = &hash_value order by piece;
exit;
EOF

然后再在另外一个shell脚本topsql.sh中获得系统中CPU耗用最多的oracle server process的spid，循环调用第一个脚本获得SQL

[oracle@qht107 test]$ cat topsql.sh
#!/bin/ksh
# creator:NinGoo
# function: get top cpu sql
# parameter: N
# useage: topsql.sh N

if [ $# -eq 0 ]; then
echo "Usage: `basename $0` N"
exit 1
fi

topcpu=`ps auxw|grep LOCAL|sort -rn +2 |head -$1|awk '{print $2}'`
i=0

for spid in $topcpu
do
i=`expr $i + 1`
echo "==========top $i spid is $spid==================="
. /home/oracle/my_bin/test/get_by_spid.sh $spid
done

那么调用就很简单了，假如我们要看系统top 3的sql语句，只需要执行topsql.sh 3即可。当然，如果我们自己通过top/topas等工具已经获得spid了，那么只要执行get_by_spid.sh spid就能获得该进程正在执行的sql语句了。