Search This Blog

Total Pageviews

Thursday 3 November 2011

Oracle on Unix to find top sql

oracle@apt-amd-02:~> cat top.sh

date
echo "-------- ----------- -------- ------- ------- ------- ----------- --- --------"
ps -ef|grep LOCAL|grep -v grep|cut -c1-15,42-79|sort -rn +2 | head -10 | while read LINE
do
SIDNAME=`echo $LINE | awk '{ print $4 }' | cut -c7-14`
CPUTIME=`echo $LINE | awk '{ print $3 }'`
UNIXPID=`echo $LINE | awk '{ print $2 }'`
#echo $SIDNAME $CPUTIME $UNIXPID
sqlplus -s "/ as sysdba" < set pages 0 lines 80 trims on echo off verify off pau off
set linesize 180
set pagesize 2000
select t.* from dba_hist_sqltext ht,table(dbms_xplan.display_awr(ht.sql_id, null, null,'ALL')) t
where ht.sql_id in ( select s.sql_id from v\$process p, v\$session s
where p.addr=s.paddr
and p.spid=$UNIXPID);
EOF
done
echo "-------- ----------- -------- ------- ------- ------- ----------- --- --------"
date

No comments:

Oracle DBA

anuj blog Archive