The server processes create a copy of the datafile block into the database buffer cache. If the block is modified in the database buffer cache, the DBWn process will write the block to the datafiles.

The default instance configuration is based on configuring the database buffer cache as a single part of the memory to receive blocks in memory, but it may be divided into smaller sections to improve database utilization.

Now, we will discuss the algorithm used by Oracle to choose the buffers where the blocks will be placed, and also to determine which blocks will be written again into the datafile.

The LRU List and the Checkpoint Queue

All buffers in the database buffer cache will be in one of the following three states:

• Pinned: This buffer is currently being used in memory, and until the work in this buffer is completed, we cannot use this buffer in other operations.
• Dirty: This buffer contains a block copy that differs from the version on disk. It is a block that has been copied to the cache and modified, but has not yet been written to disk. Therefore, it is impossible to bring another block from the datafile and place it in this buffer without first cleaning it via DBWn, which will write it to the datafile.
• Free: This buffer is neither pinned nor dirty, meaning it is either unused or has been cleaned.

When the instance is started, all buffers are unused (unused).

When a server process needs to read data into memory, it first searches for the block on disk and for a free buffer in the database buffer cache that will receive the block from disk, ensuring that it cannot copy a block from disk into a pinned buffer because it will be protected by a locking mechanism. Additionally, it cannot copy a block from disk into a dirty buffer as long as the changes have not been written to disk by DBWn, but it can copy a block into a free buffer because the data contained within it is also available on disk. There are many free buffers in the database buffer cache, but the question that arises is: which free buffer will the server process choose to store the block? This is important for performance, and the selection is based on the Least Recently Used (LRU) algorithm.

All buffers in the database buffer cache have pointers, and there is a list containing all the buffers called the LRU list. New buffers are added to this list based on their recent access. Thus, the buffer that has been accessed the longest ago will be added to the end of the LRU list.

When a server process searches for a buffer, it will evict the least recently used buffer (the end of the LRU list), while the recently used buffer will be placed at the beginning of the list. Therefore, a very busy buffer will not have its contents written to disk because the server process will not find it since it is located at the least recently used end of the LRU list.

There is another list for buffers called the Checkpoint Queue. This list contains dirty buffers waiting to have their contents written to disk via the DBWn process.

Server processes perform a search, and if they find a free buffer, they add it to the LRU list. If they find a dirty buffer, they add it to the Checkpoint Queue.

Of course, the addresses are added to the lists, not the buffer itself.

If the Checkpoint Queue contains dirty buffers, after some time, the contents of these buffers are written to disk via the DBWn process, and they then become clean or free buffers, allowing them to be used again.

This means that the server process will often first search for the block on disk, then for free buffers, and also search for dirty buffers to place them in the Checkpoint Queue for later writing to disk.

This simple mechanism allows Oracle to minimize disk I/O.

The DBWn process only writes dirty and static buffers. Buffers that have not been changed will not be written to disk. Additionally, dirty buffers will only be written to disk if they have not been interacted with for some time. The only moment when all dirty buffers are written to disk is during a database checkpoint, as well as during a tablespace checkpoint, which transfers all dirty buffers related to that tablespace to disk. The tablespace checkpoint is automatically updated as a result of the following operations:

• Dropping the tablespace
• Making the tablespace read-only
• Putting the tablespace into hot backup mode
• Taking the tablespace offline