Database Buffer
Cache
The database buffer cache holds copies of the
data blocks read from the data files.
Access to the database buffer cache is
shared.
From Oracle version 8 onwards, the buffer cache
contains three buffer pools for different types
of data usage. They are DEFAULT, KEEP, and
RECYCLE. These three buffer pools have separate
allocations of buffers and LRU lists that manage
buffers.
-
The RECYCLE buffer pool is used to store
blocks that are virtually never used after
the initial read
-
The KEEP pool is for blocks that are
referenced frequently
-
The DEFAULT buffer pool contains objects
that are not assigned to any buffer pool and
objects that are explicitly assigned to the
DEFAULT pool.
Direct insert and direct read
operations used for data loading, sorting,
or hashing operations bypass buffer pools.
In Oracle 11g RAC, the database block buffers
from each instance, through cache fusion,
are merged to form a massive logical database
buffer cache.
Large Pool
The large pool is an optional memory area in the
SGA.
This separate memory area is beneficial
for memory intensive tasks such as backup and
restore operations.
JAVA Pool
The JAVA Pool holds the JAVA execution code in a
similar manner to the PL/SQL cache in the shared
pool. The JAVA pool is used by many internal
routines.
Streams Pool
The streams pool is used exclusively by Oracle
Streams.
To configure the streams pool explicitly,
specify the size of the pool in bytes using the
streams_pool_size
initialization parameter.
If the size of the streams pool is zero
or not specified, then the memory used by
streams is allocated from the shared pool.
Redo Log Buffers
Redo log buffers are used to hold the redo
records generated by each data changing
transaction. The redo log buffer is a circular
buffer. Redo entries contain the steps needed to
reconstruct changes made to the database by
UPDATE, INSERT, CREATE, DELETE, ALTER, and DROP
operations. A redo log buffer is written out to
the online redo log by the log writer process
when:
-
The buffer becomes one third full
-
Three seconds have elapsed
-
When a DBWn
process writes modified buffers to disk
-
On commit record - when a user process
commits a transaction
While it is possible to have different sized
redo log buffers and redo logs on each instance
in a RAC database, this is not a suggested
configuration. It can lead to confusion and
misunderstanding during recovery operations.
Each instance in a RAC database must have its
own thread of redo logs.
Shared Pool
The shared pool holds the dictionary cache,
library cache, result cache, message queues,
latch and lock areas, buffers for parallel
execution
messages, and control structures.
The data dictionary is a collection of internal
tables and views of reference information about
the database, the structure and users.
The dictionary cache is the memory area
designated to hold dictionary data. It is also
known as the row cache because it holds data as
rows instead of buffers that hold entire blocks
of data.
The library cache includes the shared SQL areas,
PL/SQL procedures and packages, and control
structures such as locks and library cache
handles.
Fixed SGA
A portion of the SGA contains general
information about the state of the database and
the instance, which the background processes
need to access. This is called the fixed SGA. No
user data is stored here. The SGA also includes
information communicated between processes, such
as locking information.
