共計 3679 個字符，預計需要花費 10 分鐘才能閱讀完成。
本篇內容主要講解“Oracle 慢 SQL 監控腳本代碼分享”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓丸趣 TV 小編來帶大家學習“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 監控腳本代碼分享”有了更深的了解，不妨來實際操作一番吧！這里是丸趣 TV 網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！
