概述

我们都知道事务会产生redo日志，那么当redo日志突然急剧增加的时候，怎么去追踪产生大量redo的来源呢？

下面在官方文档基础下做一下介绍。

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

两个方法

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、查询 $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.

通过观察block_changes的值来判断哪个会话产生大量redo。

SQL> 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;

2、查询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).

通过观察USED_UBLK and USED_UREC的值来判断哪个会话产生大量redo。

SQL> 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 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.

查找引起redo日志剧增的原因

这里主要是用logminer来分析redo日志。

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 SCOTTDELETE SCOTT SCOTTINSERT SCOTT SCOTTUPDATE 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';

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';

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;

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;

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注一下~