Tips by Burleson
Oracle10g Automatic Shared Memory Management
The system global area (SGA) consists of memory components. A
component represents a pool of memory used to satisfy a particular
class of memory allocation requests. The most commonly configured
memory components include the database buffer cache, shared pool,
large pool, and java pool. Since we fix the values for these
components at instance start time, we are constrained to use them
as they are during the instance runtime (with some exceptions).
Often it happens that a certain component’s memory pool is
never used but the pool 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, we can employ the Automatic Shared
Memory Management feature. 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 we normally face in a manual
This feature enables us to specify a total memory amount to be
used for all SGA components. The Oracle Database periodically
redistributes memory between the components above according to
Using the sga_target initialization parameter configures
automatic Shared Memory Management. If you specify a non-zero
value for sga_target, the following four memory pools are
- Database Buffer cache (The Default pool)
- Shared pool
- Large pool
- Java pool
If you set sga_target to 0, the Automatic Shared Memory
Management is disabled. The default value of sga_target is 0. When
sga_target is not set, or equal to zero, auto-tuned SGA parameters
behave as in previous releases of the Oracle database.
In 10g, the initialization parameters such as db_cache_size,
shared_pool_size, large_pool_size, and java_pool_size are referred
to as 'autotuned sga’ parameters.
Configuration of the following buffers still remains manual and
they are now referred to as manually sized components:
- Log Buffer
- Other Buffer Caches (KEEP/RECYCLE, other block sizes)
- Streams Pool (new in Oracle Database 10g)
- Fixed SGA and other internal allocations
The user specifies manual SGA parameters, and parameter sizes
precisely control the sizes 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
Figure 2.3 shows an example of SGA components.
Are you wondering how this auto management is 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 components. The SGA Memory
Broker keeps track of the sizes of the components and pending