Display table with most redo/archive

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;

请使用浏览器的分享功能分享到微信等