This is an excerpt from the bestselling book
Oracle Grid & Real Application Clusters. To get immediate
access to the code depot of working RAC scripts, buy it
directly from the publisher and save more than 30%.
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.
* The variable area.
* The database buffer cache.
* The log buffer.
* The resource directory for a
RAC system.
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, private SQL areas
(in shared server), 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.
* Parallel execution message
buffers, when the initialization parameter parallel_automatic_tuning
is set to TRUE.
and include the URL for the page.
