How to Find Sessions Generating Lots of Redo or Archive logs (Doc ID 167492.1)

How to Find Sessions Generating Lots of Redo or Archive logs (Doc ID 167492.1) 

I can help you rewrite your content in a more SEO-friendly way for a blog post. Here is a possible rewrite:

---

If you want to optimize the performance of your Oracle database, you need to monitor the sessions that generate a lot of redo. Redo is the process of logging changes made to the database, which helps to ensure data integrity and recovery. However, too much redo can slow down the database and consume disk space.

To identify the sessions that generate a lot of redo, you can use two methods that are based on the amount of undo generated. Undo is the opposite of redo, which records the original state of the data before the changes. When a transaction generates undo, it also generates redo automatically.

The two methods are:

• Method 1: Use the V$SESS_IO view. This view shows the I/O statistics for each session, including the column BLOCK_CHANGES, which indicates how many blocks have been changed by the session. High values of this column mean that the session is generating a lot of redo.

The query you can use to access this view 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;

You need to run this query multiple times and compare the difference between each occurrence of BLOCK_CHANGES. Large differences mean that the session is generating a lot of redo in a short time.

• Method 2: Use the V$TRANSACTION view. This view shows the information about the transactions in progress, including the columns USED_UBLK and USED_UREC, which indicate the number of undo blocks and undo records accessed by the transaction. High values of these columns mean that the transaction is generating a lot of redo.

The query you can use to access this view 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;

You need to run this query multiple times and compare the difference between each occurrence of USED_UBLK and USED_UREC. Large differences mean that the transaction is generating a lot of redo in a short time.

You can use the first method when you want to check the programs that generate a lot of redo across multiple transactions. You can use the second method when you want to check the specific transactions that generate a lot of redo.

If you want to get more information about the sessions and transactions, you can use the following query, which joins the V$SESSION, V$TRANSACTION, and V$SQL views:

SELECT s.sid, s.serial#, s.username, s.program, s.SQL_ID, SCHEMANAME, OSUSER, PREV_SQL_ID, START_TIME, l.executions, l.FIRST_LOAD_TIME, l.LAST_LOAD_TIME, l.LAST_ACTIVE_TIME, l.sql_text, t.used_ublk, t.used_urec
FROM v$session s, v$transaction t, v$sql l
WHERE s.taddr = t.addr 
AND s.sql_id = l.sql_id
ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;

This query will show you the SQL ID, schema name, OS user, previous SQL ID, start time, number of executions, first load time, last load time, last active time, and SQL text for each session and transaction, along with the undo and redo statistics.

---