Re: URGENT! Writes to Disk while Doing a SELECT statement?

On Wed, 3 Feb 1999, Jonathan Lewis wrote:
>
> The version is 7.3.4 so the default clean-out mechanism means that
> a select statement should not cause cleaned blocks to be written.
> (delayed_logging_block_cleanouts = TRUE).

No, the deferred block cleanout mechanism still applies in 7.3.4 and after. Writing BLOCKS for cleanout is different from LOGGING cleanouts. In 7.3.4 and after, cleanonuts from selects are not logged to redo *but they are performed*. This decision was made with the presumption that the number of cleanouts that one would miss out on during a recovery is insignificant, and that those few cleanouts could easily be done by normal users selecting and updating rows once the database is recovered and opened.

What Oracle neglected to consider was the implication for hot standby databases in this scenario. If you fail over to a standby database that has been recovering for weeks, you are bound to have a *huge* number of blocks to clean out, making almost any select against that newly failed over database incur a large write cost, and making the dirty list very deep.

Cleanouts keep happening with selects, they just don't get logged. That is to say, if you perform recovery, any cleanouts that happened in the period of logs you apply won't make it into the recovered database.

Remember to differentiate from *logging cleanouts* and *cleaning out a committed block*.

--
Jeremiah Wilton http://www.wolfenet.com/~jeremiah Received on Wed Feb 03 1999 - 15:44:50 CST