RelationalDBDesign RelationalDBDesign

Instance Architecture   «Prev 

Oracle LRU List

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:

LRU List displaying the 'Least end' on the left and 'Most end' on the right

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 most 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.

Database buffer cache

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.