Re: Commit -> write to data file immediately???

Norman Dunbar <ndunbar_at_lynxfinancialsystems.co.uk> wrote:
>David,
>
>when the user commits, Oracle guarantees to write the REDO information
>to the redo logfile. When this has been successful, the user gets the 'n
>records committed' message. The changed data is still held in the buffer
>cache but has not yet been written to disc.
>
>Sounds bad, but because the redo log holds the details of how to rebuild
>that entire transaction, then it can be rolled forward in the event of a
>database failure with subsequent recovery. (ARCHIVE LOG mode required of
>course (unless the whole transaction details are still in the on-line
>redo logs)).
>
>The data in the buffer cache is written to the actual datafiles at
>certain times when a checkpoint occurs. This is caused by a redo log
>switch, a timeout (every 3 seconds), when the redo log bugger becomes
>one third full etc. ~~~~~~

I agree with your sentiment about the redo log :-)

Anyway, the most important misunderstanding above is the 3 second timeout. DBWR will wake up every 3 seconds regardless of anything else, but won't necessarily write anything at that point.

I don't know all the conditions that will cause a physical write. In 8i they include certainly a log-switch checkpoint, more than db_block_max_dirty_target dirty buffers, or if there are "too many" dirty buffers by other criteria. (I think, but don't quote me, that by default DBWR will try to keep at least 2/3 of buffers on a given LRU list clean).

-- 
Andrew Mobbs - http://www.chiark.greenend.org.uk/~andrewm/