Lesson 10	Writing out dirty buffers to make room for new data
Objective	Explain how the database writer writes out buffers.

How Oracle Frees Up Space for New Data

In an Oracle database, reading new data from disk can trigger a crucial background process: the writing of "dirty" buffers. When a user process needs to read a data block from the disk, it must first find a place for it in the database buffer cache, a shared memory area. If the cache has a free buffer, Oracle uses it. If not, it must make space.

This process is governed by the Least Recently Used (LRU) algorithm, which helps Oracle decide which existing data blocks in the cache can be overwritten. The following guide, illustrated by a 10-step sequence, explains how Oracle handles this common scenario.

The Core Components

Before diving into the process, it's important to understand a few key concepts:

Database Buffer Cache: This is a component of the System Global Area (SGA) that stores copies of data blocks read from data files. By keeping frequently accessed data in this fast memory cache, Oracle significantly reduces slow disk I/O and improves performance.
LRU (Least Recently Used) List: Oracle maintains a list of all the blocks in the buffer cache, ordered from most recently used (MRU) to least recently used (LRU). When a block is accessed, it's moved to the MRU end of the list. This ensures that the blocks at the LRU end are the ones that have not been accessed for the longest time, making them primary candidates for removal.
Dirty Buffers: When a user modifies data, the changes are first made to the block's copy in the buffer cache. A buffer containing such uncommitted changes is called a dirty buffer. This data has not yet been written back to the data files on disk. The list of dirty buffers is called the "write list."
DBWR (Database Writer): This is the background process responsible for writing dirty buffers from the cache to the physical data files on disk.

Writing Out Dirty Buffers: A Step-by-Step Guide

The following scenario illustrates what happens when a user process needs to read data from a disk, but the buffer cache is already full of modified (dirty) and unmodified blocks.

Step 1: The buffer cache is nearly full, with only one free buffer remaining. — 1) The process begins with a database buffer cache that is almost full, containing a mix of dirty (modified) and clean (unmodified) blocks, with only one free buffer left.

Step 2: A data block is read from disk and placed into the last empty buffer. — 2) A server process reads a new data block from the disk and places it into the last available free buffer. The cache is now completely full.

Step 3: With the cache full, another data read requires overwriting an existing buffer. — 3) Now, the server process needs to read another block from the disk. Since no buffers are free, it must find one to overwrite.

Step 4: The search for a free buffer begins at the least recently used (LRU) end of the list. — 4) The process starts scanning from the least recently used (LRU) end of the list, looking for a clean buffer. The first block it checks is dirty, so it cannot be overwritten yet.

Step 5: The scan continues, skipping another dirty buffer. — 5) The process moves to the next buffer in the LRU list. This one is also dirty, so it is skipped and moved to the write list.

Step 6: The scan finally finds a clean, unmodified buffer that can be used. — 6) The scan continues until it finds a clean (unmodified) buffer. This buffer can be overwritten immediately with the new data from the disk.

Step 7: A scenario where the scan finds only dirty buffers after checking several. — 7) However, if the cache is filled with many dirty buffers, the server process may scan a set threshold of buffers without finding a single clean one.

Step 8: The DBWR process is signaled to write dirty buffers to disk. — 8) When this threshold is reached, the server process stops scanning and signals the **Database Writer (DBWR)** to write some dirty buffers to disk to free up space.

Step 9: DBWR writes dirty data to disk, converting them into clean buffers. — 9) The DBWR process writes the contents of the dirty buffers from the write list to the data files on disk. This action "cleans" the buffers, making them available for overwriting.

Step 10: The server process now uses one of the newly cleaned buffers for its data. — 10) With clean buffers now available, the server process can take one of them to hold the new data block it initially needed to read from the disk.