概述

今天主要分享Oracle一个简单脚本，去统计占资源的top 30 SQL，然后针对这些sql去做优化。

实用脚本

查询占资源TOP 30SQL，可根据不同列排名修改排序值，查询指定时间区间

select *
 from (select st.sql_id,
 st.PLAN_HASH_VALUE,
 ss.begin_interval_time,
 st.module,
 CPU_Time_delta CPU_Time,
 nvl(executions_delta, 0) exe_num,
 trunc(elapsed_time_delta / 1000000) exe_time,
 trunc((elapsed_time_delta /
 decode(nvl(executions_delta, 0),
 0,
 1,
 executions_delta)) / 1000000) avg_exe_time, --平均执行时间
 buffer_gets_delta lg_read,
 trunc((buffer_gets_delta /
 decode(nvl(executions_delta, 0),
 0,
 1,
 executions_delta))) avg_lg_read, --平均逻辑读
 st.disk_reads_delta wl_read,
 trunc((disk_reads_delta /
 decode(nvl(executions_delta, 0),
 0,
 1,
 executions_delta))) avg_wl_read, --平均物理读
 st.physical_read_bytes_delta / 1024 wl_readKB,
 sql_text
 from dba_hist_sqlstat st
 inner join dba_hist_snapshot ss on st.snap_id = ss.snap_id
 and st.instance_number =
 ss.instance_number
 inner join dba_hist_sqltext sx on st.sql_id = sx.sql_id
 where begin_interval_time between
 to_date('20191126 08', 'yyyymmdd hh24') and
 to_date('20191128 15', 'yyyymmdd hh24')
 order by 6 desc)
 where rownum < 30;

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~