Which Sessions Generating Lots of Redo logs in oracle

Which Sessions Generating Lots of Redo logs in oracle?

Last week I got a critical mail alert from one of my production database “archive log destination file system reached 98%”.

Every 3 hours, we took the archive logs backup using RMAN script. I checked the backup log & last ran was successful. The RMAN jobs are running fine & we decided lot of DML operations is running on database. That’s only we getting lot of archive log files.

If file system reached 100%, Database will be hang & db connections will also fail;
To avoid this issue, manually I ran the RMAN archive log backup script & changed the archive log location to new file system.

I am investigating which session/transaction generating more redo logs?

I found one good article in www.oracle.com

Using below query, we can easily identify which session/transaction generating more redo logs.

Query 1:

V$Sess_io & V$Session

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;

BLOCK_CHANGES column shows how much blocks have been changed the session.

Query 2:

Below query shows the amount of undo blocks & undo records accessed by the transaction.

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,6 desc;

Note: Actually one of the batch cycle processing 2 million records. That day it was processed around 10 million records. Due to this we getting more number of redo logs. I informed to application team & they agreed “to notify our team, feed files more than 3 million records”.

I Hope this article helped to you. I am expecting your suggestions/feedback.
It will help to motivate me to write more articles.

Best Regards
Sunil Nanabala