Dynamic Modification of SGA_TARGET (103)

The SGA_TARGET parameter can be increased up
to the value specified for the SGA_MAX_SIZE parameter, and it can also be reduced. If
you reduce the value of SGA_TARGET, the system identifies one or more automatically
tuned components for which to release memory. You can reduce SGA_TARGET until
one or more automatically tuned components reach their minimum size. Oracle
Database determines the minimum allowable value for SGA_TARGET taking into
account several factors, including values set for the automatically sized components,
manually sized components that use SGA_TARGET space, and number of CPUs.

The change in the amount of physical memory consumed when SGA_TARGET is
modified depends on the operating system. On some UNIX platforms that do not
support dynamic shared memory, the physical memory in use by the SGA is equal to
the value of the SGA_MAX_SIZE parameter. On such platforms, there is no real benefit
in setting SGA_TARGET to a value smaller than SGA_MAX_SIZE. Therefore, setting
SGA_MAX_SIZE on those platforms is not recommended.

On other platforms, such as Solaris and Windows, the physical memory consumed by
the SGA is equal to the value of SGA_TARGET.

When SGA_TARGET is resized, the only components affected are the automatically
tuned components for which you have not set a minimum value in their
corresponding initialization parameter. Any manually configured components remain
unaffected.
For example, suppose you have an environment with the following configuration:
■ SGA_MAX_SIZE = 1024M
■ SGA_TARGET = 512M
■ DB_8K_CACHE_SIZE = 128M

In this example, the value of SGA_TARGET can be resized up to 1024M and can also be
reduced until one or more of the automatically sized components reaches its minimum
size. The exact value depends on environmental factors such as the number of CPUs
on the system. However, the value of DB_8K_CACHE_SIZE remains fixed at all times
at 128M

When SGA_TARGET is reduced, if minimum values for any automatically tuned
components are specified, those components are not reduced smaller than that
minimum. Consider the following combination of parameters:
■ SGA_MAX_SIZE = 1024M
■ SGA_TARGET = 512M
■ DB_CACHE_SIZE = 96M
■ DB_8K_CACHE_SIZE = 128M

As in the last example, if SGA_TARGET is reduced, the DB_8K_CACHE_SIZE
parameter is permanently fixed at 128M. In addition, the primary buffer cache
(determined by the DB_CACHE_SIZE parameter) is not reduced smaller than 96M.
Thus the amount that SGA_TARGET can be reduced is restricted.

Note: When enabling automatic shared memory management, it is
best to set SGA_TARGET to the desired non-zero value before starting
the database. Dynamically modifying SGA_TARGET from zero to a
non-zero value may not achieve the desired results because the shared
pool may not be able to shrink. After startup, you can dynamically
tune SGA_TARGET up or down as required.

[@more@]