如何追蹤產生大量REDO的來源

SQL: How to Find Sessions Generating Lots of Redo or Archive logs (文件 ID 167492.1)


***Checked for relevance on 13-Oct-2015***

goal: How to find sessions generating lots of redo
fact: Oracle Server - Enterprise Edition 8
fact: Oracle Server - Enterprise Edition 9
fact: Oracle Server - Enterprise Edition 10
fix:


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:
       SQL> SELECT s.sid, s.serial#, s.username, s.program,
         2  i.block_changes
         3  FROM v$session s, v$sess_io i
         4  WHERE s.sid = i.sid
         5  ORDER BY 5 desc, 1, 2, 3, 4;


   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:
      SQL> SELECT s.sid, s.serial#, s.username, s.program, 
        2  t.used_ublk, t.used_urec
        3  FROM v$session s, v$transaction t
        4  WHERE s.taddr = t.addr
        5  ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;


   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.




How To Determine The Cause Of Lots Of Redo Generation Using LogMiner (文件 ID 300395.1)


APPLIES TO:
Oracle Database - Enterprise Edition - Version 8.1.7.4 to 10.2.0.5 [Release 8.1.7 to 10.2]
Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Information in this document applies to any platform.
GOAL
This article provides guidelines DBAs can use to determine which OPERATION codes are generating lots of redo information.


This article is intended for DBAs. The article assumes the reader is familiar with LogMiner and has basic skills in mining redo logs.


SOLUTION
--- How to determine the cause of lots of redo generation using LogMiner ---


Using OPERATION Codes to Understand Redo Information


There are multiple operation codes which can generate the redo information, using following guide lines you can identify the operation codes which are causing the high redo generation and you need to take an appropriate action on it to reduce the high redo generation.


NOTE:
Redo records are not all equally sized. So remember that just because certain statements show up a lot in the LogMiner output, this does not guarantee that you have found the area of functionality generating the excessive redo.


What are these OPERATION codes ?


INSERT / UPDATE / DELETE -- Operations are performed on SYS objects are also considered as an Internal Operations.
COMMIT -- This is also "Internal" operation, you will get line "commit;" in the column sql_redo.
START -- This is also "Internal" operation, you will get line "set transaction read write;" in sql_redo INTERNAL -- Dictionary updates
SELECT_FOR_UPDATE - This is also an Internal operation and oracle generates the redo information for "select" statements which has "for update" clause.
In general INTERNAL operations are not relevant, so to query the relevant data, use "seg_owner=' in the "where" clause.


Examples :


How to extract relevant information from the view v$logmnr_contents?




1. This SQL lists operations performed by user SCOTT


SQL> select distinct operation,username,seg_owner from v$logmnr_contents where seg_owner='SCOTT';


OPERATION USERNAME SEG_OWNER
-------------------------- ------------------------- ---------------------
DDL SCOTT SCOTT
DELETE SCOTT SCOTT
INSERT SCOTT SCOTT
UPDATE SCOTT SCOTT






2. This SQL lists the undo and redo associated with operations that user SCOTT performed


SQL> select seg_owner,operation,sql_redo,sql_undo from v$logmnr_contents where SEG_owner='SCOTT';


SCOTT DDL
create table LM1 (c1 number, c2 varchar2(10));


SCOTT INSERT
insert into "SCOTT"."LM1"("C1","C2") values ('101','AAAA');
delete from "SCOTT"."LM1" where "C1" = '101' and "C2" = 'AAAA' 
and ROWID = 'AAAHfBAABAAAMUqAAA';


SCOTT UPDATE update "SCOTT"."LM1" set "C2" = 'YYY' 
where "C2" = 'EEE' and ROWID = 'AAAHfBAABAAAMUqAAE';
update "SCOTT"."LM1" set "C2" = 'EEE' where "C2" = 'YYY' 
and ROWID = 'AAAHfBAABAAAMUqAAE';


INSERT / UPDATE / DELETE -- Operations are performed on SYS objects are also considered as an Internal Operations.




3. This SQL lists undo and redo genereated for UPDATE statements issues by user SCOTT


SQL> select username, seg_owner,operation,sql_redo,sql_undo from v$logmnr_contents where operation ='UPDATE' and USERNAME='SCOTT';


UNAME SEG_OW OPERATION SQL_REDO SQL_UNDO
---------- ---------- ------------ -----------------------------------
SCOTT SYS UPDATE update "SYS"."OBJ$" set "OBJ#" = '1'..... update ....
SCOTT SYS UPDATE update "SYS"."TSQ$" set "GRANTO..... update .......
SCOTT SYS UPDATE update "SYS"."SEG$" set "TYPE#" = '5'.. update......


As per above result user SCOTT has updated SYS objects so, if you query on USERNAME, you may get incorrect result. So, better to query v$logmnr_contents on SEG_OWNER.




4. Identifying Operation Counts


Run the following query to see the OPERATION code row count from v$logmnr_contents, to understand which OPERATION code has generated lots of redo information.


SQL> select operation,count(*) from v$logmnr_contents group by operation;


OPERATION COUNT(*)
-------------------- ----------
COMMIT 22236
DDL 2
DELETE 1
INSERT 11
INTERNAL 11
SELECT_FOR_UPDATE 32487
START 22236
UPDATE 480


8 rows selected






5. Identifying User Counts


Run the following query to check user activity and operation counts:


SQL> select seg_owner,operation,count(*) from v$logmnr_contents group by seg_owner,operation;


SEG_OWNER OPERATION COUNT(*)
-------------------- ---------------- ---------
SCOTT COMMIT  22236
SCOTT DDL 2
SCOTT DELETE 1
...
BILLY COMMIT 12899
BILLY DDL 5
BILLY DELETE 2
...
 


NOTE:  
Be aware of next known issue:


If you are not using "select for update" statements often in your application and yet find a high operation count for operation code "SELECT_FOR_UPDATE" then you might be hitting a known issue.


To confirm this check whether SQL_REDO shows select,update statements on AQ$_QUEUE_TABLE_AFFINITIES and AQ$_QUEUE_TABLES.


If you see these selects and updates, then check the value of the Init.ora parameter AQ_TM_PROCESSES.  The default value is AQ_TM_PROCESSES = 0 meaning that the queue monitor is not created.


If you are not using Advanced Queuing, then set AQ_TM_PROCESSES back to zero to avoid lots of redo generation on objects AQ$_QUEUE_TABLE_AFFINITIES and AQ$_QUEUE_TABLES.