AWR_SYSSTAT_IO

 with AWR_SYSSTAT_IO as (
select to_char(begin_interval_time,'DD-MON-YY HH24') HR, sum(RedoGB)/(1048576*1000000) RedoGB, SUm(phyWrt)/(1000000) PhyWrt, sum(BlkChg)/(1000000) BlkChg
from (
select a.instance_number, a.snap_id, to_char(begin_interval_time, 'DD-MON-YY HH24') dt, stat_name ,begin_interval_time, decode(stat_name,'redo size',value - lag (value) over ( partition by a.instance_number, startup_time,stat_name order by a.snap_id )) RedoGB, decode(stat_name,'db block changes',value - lag (value) over ( partition by a.instance_number, startup_time,stat_name order by a.snap_id )) PhyWrt, decode(stat_name,'physical writes',value - lag (value) over ( partition by a.instance_number, startup_time,stat_name order by a.snap_id )) BlkChg
from (
SELECT dos.instance_number, dos.snap_id, dos.stat_name, sum(value) value
FROM dba_hist_sysstat dos, dba_hist_stat_name ws
Where dos.snap_id between 42235 and 42259 and dos.stat_id = ws.stat_id and ws.stat_name in ( 'db block changes', 'redo size', 'physical writes')
--and    ws.stat_name = 'physical writes'
group by dos.snap_id, dos.stat_name, dos.instance_number ) a, dba_hist_snapshot b
where a.snap_id = b.snap_id and a.instance_number =b.instance_number ) group by to_char(begin_interval_time,'DD-MON-YY HH24') order by 1 ), AWR_DML as (
select to_char(begin_interval_time, 'DD-MON-YY HH24') hr, max(decode(command_type,2,drows/1000000)) INSRT, max(decode(command_type,3,drows/1000000)) SEL, max(decode(command_type,6,drows/1000000)) UPD, max(decode(command_type,7,drows/1000000)) DEL
from (
select a.SNAP_ID,sum(ROWS_PROCESSED_DELTA ) drows,command_type
from dba_hist_sqlstat a, dba_hist_SQLTEXT b
where a.snap_id between 42235 and 42259 and executions_delta is not null and a.sql_id = b.sql_id and command_type in ( 3,2,6,7) group by a.snap_id ,command_type ) a, dba_hist_snapshot b
where a.snap_id = b.snap_id and b.instance_number = userenv('instance') group by to_char(begin_interval_time, 'DD-MON-YY HH24') ) , AWR_IOUSAGE_HR as (
select sys_context('userenv','DB_NAME') ||'-'||sys_context('userenv','server_host') db_host, to_char(begin_interval_time,'DD-MON-YY HH24') timeo, sum(dirread_GB) ReadGB, Sum(Write_GB) WriteGB, sum(dirread_gb) + sum(write_gb) "TotIO"
from (
select a.snap_id,to_char(begin_interval_time, 'DD-MON-YY HH24:MI') TimeO, begin_interval_time, round((readMb - lag( ReadMB) over (partition by IO,startup_time,a.instance_number order by a.snap_id))/(1024),1) DirRead_GB , round((writeMb - lag( writeMb) over (partition by IO,startup_time,a.instance_number order by a.snap_id))/(1024),1) Write_GB, readmb, lag( ReadMB) over (partition by IO,startup_time,a.instance_number order by a.snap_id) lag1, startup_time
from (
select instance_number, snap_id, 'TOT-IO' IO, sum(large_read_megabytes+small_read_megabytes) ReadMB, sum(large_write_megabytes+small_write_megabytes) WriteMB
from dba_hist_IOSTAT_FUNCTIOn b
where snaP_id between 42235 and 42259 group by snap_id, instance_number order by snap_id ) a, dba_hist_snapshot b
where a.snap_id = b.snap_id and a.instance_number = b.instance_number
--and a.instance_number = userenv('instance')
) group by to_char(begin_interval_time,'DD-MON-YY HH24') ), AWR_DT as (
SELECT trunc(max(begin_interval_time)) awrdt
from dba_hist_snapshot b
where snap_id between 42235 and 42259 ), ARCH_USAGE_HR as (
SELECT TO_CHAR(completion_time,'DD-MON-YY HH24') HR, round(sum(BLOCKS * BLOCK_SIZE)/(1048576*1000),0) "ArchGB"
FROM gv$archived_log a , awr_dt
where a.inst_id = a.thread# and a.dest_id = 1 and trunc(completion_time) = awrdt group BY TO_CHAR(completion_time,'DD-MON-YY HH24') )
SELECT db_host, Timeo,ReadGB,WriteGB, "TotIO", "ArchGB",RedoGB,BlkChg,PhyWrt , insrt, upd,del ,sel
FROM awr_iousage_hr a, ARCH_USAGE_HR c, AWR_SYSSTAT_IO d, awr_dml e
where timeo = c.hr(+) and timeo = d.hr and timeo = e.hr order by timeo