实时获得最耗CPU资源的SQL语句

#!/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,&＃39;yyyy/mm/dd hh24:mi:ss&＃39;) as login_time
  from v/$session
 where paddr in ( select addr from v/$process where spid&＃61;$1);

select sql_text
  from v/$sqltext_with_newlines
  where hash_value &＃61; &hash_value
 order by piece;
exit;
EOF

#!/bin/ksh
# creator:NinGoo
# function: get top cpu sql
# parameter: N
# useage: topsql.sh N

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

topcpu&＃61;&＃96;ps auxw|grep LOCAL|sort -rn &＃43;2 |head -$1|awk &＃39;{print $2}&＃39;&＃96;
i&＃61;0

for spid in $topcpu
do
i&＃61;&＃96;expr $i &＃43; 1&＃96;
echo "/033[32;1m&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;top $i cpu sql&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;/033[0m"
. /home/oracle/worksh/get_by_spid.sh $spid
done