Troubleshooting High Redo Generation Issues (Doc ID 782935.1)

Troubleshooting High Redo Generation Issues (Doc ID 782935.1)

To Bottom

---

In this Document

Goal

Solution

References

---

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 02-Jul-2016***

This technique can be used for all versions irrespective of platform.

GOAL

Purpose of this document is to have a checklist for troubleshooting the high redo generation issues.

SOLUTION

High redo generation can be of two types:

1. During a specific duration of the day.

2. Sudden increase in the archive logs observed.

In both the cases, first thing to be checked is about any modifications done either at the database level (modifying any parameters, any maintenance operations performed,..) and application level (deployment of new application, modification in the code, increase in the users,..).

To know the exact reason for the high redo, we need information about the redo activity and the details of the load. Following information need to be collected for the duration of high redo generation.


1] To know the trend of log switches below queries can be used.

SQL> alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select trunc(first_time, 'HH') , count(*)
  2  from   v$loghist
  3  group by trunc(first_time, 'HH') 
  4  order by trunc(first_time, 'HH');


TRUNC(FIRST_TIME,'HH   COUNT(*)
-------------------- ----------
25-MAY-2008 20:00:00          1
26-MAY-2008 12:00:00          1
26-MAY-2008 13:00:00          1
27-MAY-2008 15:00:00          2
28-MAY-2008 12:00:00          1 <- Indicate 1 log switch from 12PM to 1PM.
28-MAY-2008 18:00:00          1
29-MAY-2008 11:00:00         39
29-MAY-2008 12:00:00        135
29-MAY-2008 13:00:00        126
29-MAY-2008 14:00:00        135 <- Indicate 135 log switches from 2-3 PM.
29-MAY-2008 15:00:00        112

 
We can also get the information about the log switches from alert log (by looking at the messages 'Thread 1 advanced to log sequence' and counting them for the duration) or using an AWR report.


1] If you are in 10g or higher version and have license for AWR, then you can collect AWR report for the problematic time else go for statspack report.
 
a) AWR Report

-- Create an AWR snapshot when you are able to reproduce the issue:
SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
  
-- After 30 minutes, create a new snapshot:
SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (); 

-- Now run 
$ORACLE_HOME/rdbms/admin/awrrpt.sql

 
b) Statspack Report

SQL> connect perfstat/<Password>
SQL> execute statspack.snap;

-- After 30 minutes
SQL> execute statspack.snap;
SQL> @?/rdbms/admin/spreport

 
In the AWR/Statspack report look out for queries with highest gets/execution. You can check in the "load profile" section for "Redo size" and compare it with non-problematic duration.


2] We need to mine the archivelogs generated during the time frame of high redo generation.

-- Use the DBMS_LOGMNR.ADD_LOGFILE procedure to create the list of logs to be analyzed: 
    
SQL> execute DBMS_LOGMNR.ADD_LOGFILE('<filename>',options => dbms_logmnr.new);
SQL> execute DBMS_LOGMNR.ADD_LOGFILE('<file_name>',options => dbms_logmnr.addfile); 

-- Start the logminer

SQL> execute DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);    
   
SQL> select operation,seg_owner,seg_name,count(*)  from v$logmnr_contents group by seg_owner,seg_name,operation;

Please refer to below article if there is any problem in using logminer.
Note 62508.1 - The LogMiner Utility

We can not get the Redo Size using Logminer but we can only get user,operation and schema responsible for high redo.


3] Run below query to know the session generating high redo at any specific time.

col program for a10
col username for a10
select to_char(sysdate,'hh24:mi'), username, program , a.sid, a.serial#, b.name, c.value
from v$session a, v$statname b, v$sesstat c
where b.STATISTIC# =c.STATISTIC#
and c.sid=a.sid and b.name like 'redo%'
order by value;

This will give us all the statistics related to redo. We should be more interested in knowing "redo size" (Total amount of redo generated in bytes)
 
This will give us SID for problematic session.

In above query output look out for statistics against which high value appears and this statistics will give fair idea about problem.

REFERENCES

NOTE:1507157.1 - Master Note: Troubleshooting Redo Logs and Archiving