• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » how to find session generating lots of redo in Oracle database

how to find session generating lots of redo in Oracle database

January 1, 2015 by techgoeasy Leave a Comment

To find sessions generating lots of redo in Oracle database , 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.

How to find session generating lots of redo in Oracle database
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.The query you can use is

SELECT s.sid, s.serial#, s.username, s.program,
i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
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.

This will need  to executed on all the nodes of database if it is Oracle RAC

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).

The query you can use is:

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;

Run the query multiple times and examine the delta between each occurrence of USED_UBLK and USED_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.

Related Articles

Local Undo in Oracle Database 12c R2(12.2)

How to recreate redo log files in Oracle database

alter system switch logfile v/s archive log current

What is Undo and redo in Oracle database

Filed Under: Oracle, Oracle Database Tagged With: how to find session generating lots of redo, redo, undo

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • how to find file based on size in linux
  • SQL trace, 10046 event in Oracle
  • How to use sed replace string in file
  • Password Version in oracle
  • How to login as user without changing the password in Oracle database(alter user identified by values)

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us