TOTSQL

set lin 450
col db_name for a20
col module for a30
WITH TOTSQL as (SELECT instance_number inst, snap_id, sum(buffer_gets_delta+disk_reads_delta) totIo, sum(buffer_gets_delta) totbuf, sum(disk_reads_delta) totdsk
FROM dba_hist_sqlstat
WHERE snap_id between &&begin_snap_id and &&end_snap_id GROUP by instance_number,snap_id )
SELECT sys_context('userenv','DB_NAME') ||decode(1,1,'-'||a.inst) db_name , a.snap_id, to_char(end_interval_time, 'DD-MON-YY HH24:MI') TimeO, sql_id, rpad(substr(module,1,16),16,' ') ||' - '||cmd module , exe, bufgets, round(bufgets/totbuf*100,2) "%TotBuf", diskRd , round(diskRd/totdsk * 100,2) "%TotDsk", round(((bufgets+diskrd)/(totio) * 100),2) "%TotIO"
FROM (
SELECT instance_number inst, a.SNAP_ID,a.sql_id, decode(command_type,2,'INS',3,'SEL', 6,'UPD',7,'DEL',47,'PLS',170,'CALL',command_type) CMD, decode(module,null,'*NULL*',module) Module,action, executions_delta EXE, BUFFER_GETS_DELTA BUfgets,DISK_READS_DELTA DiskRd, rank () over ( partition by instance_number,snap_id order by buffer_gets_delta desc ) as rnk
FROM dba_hist_sqlstat a, dba_hist_sqltext b
where a.snap_id between &&begin_snap_id and &&end_snap_id and a.sql_id = b.sql_id and executions_delta is not null order by snap_id )a, TOTSQL, DBA_HIST_SNAPSHOT DH
WHERE rnk < 6 and a.snap_id = totsql.snap_id and a.snap_id = dh.snap_id and a.inst = dh.instance_number and a.inst = totsql.inst order by a.inst, a.snap_id, rnk