概述
awr报告中的sql order by XX实际上也是根据相关sql查出来的结果，下面分享几个AWR脚本中查询资源占有的SQL，更改想要的snapid就可以查相应时间段的数据库性能信息。
1、查询awr快照ID
先查一下快照ID，后面才可以查快照时间段的问题sql。
select to_char(s.startup_time, 'dd Mon "at" HH24:mi:ss') instart_fmt, di.instance_name inst_name, di.db_name db_name, s.snap_id snap_id, to_char(s.end_interval_time, 'dd Mon YYYY HH24:mi') snapdat, s.snap_level lvl from dba_hist_snapshot s, dba_hist_database_instance di where di.dbid = s.dbid and di.instance_number = s.instance_number and di.startup_time = s.startup_time order by db_name, instance_name, snap_id;
后面演示快照ID范围：11088和11093
2、按执行时间排序
根据快照ID查询按执行时间排序的相关sql
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 where snap_id > 11088 and snap_id <= 11093 group by sql_id order by sum(elapsed_time_delta) desc) where rownum <= 20) s, dba_hist_sqltext st where st.sql_id = s.sql_id order by elapsed_time desc, sql_id;
3、按消耗CPU排序
select s.sql_id, cpu_time / 1000000 cpu_time, elapsed_time / 1000000 elapsed_time, iowait_time / 1000000 iowait_time, gets, reads, rws, clwait_time / 1000000 clwait_time, execs, substr(regexp_replace(st.sql_text, '(\s)+', ' '), 1, 50) sqt, ' ' nl, cpu_time / 1000000 / decode(execs, 0, null, execs) cppe, elapsed_time / 1000000 / decode(execs, 0, null, execs) elpe, iowait_time / 1000000 / decode(execs, 0, null, execs) iope, gets / decode(execs, 0, null, execs) bpe, reads / decode(execs, 0, null, execs) rpe, rws / decode(execs, 0, null, execs) rwpe, clwait_time / 1000000 / decode(execs, 0, null, execs) clpe, ' ' ep, st.sql_text sqtn 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(iowait_delta) iowait_time, sum(clwait_delta) clwait_time from dba_hist_sqlstat where snap_id > 11088 and snap_id <= 11093 group by sql_id order by sum(cpu_time_delta) desc) where rownum <= 10) s, dba_hist_sqltext st where st.sql_id = s.sql_id order by cpu_time desc, sql_id;
4、按I/O排序
select s.sql_id, iowait_time / 1000000 iowait_time, elapsed_time / 1000000 elapsed_time, cpu_time / 1000000 cpu_time, gets, reads, rws, clwait_time / 1000000 clwait_time, execs, substr(regexp_replace(st.sql_text, '(\s)+', ' '), 1, 50) sqt, ' ' nl, iowait_time / 1000000 / decode(execs, 0, null, execs) iope, elapsed_time / 1000000 / decode(execs, 0, null, execs) elpe, cpu_time / 1000000 / decode(execs, 0, null, execs) cppe, gets / decode(execs, 0, null, execs) bpe, reads / decode(execs, 0, null, execs) rpe, rws / decode(execs, 0, null, execs) rwpe, clwait_time / 1000000 / decode(execs, 0, null, execs) clpe, ' ' ep, substr(regexp_replace(st.sql_text, '(\s)+', ' '), 51, 50) sqtn 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(iowait_delta) iowait_time, sum(clwait_delta) clwait_time from dba_hist_sqlstat where snap_id > 11088 and snap_id <= 11093 group by sql_id order by sum(iowait_delta) desc) where rownum <= 20) s, dba_hist_sqltext st where st.sql_id = s.sql_id order by iowait_time desc, reads desc, sql_id;
5、按gets排序
select s.sql_id, gets, reads, elapsed_time / 1000000 elapsed_time, cpu_time / 1000000 cpu_time, iowait_time / 1000000 iowait_time, rws, clwait_time / 1000000 clwait_time, execs, substr(regexp_replace(st.sql_text, '(\s)+', ' '), 1, 50) sqt, ' ' nl, gets / decode(execs, 0, null, execs) bpe, reads / decode(execs, 0, null, execs) rpe, elapsed_time / 1000000 / decode(execs, 0, null, execs) elpe, cpu_time / 1000000 / decode(execs, 0, null, execs) cppe, iowait_time / 1000000 / decode(execs, 0, null, execs) iope, rws / decode(execs, 0, null, execs) rwpe, clwait_time / 1000000 / decode(execs, 0, null, execs) clpe, ' ' ep, substr(regexp_replace(st.sql_text, '(\s)+', ' '), 51, 50) sqtn 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(iowait_delta) iowait_time, sum(clwait_delta) clwait_time from dba_hist_sqlstat where snap_id > 11088 and snap_id <= 11093 group by sql_id order by sum(buffer_gets_delta) desc) where rownum <= 20) s, dba_hist_sqltext st where st.sql_id = s.sql_id order by gets desc, cpu_time desc, sql_id;
6、按执行次数排序
select s.sql_id, execs, elapsed_time / 1000000 elapsed_time, cpu_time / 1000000 cpu_time, iowait_time / 1000000 iowait_time, gets, reads, rws, clwait_time / 1000000 clwait_time, substr(regexp_replace(st.sql_text, '(\s)+', ' '), 1, 50) sqt, ' ' nl, ' ' ep, elapsed_time / 1000000 / decode(execs, 0, null, execs) elpe, cpu_time / 1000000 / decode(execs, 0, null, execs) cppe, iowait_time / 1000000 / decode(execs, 0, null, execs) iope, gets / decode(execs, 0, null, execs) bpe, reads / decode(execs, 0, null, execs) rpe, rws / decode(execs, 0, null, execs) rwpe, clwait_time / 1000000 / decode(execs, 0, null, execs) clpe, substr(regexp_replace(st.sql_text, '(\s)+', ' '), 51, 50) sqtn 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(iowait_delta) iowait_time, sum(clwait_delta) clwait_time from dba_hist_sqlstat where snap_id > 11089 and snap_id <= 11092 group by sql_id order by sum(executions_delta) desc) where rownum <= 20) s, dba_hist_sqltext st where st.sql_id = s.sql_id order by execs desc, sql_id;
有时大家不想查awr报告的话也可以用这几个sql来自定义获取需要的信息。根据需要修改就可以了。
后面会分享更多DBA方面内容，感兴趣的朋友可以关注下！