Internals of Oracle redo log management Oracle Tips by Burleson Consulting

 

Also, see these important notes on redo log buffer sizing.

I recently had a DBA with an interesting question about why there appears to be redo log images generated after an SQL ?select? statement.  Normally, we are taught that redo is only for DML changes (insert, update, delete), but the explanations for this may be surprising. 

• Queuing up messages and emailing them

• Internal system-level redo

The most important of the system-level redo is the "delayed block cleanout" mechanism that will generate redo on a select statement, and it is visible through the statistics

 

• "cleanouts only - consistent read gets"

• "cleanouts and rollbacks - consistent read gets"

 

Redo with a select statement happens when dirty blocks get written to the database, and are then "cleaned up" when next read of that data block. This could happen when a large DML statement does not commit before the DB Writer needs to write modified blocks to disk.  The next time the blocks are read by a select statement they get modified, hence REDO is generated by the select statement.

 

Special thanks to Oracle guru David Aldridge for this Oracle tip!