There are several methods to find out session/table with most redo generation:
--Using logminer
--Using v$ view by query db_block_change
--Analyze the record on dba_tab_modificatioins/mon_mods_all$/mon_mods$
--Display table which generate most redo/archive logs
Method:
**Calculate DML operation according to dba_tab_modifications
For detail please refer to :
http://blog.itpub.net/28412973/viewspace-2154868/
Methods:
**Calculate by log miner
For detail please refer to:
https://blog.yannickjaquier.com/oracle/logminer-with-oracle-10g-and-above.html
Excessive Redolog Generation Every Day At Midnight (Doc ID 1547846.1)
How to Find Sessions Generating Lots of Redo or Archive logs (Doc ID 167492.1)
**example
--Display session with most rego generation
SELECT
s.sid,
s.username,
s.program,
sn.name,
DECODE(sn.class, 1, 'User', 2, 'Redo', 4, 'Enqueue', 8, 'Cache', 16, 'OS', 32, 'Real Application Clusters', 64, 'SQL', 128, 'Debug', '????') AS "Type",
ROUND(t.VALUE/(1024*1024)) AS "Redo Size MB"
FROM V$SESSION s, V$SESSTAT t, V$STATNAME sn
WHERE s.sid = t.sid
AND t.statistic# = sn.statistic#
AND sn.name = 'redo size'
AND ROUND(t.VALUE/(1024*1024)) != 0
ORDER BY SIGN(t.VALUE), t.VALUE DESC;
SELECT s.username,
s.sid,
s.serial#,
s.program,
o.block_changes,
ROUND ( ( o.block_changes
* (SELECT VALUE
FROM v$parameter
WHERE name = 'db_block_size'))
/ 1024
/ 1024,
2)
redo_mb
FROM v$session s, v$sess_io o
WHERE s.sid = o.sid
ORDER BY block_changes DESC;
--Displa logminer from redo log
--Display reolog file
SELECT DISTINCT MEMBER LOGFILENAME FROM V$LOGFILE;
--Add redo log file
EXEC dbms_logmnr.add_logfile('/u01/app/oracle/oradata/ora11g/redo01.log');
--Start logminer
EXEC dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
--Display logminer contents
SELECT start_scn,
commit_scn,
timestamp,
start_timestamp,
commit_timestamp,
operation,
seg_name,
table_name,
sql_redo,
sql_undo
FROM v$logmnr_contents;
SELECT operation,
seg_owner,
seg_name,
COUNT (*)
FROM v$logmnr_contents
GROUP BY seg_owner, seg_name, operation
ORDER BY COUNT (*) DESC;
--End logminer
EXEC dbms_logmnr.end_logmnr();
Methods:
**Caculate according to block_change
**Large deltas indicate high redo generation by
the session.
--Display high block change on session
SELECT s.username,
s.sid,
s.serial#,
s.program,
o.block_changes,
ROUND ( ( o.block_changes
* (SELECT VALUE
FROM v$parameter
WHERE name = 'db_block_size'))
/ 1024
/ 1024,
2)
redo_mb
FROM v$session s, v$sess_io o
WHERE s.sid = o.sid
ORDER BY block_changes DESC;
--Display the transaction generate most undo
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 t.used_ublk DESC;
--List top 5 db block change on each snapshot
SELECT *
FROM (SELECT SNAP_ID,
O.OBJECT_NAME,
O.OBJECT_TYPE,
DB_BLOCK_CHANGES_DELTA,
ROUND ( ( DB_BLOCK_CHANGES_DELTA
* (SELECT VALUE
FROM v$parameter
WHERE name = 'db_block_size'))
/ 1024
/ 1024,
2)
DB_BLOCK_CHANGE_MB,
DB_BLOCK_CHANGES_TOTAL,
RANK ()
OVER (PARTITION BY SNAP_ID
ORDER BY DB_BLOCK_CHANGES_DELTA DESC)
DB_BLOCK_CHANGE_RANK
FROM DBA_HIST_SEG_STAT S, DBA_HIST_SEG_STAT_OBJ O
WHERE S.DBID = O.DBID AND S.OBJ# = O.OBJ#)
WHERE DB_BLOCK_CHANGE_RANK <= 5;