oracle-查询指定时间范围内最消耗资源的前100条sql

SET SERVEROUT ON SIZE UNLIMITED;
REM 查询指定时间范围内最消耗资源，按照elapsed_time、cpu_time或者buffer_gets，gets_per_row排序的前100条sql
PRO
PRO
PRO 查询指定时间范围内最消耗资源，按照elapsed_time、cpu_time或者buffer_gets，gets_per_row排序的前100条sql
SET TERM ON ECHO OFF;
SET VERIFY off;
ACCEPT begintime CHAR PROMPT 'Enter begin_time(eg:201805030001): '
ACCEPT endtime CHAR PROMPT 'Enter end_time(eg:201805031001): '
ACCEPT rank CHAR DEFAULT 'time_rank' PROMPT 'Enter Rank column(eg:time_rank,cputime_rank,gets_rank,gets_per_row_rank): '
col begintime new_value begintime noprint
col endtime new_value endtime noprint

set line 250
set pages 150
col begin_time for a12
col end_time for a12
col sql_id for a15
col plans_diff for 99
col cputime_per_exec for 999990.999
col time_per_exec for 999990.999
col gets_per_exec for 999999999
col rows_per_exec for 99999990.99
col cputime_delta for 99999990.999
col time_delta for 999999990.999
col exec_delta for 9999999999999
col gets_d for 9999999999
col rows_d for 999999999
col cputime_rank for 9999
col time_rank for 9999
col gets_rank for 9999
col gets_per_row_rank for 9999

spool /tmp/tmp.sql
select * from (
select 
    a.sql_id sql_id,
    to_char(min(b.begin_interval_time),'yyyymmddhh24') begin_time,
    to_char(max(b.end_interval_time),'yyyymmddhh24') end_time,
    count(distinct(a.plan_hash_value)) plans_diff,
    round(decode(sum(a.EXECUTIONS_DELTA),0,sum(a.CPU_TIME_DELTA/1000000),sum(a.CPU_TIME_DELTA/1000000)/sum(a.EXECUTIONS_DELTA)),3) cputime_per_exec,
    round(decode(sum(a.EXECUTIONS_DELTA),0,sum(a.ELAPSED_TIME_DELTA/1000000),sum(a.ELAPSED_TIME_DELTA/1000000)/sum(a.EXECUTIONS_DELTA)),3) time_per_exec,
    round(decode(sum(a.EXECUTIONS_DELTA),0, sum(a.BUFFER_GETS_DELTA ), sum(a.BUFFER_GETS_DELTA )/sum(a.EXECUTIONS_DELTA)),0) gets_per_exec,
    round(decode(sum(a.EXECUTIONS_DELTA),0,sum(a.ROWS_PROCESSED_DELTA),sum(a.ROWS_PROCESSED_DELTA)/sum(a.EXECUTIONS_DELTA)),2) rows_per_exec,
    round(sum(a.CPU_TIME_DELTA/1000000),3) cputime_delta,
    round(sum(a.ELAPSED_TIME_DELTA/1000000),3) time_delta,
    sum(a.EXECUTIONS_DELTA) exec_delta,
    sum(a.BUFFER_GETS_DELTA ) gets_delta,    
    sum(a.ROWS_PROCESSED_DELTA) rows_delta,
    DENSE_RANK() OVER (ORDER BY decode(sum(a.EXECUTIONS_DELTA),0,sum(a.CPU_TIME_DELTA/1000000),sum(a.CPU_TIME_DELTA/1000000)/sum(a.EXECUTIONS_DELTA)) DESC) cputime_rank,
    DENSE_RANK() OVER (ORDER BY decode(sum(a.EXECUTIONS_DELTA),0,sum(a.ELAPSED_TIME_DELTA/1000000),sum(a.ELAPSED_TIME_DELTA/1000000)/sum(a.EXECUTIONS_DELTA)) DESC) time_rank,
    DENSE_RANK() OVER (ORDER BY decode(sum(a.EXECUTIONS_DELTA),0, sum(a.BUFFER_GETS_DELTA), sum(a.BUFFER_GETS_DELTA )/sum(a.EXECUTIONS_DELTA)) DESC) gets_rank,
    DENSE_RANK() OVER (ORDER BY decode(sum(a.ROWS_PROCESSED_DELTA),0, sum(a.BUFFER_GETS_DELTA)/sum(EXECUTIONS_DELTA), sum(a.BUFFER_GETS_DELTA )/sum(a.ROWS_PROCESSED_DELTA)) DESC) gets_per_row_rank
from  
    dba_hist_sqlstat a,
    dba_hist_snapshot b
where
    a.snap_id = b.snap_id
and
    b.BEGIN_INTERVAL_TIME between to_date('&begintime','yyyymmddhh24mi') and to_date('&endtime','yyyymmddhh24mi')
and 
    a.executions_delta > 0    
group by 
    a.sql_id
having 
   sum(a.EXECUTIONS_DELTA) > round(to_number(to_date('&begintime','yyyymmddhh24mi') - to_date('&endtime','yyyymmddhh24mi')) * 24)
)
where &rank <= 100  
order by &rank,time_rank,cputime_rank ,gets_per_row_rank,gets_rank;
spool off