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 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 just give 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 involed 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 hostorical 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 this insert during that same period. Now I know which SQL was actually generating a lot of redo log.
