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(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格式去建立即可。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29734436/viewspace-2135279/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【SQL監控】SQL完全監控的指令碼SQL指令碼
- oracle 11g監控SQL指令碼OracleSQL指令碼
- oracle 監控指令碼Oracle指令碼
- Oracle DBA常用監控指令碼Oracle指令碼
- 資料庫效能SQL監控指令碼資料庫SQL指令碼
- 網路卡流量監控指令碼,python實現指令碼Python
- 監控oracle表空間指令碼Oracle指令碼
- oracle會話監控shell指令碼Oracle會話指令碼
- oracle空間使用監控指令碼Oracle指令碼
- 監控指令碼指令碼
- UAVStack的慢SQL資料庫監控功能及其實現SQL資料庫
- SQL Server 監控統計阻塞指令碼資訊SQLServer指令碼
- 透過shell指令碼監控oracle session指令碼OracleSession
- 通過shell指令碼監控oracle session指令碼OracleSession
- oracle DBA 常用監控指令碼1(轉)Oracle指令碼
- mysql監控指令碼MySql指令碼
- DBA監控指令碼指令碼
- session指令碼監控Session指令碼
- 埠監控指令碼指令碼
- listener監聽監控指令碼指令碼
- 監控使用高cpu的sql語句指令碼SQL指令碼
- oracle使用監控指令碼_ck_tablespace.shOracle指令碼
- oracle 長sql監控OracleSQL
- Oracle常用監控SQLOracleSQL
- ogg監控指令碼指令碼
- stap監控IO指令碼指令碼
- 【shell】磁碟監控指令碼指令碼
- 通過shell指令碼監控sql執行頻率指令碼SQL
- 透過shell指令碼監控sql執行頻率指令碼SQL
- mysql 的一個監控指令碼,監控heartbeatMySql指令碼
- 監控Oracle系統中鎖的常用指令碼Oracle指令碼
- 監控Oracle資料庫的常用shell指令碼Oracle資料庫指令碼
- Oracle-常用監控SQLOracleSQL
- Oracle常用監控SQL(轉)OracleSQL
- mysql mon 的一個監控指令碼,監控heartbeatMySql指令碼
- PostgreSQL之鎖監控指令碼SQL指令碼
- memcached程式埠監控指令碼指令碼
- Nagios 監控ESXI指令碼iOS指令碼