The Oracle database is accessed through an
instance. The combination of SGA (System Global
Area) with one or more Oracle processes constitutes an
instance. After the instance is started, the
database is associated with it. This process is
called database mounting. In the case of a RAC
system, the database can be associated with
multiple instances. The main purpose of the SGA
is to store data in memory for quick access and
for processing.
SGA – System Global Area
The instance is the structure or entity with which
application users connect. The SGA is a group of
shared memory structures that contain data and
control information for the database instance.
Oracle allocates memory for an SGA system
whenever the instance is started. Multiple
instances can be associated with a database in a
RAC system, and each instance has its own SGA.
The SGA contains five main areas:
The fixed area of the SGA contains several
thousand atomic variables. These are small data structures, such as latches
and pointers, which refer to other areas of the SGA. The size
of the fixed area is static. It also contains
general information about the state of the
database and the instance which the background
processes need to access.
The variable part of the SGA is made up of a
large pool and a shared pool. All memory in the
large pool is dynamically allocated, whereas the
shared pool contains both dynamically managed
memory and a permanent memory. The database
buffer cache is where database block copies are
held for processing. All user processes
concurrently connected to the instance share
access to the database buffer cache. There are
many groups of buffers within the SGA.
Shared Pool and Large Pool
The shared pool segment of the SGA contains
three major areas: the library cache, the
dictionary cache, and buffers for parallel
execution messages.
-
Library Cache - The library cache
includes the shared SQL areas, PL/SQL
procedures and packages and control
structures such as library cache handles,
locks, synonym translations, and dependency
tracking information. It contains parse
trees and execution plans for shareable SQL
statements,
as well as pseudo code for PL/SQL program
units. All users access the shared SQL
areas.
-
Dictionary Cache – Includes the usernames, segment
information, profile data, tablespace
information, and the sequence numbers. The
dictionary cache
also contains descriptive information or
metadata about the schema objects. Oracle
uses this metadata when parsing SQL cursors
or during the compilation of PL/SQL
programs.
The dictionary cache is also known as the
row cache because it holds the data in rows
instead of buffers. It also holds entire
blocks of data. This helps to reduce
physical access to the data dictionary
tables from the system tablespace and also
enables fine-grained locking of individual
data dictionary rows.
The large pool is an optional area. If the
large_pool_size
parameter is set, then the large pool is
configured as a separate heap within a variable
area of the SGA. The large pool is not a part of
the shared pool.
Using the large pool instead of the shared pool
decreases fragmentation of the shared pool.
Unlike the shared pool, the large pool does not
have an LRU list. Oracle does not attempt to age
memory out of the large pool. The large pool is
useful to allocate large memory allocations for:
-
Session memory for the shared
server and the Oracle XA interface that is
used where transactions interact with more
than one database
-
I/O server processes
-
Oracle backup and restore
operations - recovery manager can use the
large pool to cache I/O buffers during
backup and restore operations
