How to Find Sessions Generating Lots of Redo or Archive logs

假如需要我們排查哪個會話產生大量redo的時候，通常有以下兩種做法。由於一個事物產生undo的時候同時也產生redo因此該種做法也適合排查undo的使用情況。
The methods are:
1) 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.
SQL> SELECT s.sid, s.serial#,
                    s.username, s.program,
                    i.block_changes 
           FROM v$session s, v$sess_io i 
          WHERE s.sid = i.sidORDER BY 5 desc, 1, 2, 3, 4;

注：該種方法需要執行多次，透過對block_changes的值進行前後對比，來判斷哪個會話產生大量的redo.

2)Query V$TRANSACTION. This view contains information about the amount of undo blocks and undo records accessed by the transaction (as found in the USED_UBLK and USED_UREC columns).
SQL> 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 5 desc, 6 desc, 1, 2, 3, 4;

注：透過多次執行該語句，觀察每次執行時的used_ublk,used_urec的詳細情況，可以從中發現哪個session產生大量的redo和undo.
當你第一次分析一個程式（多個事物）佔用多大的redo或undo時，這樣的情況第二種方式較適合。