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.
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.
FROM dba_hist_seg_stat dhss,
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
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,
WHERE upper(dhst.sql_text) LIKE ‘%WINDOW%’
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.