oracle lost write detection机制导致select产生大量redo

某客户执行一个select全表扫，产生大量redo，需要分析原因。

一般延迟块清除导致select产生redo，但这个案例中并不是延迟块清除的原因。

分析过程

1、在SELECT执行前保存该session的v$sesstat数据

select
    ses.sid,
    sn.name,
    ses.value
from
    v$sesstat ses,
    v$statname sn
where
    sn.statistic# = ses.statistic#
and ses.sid in (2657)
and value>0;

2、执行产生REDO的SELECT
可以看到

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     864313  consistent gets
     805988  physical reads
 23,439,672  redo size  <<<<
        535  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

3、在SELECT执行后保存该session的v$sesstat数据
4、对比sesstats前后产生的变更，增量
发现
SELECT执行前

redo size 23449636
redo size for lost write detection 22281124

SELECT执行后

redo size 46879924
redo size for lost write detection 45355776

可以看到REDO的产生主要为redo size for lost write detection

我们在MOS上找到如下文档
Large Redo Size Generated When Set Autotrace On for Query (Doc ID 2431697.1)
检查DB_LOST_WRITE_PROTECT参数为TYPICAL，而不是默认值NONE。该参数为动态参数，可以在线改。

From 11.1 onwards, if DB_LOST_WRITE_PROTECT is set to non-default value of TYPICAL or FULL, lost write detection will be enabled Then buffer cache reads are logged which leads to generation of redo for selects. This is necessary for lost write detection. This is expected behavior. For further information of lost write detection related functionality, please refer to following Note and online documentation.
Best Practices for Corruption Detection, Prevention, and Automatic Repair - in a Data Guard Configuration Document 1302539.1

Database Reference
DB_LOST_WRITE_PROTECT
https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams058.htm#REFRN10268

从11.1开始，如果将DB_LOST_WRITE_PROTECT设置为TYPICAL或FULL的非默认值，则会启用丢失写检测功能，然后记录缓冲区高速缓存读取，这会导致针对selects生成重做。 这对于丢失写检测是必需的。 这是预期的行为。