Scripts: Finding the object or statement that is causing excessive redo generation

How to find the object that is causing excessive redo or archive generation between a given date. You can get from the v$log_history but for history you will need to query the awr history views as below.

SELECT dhso.object_name,
sum(db_block_changes_delta)
FROM dba_hist_seg_stat dhss,
dba_hist_seg_stat_obj dhso,
dba_hist_snapshot dhs
WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhso.obj#
AND dhss.dataobj# = dhso.dataobj#
AND begin_interval_time BETWEEN to_date('2012_05_02 04','YYYY_MM_DD HH24') AND to_date('2012_05_02 07','YYYY_MM_DD HH24')
GROUP BY dhso.object_name
order by sum(db_block_changes_delta) desc
/

OBJECT_NAME                    SUM(DB_BLOCK_CHANGES_DELTA)
------------------------------ ---------------------------
XXXXXXX                                     315744448

Find the statement related to it.

SELECT distinct dbms_lob.substr(sql_text,4000,1)
FROM dba_hist_sqlstat dhss,
dba_hist_snapshot dhs,
dba_hist_sqltext dhst
WHERE upper(dhst.sql_text) LIKE '%XXXXXXXXXX%'
AND dhss.snap_id=dhs.snap_id
AND dhss.instance_Number=dhs.instance_number
AND dhss.sql_id = dhst.sql_id and rownum<2;

DBMS_LOB.SUBSTR(SQL_TEXT,4000,1)
--------------------------------------------------------------------------------
update XXXXXXXX set LATEST_VERSION=:1  where ID=:2  and VERSION<:3

SQL> SQL>

Hope this helps!!!

-Thanks

Geek DBA