sql按时间排序_实用脚本--Oracle AWR中常用到的几个SQL语句

概述

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