Identify the redo size increase:

Identify the redo size increase:




col begin_interval_time format a30
set lines 160 pages 1000
col end_interval_time format a30
set colsep '|'
alter session set nls_date_format='DD-MON-YYYY';
with redo_sz as (
SELECT  sysst.snap_id, sysst.instance_number, begin_interval_time ,end_interval_time ,  startup_time,
VALUE - lag (VALUE) OVER ( PARTITION BY  startup_time, sysst.instance_number
                ORDER BY begin_interval_time, startup_time, sysst.instance_number) stat_value,
EXTRACT (DAY    FROM (end_interval_time-begin_interval_time))*24*60*60+
            EXTRACT (HOUR   FROM (end_interval_time-begin_interval_time))*60*60+
            EXTRACT (MINUTE FROM (end_interval_time-begin_interval_time))*60+
            EXTRACT (SECOND FROM (end_interval_time-begin_interval_time)) DELTA
  FROM sys.wrh$_sysstat sysst , DBA_HIST_SNAPSHOT snaps
WHERE (sysst.dbid, sysst.stat_id) IN ( SELECT dbid, stat_id FROM sys.wrh$_stat_name WHERE  stat_name='redo size' )
AND snaps.snap_id = sysst.snap_id
AND snaps.dbid =sysst.dbid
AND sysst.instance_number=snaps.instance_number
and begin_interval_time > sysdate-10
)
select instance_number,
  to_date(to_char(begin_interval_time,'DD-MON-YYYY'),'DD-MON-YYYY') dt
, sum(stat_value)/1024/1024  MB_redo,sum(stat_value)/1024/1024/1024  GB_redo
from redo_sz
group by  instance_number,
  to_date(to_char(begin_interval_time,'DD-MON-YYYY'),'DD-MON-YYYY')
order by instance_number, 2
/





INSTANCE_NUMBER|DT         |   MB_REDO|   GB_REDO
---------------|-----------|----------|----------
              1|10-AUG-2013| 2.3134079|.002259187
              1|11-AUG-2013|250.177128|.244313601
              1|12-AUG-2013|319.943378|.312444706
              1|13-AUG-2013|833.486774|.813951928
              1|14-AUG-2013|6408.85134|6.25864389
              1|15-AUG-2013|604.410999|.590245117
              1|16-AUG-2013|286.627712|.279909875
              1|17-AUG-2013|369.365307|.360708307
              1|18-AUG-2013|237.620087|.232050866
              1|19-AUG-2013| 253.68679|.247741006
              1|20-AUG-2013|253.030739|.247100331

11 rows selected.

SQL>