1. DBWR writes dirty buffers from the data buffer cache to data files
2. LGWR writes the redo log buffer to the active online redo log files
3. Checkpoint occurs when log file is full and then DBWR do what need to do (1)
According Oracle documentation: ‘If DBWR process has not finished checkpoint a file and LGWR needs the file again, LGWR has to wait’.
I do not understand why LGWR needs to wait because LGWR works with log files and DBWR works with data files.
imagine your log file size is very small, 500K. remember each log switch cause checkpoint.
if lgwr doesnt wait for dbwr finish flushing dirty buffers, the redo activity is very high and checkpoint takes long. before dbwr finish flushing dirty buffer another log switch occurs (because the log file is small) then dbwr would have to flush again the dirty buffers, now if another log switch occurs again and again (log switch occurs so fast) that dbwr cant follow what is going on since it´s still trying to flush the dirty buffer from first log switch.
that means the reason for waiting is to prevent new order for dbwr to flush dirty buffers but dbwr doesnt flush dir. buff. only on checkpoints.
Which mechanism Oracle uses if there are lot of activities on DB and dbwr is in process of flushing and Server cannot find enough free blocks on the LRU list. Again we have the same problem as we would have if lgwr doesnt wait for dbwr.
usually one dbwr is enough to keep up with checkppint activity, dbwr writes every 3 seconds, data buffer reaches a threshold, checkpoint, no free buffer. If there are lots of activity and no free buffer you will face system waits.
Oracle doesnt only write when there is a checkpoint. A checkpoint doesnt only signal dbwr to flush the dirty buffer, it also sincronizes datafile headers, redo log headers, controlfiles with same SCN, timestamp