实用脚本--一键统计Oracle中最占资源的TOP 30SQL
概述
今天主要分享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方面的内容,感兴趣的朋友可以关注下~
相关推荐
lklong 2020-11-22
oraclemch 2020-11-06
shilukun 2020-10-10
周嘉笙 2020-11-09
iilegend 2020-10-19
EricRay 2020-10-16
zhuzhufxz 2020-09-16
dataminer 2020-08-17
bfcady 2020-08-16
Hody 2020-08-16
FightFourEggs 2020-08-16
数据库设计 2020-08-16
Seandba 2020-08-16
Omega 2020-08-16
zjyzz 2020-08-16
yanghuatong 2020-08-16
ktyl000 2020-08-16
dbasunny 2020-08-16