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 memory area.
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, or
logon information, and other information related
to the session.
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.
Dirty Blocks
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 disk
-
When the checkpoint takes place,
the database writer process writes the dirty
blocks to disk
