O'Reilly logo

Oracle Database 12c Backup and Recovery Survival Guide by Aman Sharma, Francisco Munoz Alvarez

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 column block_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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required