oracle 11G 慢查询监控脚本---可直接打印执行计划等待事件并发送邮件

以下为oracle 11G 慢查询监控脚本，可直接打印执行计划等待事件并发送邮件

#!/bin/bash
. ~/.bash_profile
export LANG=en_US.utf-8
dbuser="dbuser"
dbpass="dbpass"
outputfile="/home/oracle/slowsql/slowsql.txt"
single_quote=\'
h_name=`cat /etc/sysconfig/network|grep HOST|awk -F"[=]" '{print $2}'`

echo > $outputfile
sql__id=`sqlplus -s $dbuser/$dbpass <<END
set pagesize 0 feedback off verify off heading off echo off 
select sql_id from gv\\\$session_longops t where t.last_update_time > sysdate-1/48 and t.OPNAME not like 'RMAN%';
exit;
END`

if [ -z "$sql__id" ] ; then
exit
else
echo "$sql__id" |sort|uniq|grep -v ^$|while read i
do
#sleep 2
echo $i >> $outputfile
sqlplus -s $dbuser/$dbpass >> $outputfile <<EOF1 
set pagesize 0 feedback off verify off heading off echo off lines 1000 long 20000000 longchunksize 1000
select dbms_sqltune.report_sql_monitor(sql_id =>$single_quote$i$single_quote,type => 'text') from dual;
exit
EOF1
echo -e "\n\n\n\n" >> $outputfile
done

/bin/echo -e "$h_name The slow query is in the attachment. Please open it in Notepad or EditPlus"|/usr/bin/mutt -s "$h_name More than 5 seconds slow query" xxxx@xxxx.com -a $outputfile
fi

 

监控结果如下：