上QQ阅读APP看书，第一时间看更新
Finding sessions that generate lots of redo
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.
- Query
v$sess_io: This view contains the columnblock_changes, which indicates how many blocks have been changed by the session. High values indicate a session generating lots of redo. - The query you can use is:
SQL> SELECT s.sid, s.serial#, s.username, s.program, i.block_changes 2 FROM v$session s, v$sess_io i 3 WHERE s.sid = i.sid 4 ORDER BY 5 desc, 1, 2, 3, 4;
- Run the query multiple times and examine the delta between each occurrence of
block_changes. Large deltas indicate high redo generation by the session. - Query
v$transaction: This view contains information about the amount of undo blocks and undo records accessed by the transaction (as found in theused_ublkandused_ureccolumns). - The query you can use is:
SQL> SELECT s.sid, s.serial#, s.username, s.program, t.used_ublk, t.used_urec 2 FROM v$session s, v$transaction t 3 WHERE s.taddr = t.addr 4 ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;
- Run the query multiple times and examine the delta between each occurrence of
used_ublkandused_urec. Large deltas indicate high redo generation by the session.
You use the first query when you need to check for programs generating lots of redo when these programs activate more than one transaction. The latter query can be used to find out which particular transactions are generating redo.