set head on
column module format a20
set lines 1000 pages 1000
prompt SQL Ordered by Elapsed time
/*FIND THE SNAP ID*/
set pages 0
col begin_interval_time for a30
col end_interval_time for a30
select snap_id,begin_interval_time,end_interval_time from dba_hist_snapshot order by snap_id asc
/
/*FIND SQL BY ELAPSED TIME*/
set pages 0
select * from (
select sql_id, module,
sum(ELAPSED_TIME_DELTA)/1000000 “Elapsed Time(s)”,
sum(CPU_TIME_DELTA)/1000000 “CPU Time(s)”,
sum(executions_delta) “Executions”,
sum(ROWS_PROCESSED_DELTA) rows1,
sum(BUFFER_GETS_DELTA) “Buffer Gets”,
sum(DISK_READS_DELTA) “Physical Reads”,
sum(iowait_delta)/1000000 “IO Wait”,
sum(ccwait_delta)/1000000 cc_wait,
sum(apwait_delta)/1000000 ap_wait,
sum(clwait_delta)/1000000 cl_wait,
sum(BUFFER_GETS_DELTA)/decode(sum(ROWS_PROCESSED_DELTA), 0, 1, sum(ROWS_PROCESSED_DELTA)) gets_per_row,
sum(DISK_READS_DELTA)/decode(sum(ROWS_PROCESSED_DELTA), 0, 1, sum(ROWS_PROCESSED_DELTA)) prds_per_row,
sum(BUFFER_GETS_DELTA)/decode(sum(executions_delta), 0, 1, sum(executions_delta)) gets_per_exec
from dba_hist_sqlstat
where snap_id between &min_snap_id and &max_snap_id
group by sql_id, module
order by 3 desc
) where rownum <= &rnum
/
