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%.
Oracle 10g, with its emphasis on
self-management and self-tuning, introduces many features that
operate automatically, as well as in an advisory role. Since the
values for the SGA components are fixed at instance start time, they
must be used as they are during the instance runtime, with some
Often it happens that a certain
component?s memory pool is never used and it is not available for
another component, which is in need of extra memory. Under-sizing
can lead to poor performance and out-of memory errors (ORA-4031),
while over-sizing can waste memory.
With the Database 10g, the
Automatic Shared Memory Management feature can be employed. This
feature enables the Oracle database to automatically determine the
size of each of these memory components within the limits of the
total SGA size. This solves the allocation issues that are normally
faced in a manual method.
This feature enables the
specification of a total memory amount to be used for all SGA
components. The Oracle Database periodically redistributes memory
between the components according to workload requirements.
Use of the sga_target
initialization parameter configures automatic Shared Memory
Management. If a non-zero value is specified for sga_target, the
following four memory pools are automatically sized by Oracle.
* Database Buffer cache (Only
the Default pool)
* Shared Pool
* Large Pool
* Java Pool
If sga_target is set to the
default value of 0, Automatic Shared Memory Management is disabled.
When sga_target is not set, or equal to the default value of 0,
auto-tuned SGA parameters behave as in previous releases of the
Oracle database. In Oracle 10g, the initialization parameters such
as db_cache_size, shared_pool_size, large_pool_size, and
java_pool_size are referred to as auto-tuned SGA parameters.
Figure 4.4 shows an example of
auto tuned SGA components.
Figure 4.4: SGA Memory Buffers
Configuration of the following
buffers still remains manual and they are now referred to as
* Log Buffer
* Other Buffer Caches
(KEEP/RECYCLE, other block sizes)
* Streams Pool (Newly introduced
in Oracle Database 10g)
* Fixed SGA and other internal
The user specifies manual SGA
parameters, and parameter sizes precisely control the size of their
corresponding components. When sga_target is set, the total size of
manual SGA parameters are subtracted from the sga_target value and
the balance is given to the auto-tuned SGA components.
sga_target is also a dynamic
parameter and can be changed through Enterprise Manager or with the
ALTER SYSTEM command. However, the sga_target can be increased only
up to the value of sga_max_size.
The question may arise, how is
auto management possible? A new background process named Memory
Manager (MMAN) manages the automatic shared memory. MMAN serves as
the SGA Memory Broker and coordinates the sizing of the memory
Important: statistics_level must
be set to TYPICAL (default) or ALL to use Automatic Shared Memory
Program Global Area (PGA)
A program global area (PGA) is a
memory region that stores the data and control information for the
server processes. Each Server process has 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. Broadly speaking, PGA 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 an SQL statement has a private SQL area.
Note that the Location of a
private SQL area depends on the type of connection established for a
session. If a session is connected through a dedicated server,
private SQL areas are located in the server process? PGA. However,
if a session is connected through a shared server, part of the
private SQL area is kept in the SGA.
Session memory is the memory
allocated to hold a session?s variables (logon information) and
other information related to the session. For a shared server, the
session memory is shared and not private.
the initialization parameter pga_aggregate_target, sizing of work
areas for all dedicated sessions is made automatic and all *_area_size
parameters are ignored for these sessions. At any given time, the
total amount of PGA memory available to active work areas on the
instance is automatically derived from the pga_aggregate_target