--cheng.zhang
--查询占资源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('20190510 08','yyyymmdd hh24') and to_date('20190510 18','yyyymmdd hh24')
order by 6 desc) where rownum<10;