How Oracle finds Space to write to new Data

Reading new data from disk is one of the things that can indirectly trigger the database writer to write dirty buffers back to disk. When Oracle reads a block of data from the disk, it must place that data somewhere in the database buffer cache. If it can find an empty buffer, it uses that. Otherwise, it looks for an old buffer that has not been changed. If it finds one, it overwrites it with the new data. After checking a certain number of old buffers, if an unchanged one has not been found, the DBWR process will be asked to start writing some data back to disk in order to free up space for the new data. The following series of images demonstrates how this works.

Sequence for using Buffers: When Oracle is checking for unmodified buffers it always starts with the least recently used buffer and works its way forward to the most recently used. Similarly when writing data, Oracle will write the least recently used blocks first.

How does Oracle know which blocks (or buffers) have been used recently, and which have not? Oracle keeps a list and the name of this list is the Least Recently Used List, or the LRU List for short. The LRU list is a list of pointers. There is a most recently used end, and a least recently used end. Each pointer in the list refers to a block in the database buffer cache. Here's a diagram that illustrates this:

Every time a block in the buffer cache is read or modified as the result of a query, the pointer to that block is moved to the furthest end of the LRU list. This is the general rule. There are some exceptions to this that you will learn about later.
Over time, this natural sorting mechanism results in the pointers to the least recently used blocks ending up at the least end of the list. These are the least likely to be required by any subsequent queries, so they are always the first ones to be replaced when Oracle needs to read in fresh data.

The database buffer cache holds blocks of data retrieved from the database. This buffer between the requests of the users and the actual datafiles improves the performance of the Oracle database. If a piece of data can be found in the buffer cache (for example, as the result of a recent query), you can retrieve it from memory without the overhead of having to go to disk. Oracle manages the cache using a least recently used (LRU) algorithm. If a user requests data that has been recently used, the data is more likely to be in the database buffer cache; data in the cache can be delivered immediately without a disk-read operation being executed.
When a user wants to read a block that is not in the cache, the block must be read and loaded into the cache. When a user makes changes to a block, those changes are made to the block in the cache. At some later time, those changes will be written to the datafile in which the block resides. This avoids making users wait while Oracle writes their changed blocks to disk. This notion of waiting to perform I/O until absolutely necessary is common throughout Oracle. Disks are the slowest component of a computer system, so the less I/O performed, the faster the system runs. By deferring noncritical I/O operations instead of performing them immediately, an Oracle database can deliver better performance.Since Oracle8, the database buffer cache can be configured with buffer pools.

The buffers in the cache are organized in two lists:

1. the write list and
2. the least recently used (LRU) list.

The write list holds dirty buffers, which contain data that has been modified but has not yet been written to disk. The LRU list holds free buffers, pinned buffers, and dirty buffers that have not yet been moved to the write list. Free buffers do not contain any useful data and are available for use. Pinned buffers are currently being accessed. When an Oracle process accesses a buffer, the process moves the buffer to the most recently used (MRU) end of the LRU list. As more buffers are continually moved to the MRU end of the LRU list, dirty buffers age toward the LRU end of the LRU list. The first time an Oracle user process requires a particular piece of data, it searches for the data in the database buffer cache. If the process finds the data already in the cache (a cache hit), it can read the data directly from memory. If the process cannot find the data in the cache (a cache miss), it must copy the data block from a datafile on disk into a buffer in the cache before accessing the data. Accessing data through a cache hit is faster than data access through a cache miss. Before reading a data block into the cache, the process must first find a free buffer. The process searches the LRU list, starting at the least recently used end of the list. The process searches either until it finds a free buffer or until it has searched the threshold limit of buffers. If the user process finds a dirty buffer as it searches the LRU list, it moves that buffer to the write list and continues to search. When the process finds a free buffer, it reads the data block from disk into the buffer and moves the buffer to the MRU end of the LRU list. If an Oracle user process searches the threshold limit of buffers without finding a free buffer, the process stops searching the LRU list and signals the DBW0 background process to write some of the dirty buffers to disk.