redo NOLOGGING 生成量对比

Purpose

To help with diagnosing excessive redo generation.

First, some background on redo generation:

What is the Online Redo Log?
The most crucial structure for recovery operations is the online redo log,
which consists of two or more pre-allocated files that store all changes made
to the database as they occur. Every instance of an Oracle database has an
associated online redo log to protect the database in case of an instance
failure.

What are the contents of the Online Redo Log?
Online redo log files are filled with redo records. A redo record, also called
a redo entry, is made up of a group of change vectors, each of which is a
description of a change made to a single block in the database. For example,
if you change a salary value in an employee table, you generate a redo record
containing change vectors that describe changes to the data segment block for
the table, the rollback segment data block, and the transaction table of the
rollback segments.

Redo entries record data that you can use to reconstruct all changes made to
the database, including the rollback segments. Therefore, the online redo log
also protects rollback data. When you recover the database using redo data,
Oracle reads the change vectors in the redo records and applies the changes
to the relevant blocks.

Troubleshooting Steps

Scope & Application

You experience a large amount of redo being generated while performing a DML
even if "NOLOGGING" is used.

For assistance with determining what session is causing excessive redo, please see
Note:167492.1 titled "How to Find Sessions Generating Lots of Redo".

How to diagnose excessive redo generation.

Check the following:

1. Confirm if the table or index has "NOLOGGING" set.---ADG下没有效果

Issue the following statement.

select table_name,logging from all_tables where table_name = <table name>;
-or-
select table_name,logging from all_indexes where index_name = <index name>;

2. Table has no triggers that might cause some indirect DML on other tables.

3. Auditing is not the contributor for this excessive redo generation.

4. The tablespace is not in hot backup mode.

5. Note that only the following operations can make use of NOLOGGING mode:

- direct load (SQL*Loader)
- direct-load INSERT
- CREATE TABLE ... AS SELECT
- CREATE INDEX
- ALTER TABLE ... MOVE PARTITION
- ALTER TABLE ... SPLIT PARTITION
- ALTER INDEX ... SPLIT PARTITION
- ALTER INDEX ... REBUILD
- ALTER INDEX ... REBUILD PARTITION
- INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode stored out of line

Consider the following illustration.

Both tables below have "nologging" set at table level.

SQL> desc redo1
Name Null? Type
----------------------------------------- -------- ----------
X NUMBER
Y NUMBER

SQL> desc redotesttab
Name Null? Type
----------------------------------------- -------- -------
X NUMBER
Y NUMBER

begin
for x in 1..10000 loop
insert into scott.redotesttab values(x,x+1);
-- or
-- insert /*+ APPEND */ into scott.redotesttab values(x,x+1);
end loop;
end;

Note: This will generate redo even if you provide the hint because this
is not a direct-load insert.

Now, consider the following bulk inserts, direct and simple.

SQL> select name,value from v$sysstat where name like '%redo size%';

NAME VALUE
----------------------------------------------------------- ----------
redo size 27556720

SQL> insert into scott.redo1 select * from scott.redotesttab;
50000 rows created.

SQL> select name,value from v$sysstat where name like '%redo size%';

NAME VALUE
----------------------------------------------------------- ----------
redo size 28536820

SQL> insert /*+ APPEND */ into scott.redo1 select * from scott.redotesttab;
50000 rows created.

SQL> select name,value from v$sysstat where name like '%redo size%';

NAME VALUE
----------------------------------------------------------- ----------
redo size 28539944

You will notice that the redo generated via the simple insert is "980100" while
a direct insert generates only "3124".

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.