A Peek into the SQL with most Redo Log Generation

I recently came across a situation in my stage database where a new third party application was getting deployed. The issue was with the amount of redo log generated in a day. The whole database was just 40G and it was generating about 300G of redo log per day. This made me to think more and I did some analysis to find out what SQLs are the reason for this high redo log.

To find sessions generating lots of redo, you can use either of the following methods. Both methods examine the amount of undo generated. When a transaction generates undo, it will automatically generate redo as well.
We can query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates how much blocks have been changed by the session. High values indicate a session generating lots of redo.

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;

We need to run the query multiple times and examine the delta between each occurrence of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.

This gives me an idea about what is currently happening. But I want more historical information. Let us say for last 3 days, what was the SQLs involved in most redo.
I did generate an AWR for last two days but that did not gave me the information that I was looking for. So I decided to directly query DBA_HIST views.

I found a way to get the historical db block change information from DBA_HIST views. dba_hist_seg_stat view has a column called db_block_changes_delta column. We can query this view and find out the sum of block changes per object.

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(‘2011_05_25 08′,’YYYY_MM_DD HH24’) AND to_date(‘2011_05_27 00′,’YYYY_MM_DD HH24’)
GROUP BY dhso.object_name
order by sum(db_block_changes_delta) desc
/

WINDOW_EVENTS 809200
WINDOWS 153968
HISTGRM$ 65040
I_H_OBJ#_COL# 61168
MODEL_REPORT 31840
DATA_PROCESS 16784
RULE_EXECUTE_INFO 12448
PK_MODEL_REPR 9088

Above query gives the sum(db_block_changes_delta). Now we can use dba_hist_sqlstat view to get the SQL statements. So I did run another query to see what SQLs are run on WINDOW_EVENTS or WINDOW during same period.

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 ‘%WINDOW%’
AND dhss.snap_id=dhs.snap_id
AND dhss.instance_Number=dhs.instance_number
AND dhss.sql_id = dhst.sql_id and rownum<2; INSERT INTO WINDOWS ( EVENT_ID, DATA_PROCESS_ID, EVENT_TIME, DENOMINATOR_STR, TIME_WINDOW_ID, TIME_WINDOW_PANE, OWNER_ORG_ID ) INSERT INTO WINDOW_EVENTS ( EVENT_ID , DATA_PROCESS_ID , EVENT_TIME , EVENT_EPOCH_TIME , OWNER_ORG_ID , DENOMINATOR_STR )

This is just the part of the output. There was a lot of this insert during that same period. This is just simple way to figure out the SQLs that generated a lot of redo in the past.