Find Sessions Generating Lots of Redo or Archive logs 

Find the amount of redo generated by the block changes column high value

It will bring the top most line because of Order by clause on i.Block_changes in descending order.

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;

Find the amount of undo blocks and 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 desc, 6 desc, 1, 2, 3, 4;
This entry was posted in Oracle on by .
Unknown's avatar

About SandeepSingh

Hi, I am working in IT industry with having more than 15 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

Leave a Reply