The
database buffer cache is the important portion in SGA that holds the copies of
the data blocks from the data files.
How
Database Buffer cache works
Buffers
in the cache are organized in to 2 parts.
a. Write List and
b. Least Recently Used (LRU) list.
WRITE LIST holds the dirty buffers contains the data that
has been modified but has not yet be written to the disk.
LEAST RECENTLY USED 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 database process accesses a buffer, the process moves the buffer to the
most recently used (MRU) list. As more
buffers are continually moved to the MRU end of the LRU list , dirty buffer age
toward the LRU end of the LRU list.
First
time the oracle database process requires a particular piece of data, it
searches the data in the database buffer cache. If the process finds the data
already in cache( a cache hit), it
can read the data directly in the memory.
If the process cannot find the data in cache ( a cache miss) , it must copy the data from the data block from a datafile on disk into the buffer 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 Database 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.
How LRU Algorithm works for Full Table Scan
When the
user process is performing a full table scan, it reads the blocks of the table
into buffers and puts them on the LRU end (instead of the MRU end) of the LRU
list. This is because a fully scanned table usually is needed only briefly, so
the blocks should be moved out quickly to leave more frequently used blocks in
the cache.
You can
control this default behavior of blocks involved in table scans on a
table-by-table basis. To specify that blocks of the table are to be placed at
the MRU end of the list during a full table scan, use the CACHE clause when
creating or altering a table or cluster. You can specify this behavior for
small lookup tables or large static historical tables to avoid I/O on
subsequent accesses of the table.
