Oracle慢SQL監控腳本代碼分享

本篇內容主要講解“Oracle慢SQL監控腳本代碼分享”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“Oracle慢SQL監控腳本代碼分享”吧!

線上Oracle準備實現類似MySQL slow query的監控腳本，把查詢時間超出定值的SQL定時的發送郵件告警，實現過程記錄如下：

主要思路是通過DBA_HIST的幾個視圖來獲取每小時快照中慢SQL的情況，為了不影響線上環境，這里把腳本部署在了自己的監控端，通過DBLINK定期的抓取線上生產庫的數據到監控數據庫，并簡單的處理后獲得csv格式的報表，發送報表至郵箱。

定時腳本 每小時查詢一次

00 * * * *  /opt/scripts/oracle/get_slow_query.sh

腳本內容如下

[oracle@59-Mysql-Test ~]$ cat /opt/scripts/oracle/get_slow_query.sh

#!/bin/bash

errlog="/opt/scripts/oracle/sqlerror.log"

sq_data="/opt/scripts/oracle/slow_query_data.xls"

check_file="/opt/scripts/oracle/slowsql_check.log"

send_mail_check="/opt/scripts/oracle/send_mail.chk"

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

export ORACLE_SID=oramon

export PATH=/usr/sbin:$PATH

export PATH=/u01/app/oracle/product/11.2.0/db_1/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export CLASSPATH=/u01/app/oracle/product/11.2.0/db_1/JRE:/u01/app/oracle/product/11.2.0/db_1/jlib:/u01/app/oracle/product/11.2.0/db_1/rdbms/jlib

cd /opt/scripts/oracle/

$ORACLE_HOME/bin/sqlplus -S sqmon/oracle @main  > ${errlog}

cat ${errlog} | grep -v 'Call completed.' | grep -v '' > ${check_file}

[ -s ${check_file} ] && /bin/mail -s "Oracle slow query check error" xxx@xxx.com < ${check_file}

cat ${sq_data} | grep -v '<' >${send_mail_check}

[ -s ${send_mail_check} ] && /bin/mail -a ${sq_data} -s "OracleDB find slow query,please check" xxx@xxx.com,xxx@xxx.com

[oracle@59-Mysql-Test oracle]$ cat main.sql

call  pro_get_slow_query();

set linesize 5000

set term off verify off feedback off pagesize 999

set markup html on entmap ON spool on preformat off

spool slow_query_data.xls

@get_tables.sql

spool off

exit

[oracle@59-Mysql-Test oracle]$ cat get_tables.sql

select sql_id,elapsed_time,cpu_time,iowait_time,gets,reads,rws,clwait_time,execs,elpe,machine,username,dbms_lob.substr(sqt,4000) from DBA_ORA_SLOW_QUERY where elpe > 10 and machine not in ('rac01','rac02');

存儲過程pro_get_slow_query內容如下

CREATE OR REPLACE PROCEDURE SQMON.pro_get_slow_query

AS

BEGIN

/**********delete old data on sqltext*************/

delete from local_dba_hist_sqltextas;

commit;

insert into local_dba_hist_sqltextas select * from dba_hist_sqltext@dg2;

commit;

insert into DBA_ORA_SLOW_QUERY_HISTORY select a.*,sysdate from DBA_ORA_SLOW_QUERY;

commit;

delete from DBA_ORA_SLOW_QUERY;

commit;

/*

select * from DBA_ORA_SLOW_QUERY;

select * from DBA_ORA_SLOW_QUERY_HISTORY;

*/

/************insert new date ********************/

insert into  DBA_ORA_SLOW_QUERY

select v_1.sql_id,

       v_1.elapsed_time,

       v_1.cpu_time,

       v_1.iowait_time,

       v_1.gets,

       v_1.reads,

       v_1.rws,

       v_1.clwait_time,

       v_1.execs,

       v_1.elpe,

       v_2.machine,

       v_2.username,

       v_1.sqt

  from (select s.sql_id,

               elapsed_time / 1000000 elapsed_time,

               cpu_time / 1000000 cpu_time,

               iowait_time / 1000000 iowait_time,

               gets,

               reads,

               rws,

               clwait_time / 1000000 clwait_time,

               execs,

               st.sql_text sqt,

               elapsed_time / 1000000 / decode(execs, 0, null, execs) elpe

          from (select *

                  from (select sql_id,

                               sum(executions_delta) execs,

                               sum(buffer_gets_delta) gets,

                               sum(disk_reads_delta) reads,

                               sum(rows_processed_delta) rws,

                               sum(cpu_time_delta) cpu_time,

                               sum(elapsed_time_delta) elapsed_time,

                               sum(clwait_delta) clwait_time,

                               sum(iowait_delta) iowait_time

                          from dba_hist_sqlstat@HUBSDG2

                         where snap_id >=

                               (select max(snap_id) - 1

                                  from dba_hist_snapshot@DG2)

                           and snap_id <=

                               (select max(snap_id)

                                  from dba_hist_snapshot@DG2)

                         group by sql_id

                         order by sum(elapsed_time_delta) desc)

                 where rownum <= 20) s,

               local_dba_hist_sqltextas st

         where st.sql_id = s.sql_id) v_1

  left join (select distinct a.sql_id, a.machine, b.username

               from dba_hist_active_sess_history@DG2 a

               left join dba_users@DG2 b

                 on a.user_id = b.user_id

              where a.snap_id >=

                    (select max(snap_id) - 1 from dba_hist_snapshot@DG2)

                and a.snap_id <=

                    (select max(snap_id) from dba_hist_snapshot@DG2)) v_2

    on v_1.sql_id = v_2.sql_id

 order by elpe desc;

commit;

END;

/

上面標綠加粗部分的表不再列出，可以直接通過CTAS格式去創建即可。

到此，相信大家對“Oracle慢SQL監控腳本代碼分享”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！