RelationalDBDesign RelationalDBDesign

Data Buffer Cache   «Prev 

Oracle Database Blocks

Oracle Database Blocks 1. Permanent Residence 2.New data blocks 3. User Process 4. Oracle data buffer cache 5. modified but not written to disk 6. DBWR

  1. This is the permanent residence for the Oracle data blocks.
  2. These are new data blocks.
  3. This is the user process that is requesting to see a row within an Oracle data block.
  4. This is the Oracle data buffer cache which contains data blocks that have been read from disk into the Oracle data buffer cache.
  5. These are blocks that have been modified but not yet written back to disk.
  6. This is the Oracle background database writer process.

Dirty Block

When a particular block is said to be dirty and that block needs to be written to disk, and that same block is reused many times in the cache which means it is in the MRU end. If during that time, 1) the buffer cache is effectively full and 2) the new block from the disk will be replaced by another less used block in the cache, the dirty block which is being discussed will be in the cache itself until it is moved to the (LRU) "Least Recently Used" end.
Question: In such a scenario, when will the block be transferred to the respective data file?
I know that a dirty block will be moved to a redo log file under certain conditions, but apart from this I would like to know when the dirty block will be moved to a datafile. Note: Element 5 in the above diagram is a dirty block.

Dirty Block Defined

Whenever a server process changes or modifies a data block, it becomes a dirty block. Once a server process makes changes to the data block, the user may commit transactions, or transactions may not be committed for quite some time. In either case, the dirty block is not immediately written back to disk. Writing dirty blocks to disk takes place under the following two conditions:
  1. When a server process cannot find a clean, reusable buffer after scanning a threshold number of buffers, then the database writer process writes the dirty blocks to disk.
  2. When the checkpoint takes place, the database writer process writes the dirty blocks to disk.