How to find Sessions generating high redo/archives in Oracle

oracle-logoSometimes, database generates lot more archives than the normal. In this article I will show you how to find them. Below are the queries to drill down to the root cause and fix the issue.

If there is a drastic change in redo/archive generation run below queries.. Get the segment that experienced the most changes during a specific period. This is helpful in tracking history data.

Note:- Change the date ranges according to the time frame.

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.

The methods are:

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:

Run the query multiple times and examine the delta between each occurrence of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.

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:

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.

The following two tabs change content below.

Anil Panda

Lead DBA
Anil is working as a DBA Lead in a reputated MNC. He loves to watch Cricket and WWE in his leisure time. He is always busy with helping others. Lastly he is a great fan of Mr. Sachin Tendulkar (Cricketer).

One thought on “How to find Sessions generating high redo/archives in Oracle

  1. Good article .It really worked in our case to trace out the candidate object and programs responsible for heavy archive generation.

    Once we know the segment with heavy block changes we can get the SQL information related to those objects:

    We can use below query to trace the sql statement causing heavy block changes on identified segment . Provide the time duration of heavy archive generation and identified segment as per article in filter condition as %segmentname%’ given below.

    SELECT to_char(begin_interval_time,’YYYY_MM_DD HH24:MI’),
    dbms_lob.substr(sql_text,4000,1),
    dhss.instance_number,
    dhss.sql_id,executions_delta,rows_processed_delta
    FROM dba_hist_sqlstat dhss,
    dba_hist_snapshot dhs,
    dba_hist_sqltext dhst
    WHERE upper(dhst.sql_text) LIKE ‘%segmentname%’
    AND dhss.snap_id=dhs.snap_id
    AND dhss.instance_Number=dhs.instance_number
    AND begin_interval_time BETWEEN to_date(‘2013_06_22 17′,’YYYY_MM_DD HH24’)
    AND to_date(‘2013_07_22 21′,’YYYY_MM_DD HH24’)
    AND dhss.sql_id = dhst.sql_id;

    Once queries are traced we can fix or reduce the archive generation by possible query optimization.

    Regards
    Dinesh Patil.

Leave a Reply