logstash抽取oracle慢sql和alert日志

1.通过Oracle快照定位慢日志

  Oracle快照每小时产生一个，每个快照包含了一小时内所需记录sql的执行情况

  快照视图：DBAHISTSQLSTAT

  详解  https : docs. oracle. com/ en/ database oracle oracle - database  12.2/refrn/DBA_HIST_SQLSTAT.html#GUID-F5A246E0-C04A-406C-9E10-AC26E7742F06

2.创建视图，用于查询最新一小时的慢SQL

create or replace view slow_sql_view as
select to_char(sysdate-1/24,'yyyy-mm-dd hh24') as snapshot_time,--快照时间
v_1.sql_id,
v_1.elapsed_time,--一小时内累计耗时
v_1.cpu_time,--一小时内累计CPU时间
v_1.iowait_time,--一小时内累计io等待时间
v_1.gets,--一小时内累逻辑读
v_1.reads,--一小时内累计物理读
v_1.rws,--一小时内累计返回行数
v_1.clwait_time,--一小时内累计集群等待时间
v_1.execs,--一小时内累计执行次数
v_1.elpe,--平均每条SQL消耗时间
nvl(v_2.machine,'null') as machine,--客户服务器名称
nvl(v_2.username,'null') as username,--客户连接用户名
to_char(substr(v_1.sqt,1,3000)) as sql

from
(select s.sql_id,
round(elapsed_time 1000000,2) elapsed_time,
round(cpu_time / 1000000,2) cpu_time,
round(iowait_time / 1000000,2) iowait_time,
gets,
reads,
rws,
round(clwait_time / 1000000,2) clwait_time,
execs,
st.sql_text sqt,
round(elapsed_time / 1000000 / decode(execs, 0, null, execs),2) 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
where snap_id =(select max(snap_id) from dba_hist_snapshot)
group by sql_id
order by sum(elapsed_time_delta) desc)
where rownum =1 --平均执行时间大于1s的sql过滤出来
order by elpe desc
;