CASS

--- topsql.sql list  top SQL statements ordered by Elapsed time over the last 4 hours
---
col SNAPIDS_TIME format a24
col EXECS format 9999
col RK format 99
col S_MINS format 9999
col TEXT_SUBSET a30
col CPU_SECS format 999999.99
col ELAPS_SECS format 999999.99
col AVG_ELAPS format 99999.99
break on SNAPIDS_TIME skip 1 

--- Set up title
set termout off serveroutput off heading off timing off
col title_name new_value title_name
select '['||to_char(sysdate,'HH24:MI DD-MON-RR')||' '||INSTANCE_NAME||'@'||HOST_NAME||' '||VERSION||'] ' title_name from  v$instance;
set termout on serveroutput on heading on 

TTitle Left '###  Top 10 SQL statements ordered by Elapsed time in last &&hours hours  -  ' title_name skip 2 

---  Top 10 SQL statements ordered by Elapsed 

select ssnapid||':'||esnapid||' '||to_char(etime,'HH24:MI DDMON') SNAPIDS_TIME, round((etime-stime)*24*60) S_MINS,
time_rank Rk, HASH_VALUE,EXECS,DISK_RDS,BUF_GETS,ROWSP,
round(CPU_TIME/1000000,2)CPU_SECS ,round(ELAPS_TIME/1000000,2) ELAPS_SECS, round((ELAPS_TIME/EXECS)/1000000,2) AVG_ELAPS, TEXT_SUBSET
from (select ssnapid,esnapid,stime,etime,HASH_VALUE,EXECS,DISK_RDS,BUF_GETS,ROWSP,CPU_TIME,ELAPS_TIME,TEXT_SUBSET,
      dense_rank() over (partition by esnapid order by ELAPS_TIME desc) time_rank
      from (select ssnapid,esnapid,stime,etime, HASH_VALUE, TEXT_SUBSET,
            case when EXECUTIONS < nvl(lag(EXECUTIONS) over (partition by hash_value order by sq.snap_id),0) then EXECUTIONS
                 else EXECUTIONS - lag(EXECUTIONS) over (partition by hash_value order by sq.snap_id) END EXECS,
            case when DISK_READS < nvl(lag(DISK_READS) over (partition by hash_value order by sq.snap_id),0) then DISK_READS
                 else DISK_READS - lag(DISK_READS) over (partition by hash_value order by sq.snap_id) END DISK_RDS,
            case when BUFFER_GETS < nvl(lag(BUFFER_GETS) over (partition by hash_value order by sq.snap_id),0) then BUFFER_GETS
                 else BUFFER_GETS - lag(BUFFER_GETS) over (partition by hash_value order by sq.snap_id) END BUF_GETS,
            case when ROWS_PROCESSED < nvl(lag(ROWS_PROCESSED) over (partition by hash_value order by sq.snap_id),0) then ROWS_PROCESSED
                 else ROWS_PROCESSED - lag(ROWS_PROCESSED) over (partition by hash_value order by sq.snap_id) END ROWSP,
            case when CPU_TIME < nvl(lag(CPU_TIME) over (partition by hash_value order by sq.snap_id),0) then CPU_TIME
                 else CPU_TIME - lag(CPU_TIME) over (partition by hash_value order by sq.snap_id) END CPU_TIME,
            case when ELAPSED_TIME < nvl(lag(ELAPSED_TIME) over (partition by hash_value order by sq.snap_id),0) then ELAPSED_TIME
                 else ELAPSED_TIME - lag(ELAPSED_TIME) over (partition by hash_value order by sq.snap_id) END ELAPS_TIME
            from STATS$SQL_SUMMARY sq,
                 (SELECT iv.start_snap_id as ssnapid, iv.end_snap_id as esnapid, iv.start_snap_time as stime, iv.end_snap_time as etime
                  FROM (SELECT lag(dbid) over (order by dbid, instance_number, snap_id) AS start_dbid, dbid AS end_dbid,
                        lag(snap_id) over (order by dbid, instance_number, snap_id) AS start_snap_id, snap_id AS end_snap_id,
                        lag(instance_number) over (order by dbid, instance_number, snap_id) AS start_inst_nr, instance_number AS end_inst_nr,
                        lag(snap_time) over (order by dbid, instance_number, snap_id) AS start_snap_time, snap_time AS end_snap_time,
                        lag(startup_time) over (order by dbid, instance_number, snap_id) AS start_startup_time, startup_time AS end_startup_time
                        FROM perfstat.stats$snapshot
                        where SNAP_TIME > sysdate - &&hours/24
                        ) iv
                  WHERE iv.start_snap_id IS NOT NULL
                  AND iv.start_dbid=iv.end_dbid
                  AND iv.start_inst_nr=iv.end_inst_nr
                  AND iv.start_startup_time=iv.end_startup_time) i
            where i.esnapid = sq.SNAP_ID
            )
   Where EXECS is not null and  EXECS >0
   )
where time_rank < 11
order by etime, time_rank; 

TTitle off