A log buffer is a circular
buffer in the SGA that holds information about changes made to the
database. This information is stored in the redo entries. Redo
entries contain the information necessary to reconstruct or redo
changes made to the database by insert, update, delete, create,
alter, or drop operations. Redo entries are primarily used for
database recovery as necessary.
The server processes generate
redo data into the log buffer as they make changes to the data
blocks in the buffer. LGWR subsequently writes entries from the redo
log buffer to the online redo log.
Database Buffer Cache
The database buffer cache holds
copies of data blocks read from the data files. The term data block
is used to describe a block containing table data, index data,
clustered data, and so on. Basically, it is a block that contains
data. All user processes concurrently connected to the instance
share access to the database buffer cache. The database buffer cache
is logically segmented into multiple sets. This reduces contention
on multiprocessor systems.
This area of the SGA contains
only the buffers themselves and not their control structures. For
each buffer, there is a corresponding buffer header in the variable
area of the SGA.
Program Global Area (PGA)
A Program Global Area (PGA) is a
memory region that contains data and control information for a
server process. It is a non-shared memory region created by Oracle
when a server process is started. Access to the PGA is exclusive to
that server process and it is read and written only by Oracle code
acting on its behalf. It contains a private SQL area and a session
A private SQL area contains data
such as bind information and runtime memory structures. Each session
that issues a SQL statement has a private SQL area. Session memory
is the memory allocated to hold a session's variables (logon
information) and other information related to the session. For a
shared server, the session memory is shared and not private.
Buffer Cache Management
The database buffer cache is
organized in two lists: the write list and 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 buffers
that are currently being accessed.
When an Oracle process requires
data, it searches the buffer cache, finds the data blocks, and then
uses the data. This is known as a cache hit. If it cannot find the
data, then it must be obtained from the data file. In this case, it
finds a free buffer to accommodate the data block by scanning the
LRU list, starting at the least-recently-used from the end of the
list. The process searches either until it finds a free buffer or
until it has searched the threshold limit of buffers.
When the user process is
performing a full table scan, it reads the data blocks into buffers
and places them on the LRU end instead of the MRU end of the LRU
list. This is because a fully scanned table is usually needed only
briefly and the blocks should be moved out quickly.
What Is a Dirty Block?
Whenever a server process
changes or modifies a data block, it becomes a dirty block. Once a
server process makes changes to the data block, the user may commit
transactions, or transactions may not be committed for quite some
time. In either case, the dirty block is not immediately written
back to disk.
Writing dirty blocks to disk
takes place under the following two conditions:
* When a server process cannot
find a clean, reusable buffer after scanning a threshold number of
buffers, then the database writer process writes the dirty blocks to
* When the checkpoint takes
place, the database writer process writes the dirty blocks to disk.