实战经验：Oracle Lost Write Detection机制导致Select产生大量Redo

墨墨导读：某客户执行一个Select全表扫，产生大量Redo，需要分析原因。一般延迟块清除导致Select产生Redo，但这个案例中并不是延迟块清除的原因。

分析过程

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

select ses.sid, sn.name, ses.valuefrom v$sesstat ses, v$statname snwhere 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 23449636redo size for lost write detection 22281124

SELECT执行后

redo size 46879924redo 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生成重做。这对于写丢失检测是必需的。这是预期的行为。

作者

范计杰，云和恩墨技术顾问，5年大型ORACLE数据库维护经验，擅长性能调优、故障处理等。

墨天轮原文链接：https://www.modb.pro/db/45357（复制到浏览器中打开或者点击“阅读原文”立即查看）