Databases Reference
In-Depth Information
The overall memory allocated to Oracle can be divided into two categories:
shared memory and nonshared memory. The SGA and the Software Code Area
are shared among all database users. The PGA is considered nonshared. There is
one dedicated PGA allocated for each user connected to the database.
System Global Area
is the memory area that is shared by all con-
nected users of the database. The SGA is broken down into many areas. We will
discuss the areas that hold cached data blocks from database tables, recently exe-
cuted SQL statements, and information on recent structural and data changes in
the database. These areas are known as the database buffer cache, the shared
pool, and the redo log buffer, respectively.
The
System Global Area (SGA)
System Global Area (SGA)
A group of shared memory structures for
a single Oracle instance.
Database Buffer Cache
The
holds copies of database blocks that have been
recently read from or written to the database datafiles. The data cached here
primarily includes table and index data, along with data that supports
database buffer cache
database buffer cache
The memory structure in the SGA that
holds the most recently used or written
blocks of data.
ROLLBACK
statements.
Any database block can be in one of three states: dirty, free, or pinned.
A dirty buffer contains data from a database block that has
been changed or added because of an
Dirty buffers
statement but has not yet been written to disk. This buffer cannot be reused
until it has been successfully written to disk.
INSERT
, an
UPDATE
, or a
DELETE
These buffers either never contained any data or have data
that matches their corresponding database block on disk. Free buffers are
available to be overwritten by another read operation from disk at any
time. Oracle employs an
Free buffers
LRU (least recently used) algorithm
An algorithm used to determine when to
reuse buffers in the database buffer
cache that are not dirty or pinned. The
less frequently a block is used, the more
likely it is to be replaced with a new data-
base block read from disk.
in the buffer
cache; the longer a buffer has not been used, the more likely it is that it will
be reused by a new database block read from disk.
LRU (least recently used) algorithm
These buffers are currently in use by DML statements or
are explicitly saved for future use, and therefore they cannot be reused.
Pinned buffers
Shared Pool
The
contains recently used SQL and PL/SQL statements (stored
procedures and functions). It also contains data from system tables (the data
dictionary tables), such as character set information and security information.
Because objects such as PL/SQL stored functions can be cached in the shared
pool, another user or process that needs the same stored functions can benefit
from the performance improvement because of the stored function already
being in memory.
shared pool
shared pool
An area in the SGA that contains cached
SQL and PL/SQL statements and cached
tables owned by
SYS
.
