实战经验：Oracle Lost Write Detection机制导致Select产生大量Redo
墨墨导读：某客户执行一个Select全表扫，产生大量Redo，需要分析原因。一般延迟块清除导致Select产生Redo，但这个案例中并不是延迟块清除的原因。
分析过程
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;
可以看到
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 processed4、对比sesstats前后产生的变更，增量
SELECT执行前
redo size 23449636redo size for lost write detection 22281124redo size 46879924redo size for lost write detection 45355776Large Redo Size Generated When Set Autotrace On for Query (Doc ID 2431697.1)
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.1Database Reference
DB_LOST_WRITE_PROTECT
https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams058.htm#REFRN10268
范计杰，云和恩墨技术顾问，5年大型ORACLE数据库维护经验，擅长性能调优、故障处理等。
赞 (0)