tdba_hist_snapshot

set lin 450
col db_host for a20
col TIMEO for a15
WITH tdba_hist_snapshot AS (
select instance_number inst, snap_id,begin_interval_time, end_interval_time, startup_time, round(round((cast(end_interval_time as date) - cast ( begin_interval_time as date)) * 1440 ,4) * 60,0) awr_time_sec
from dba_hist_snapshot
where snap_id between &&begin_snap_id and &&end_snap_id ), SYSTAT as (
select snap_id, inst, max(decode(stat_name,'redo size',(redovalue/1048576))) redosizeMB , max(decode(stat_name,'user commits',redovalue)) commits , max(decode(stat_name,'user calls',redovalue)) Calls , max(decode(stat_name,'user rollbacks',redovalue)) rollback , max(decode(stat_name,'redo writes',redovalue)) redowrites, max(decode(stat_name,'redo KB read',redovalue/1000)) RedoRdMB, max(decode(stat_name,'redo blocks written',redovalue/1000000)) BlkWritten, max(decode(stat_name,'redo write time',redovalue)) WriteTime, max(decode(stat_name,'redo wastage',redovalue/(1048576))) Wastage, max(decode(stat_name,'redo write time',redovalue)) / max(decode(stat_name,'redo writes',redovalue)) TimePerWrite
from (
select snap_id, stat_name, instance_number inst, value - lag( value) over ( partition by stat_name order by dos.instance_number,dos.snap_id ) redovalue
from dba_hist_sysstat dos
where stat_name in ( 'redo writes', 'redo blocks written', 'redo write time', 'redo wastage', 'redo size', 'user commits', 'user calls', 'user rollbacks', 'redo KB read' ) and snap_id between &&begin_snap_id-1 and &&end_snap_id order by snap_id,stat_name ) group by snap_id, inst )
select sys_context('userenv','DB_NAME') ||decode( 0,1,'-'||a.inst)||'-'||sys_context('userenv','server_host') db_host, to_char(begin_interval_time,'DD-MON-YY HH24:MI') Timeo, redosizeMB, redoRdMB, round(redoRdMB/awr_time_sec,0) "RedoRd-s", commits, Calls, redowrites, BlkWritten, WriteTime,
--Wastage
commits/awr_time_sec "commit-s", calls/awr_time_sec "calls-s", (calls/awr_time_sec)/ ( (commits+rollback)/awr_time_sec) "calls-txn", (commits+rollback)/awr_time_sec "txn-s"
--TimePerWrite
from systat a, tdba_hist_snapshot b
where a.snap_id = b.snap_id and a.inst = b.inst order by a.inst