Avg REDO Generation

Put these in one file.sql and run.
----------------------------------

set linesize 200 verify off pages 100
accept days char prompt 'How many days(Default - 1 day) :'
select to_char(bit,'DD-MON-YYYY') DAY,round(sum(avgredoinmb)/1024,2) "Avg Redo in GB"
from
(
select
        /*+parallel(a,8) parallel(b,8)*/
        b.begin_interval_time bit,
        b.instance_number inst,
        (AVERAGE*60*(ROUND( TO_NUMBER ( CAST(b.end_interval_time as DATE) - CAST(b.begin_interval_time as DATE)) * 24 * 60 )))/1024/1024 AvgRedoInMB
from
        DBA_HIST_SYSMETRIC_SUMMARY a,
        dba_hist_snapshot b
where
        a.snap_id=b.snap_id
        and
        b.instance_number=a.instance_number
        and
        a.metric_name='Redo Generated Per Sec'
        and
        trunc(b.begin_interval_time)>sysdate-nvl('&days',1)
)
group by to_char(bit,'DD-MON-YYYY')
ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');
exit;



AND



If there is a drastic change in redo/archive generation run below queries.
===========================================================================


Get the segment name / object name that experienced the most changes during a specific period. This is helpful in tracking history data.



SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI') snap_time,
        dhsso.object_name,
        sum(db_block_changes_delta)
  FROM dba_hist_seg_stat dhss,
         dba_hist_seg_stat_obj dhsso,
         dba_hist_snapshot dhs
  WHERE dhs.snap_id = dhss.snap_id
    AND dhs.instance_number = dhss.instance_number
    AND dhss.obj# = dhsso.obj#
    AND dhss.dataobj# = dhsso.dataobj#
    AND begin_interval_time BETWEEN to_date('2013_10_22 12','YYYY_MM_DD HH24')
                                           AND to_date('2013_10_23 12','YYYY_MM_DD HH24')
  GROUP BY to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
           dhsso.object_name order by 3 desc;





Redo generated by current user sessions:
===========================================

select v$session.sid, username, value redo_size
from v$sesstat, v$statname, v$session
where v$sesstat.STATISTIC# = v$statname.STATISTIC#
and v$session.sid = v$sesstat.sid
and name = 'redo size'
and value > 0
and username is not null
order by value
/


Redo generated during my session since the session started:
==============================================================
select value redo_size
from v$mystat, v$statname
where v$mystat.STATISTIC# = v$statname.STATISTIC#
and name = 'redo size'
/



Which Session doing high Block Changes:
========================================
SELECT s.sid, s.serial#, s.username, s.program,
i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
ORDER BY 5 desc, 1, 2, 3, 4;
/




This view contains information about the amount of undo blocks and undo records accessed by the transaction (as found in the USED_UBLK and USED_UREC columns).
============================================================================================================

SELECT s.sid, s.serial#, s.username, s.program,
t.used_ublk, t.used_urec
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr
ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;