Memory Structures - Expert Oracle Database Architecture - page 168

So just be aware of the possibility that in 11 g Release 2 and higher, Oracle might perform some degree of
automatic SGA memory reallocation, even when you've turned the feature off. As earlier noted, Oracle does this to
keep processes from running out of space in the shared pool.

■ See MOS note 1269139.1 for further details on automatic SGa resizing when using manual shared memory
management.

Tip

Automatic Shared Memory Management
Under automatic shared memory management, the primary parameter for sizing the total auto-tuned components is
SGA_TARGET , which may be dynamically sized while the database is up and running, up to the setting of the SGA_MAX_
SIZE parameter. This defaults to be equal to the SGA_TARGET , so if you plan on increasing the SGA_TARGET , you must
have set the SGA_MAX_SIZE larger before starting the database instance. The database will use the SGA_TARGET value,
minus the size of any of the other manually sized components such as the DB_KEEP_CACHE_SIZE , DB_RECYCLE_CACHE_
SIZE , and so on, and use that amount of memory to size the default buffer pool, shared pool, large pool, and Java
pool. Dynamically at runtime, the instance will allocate and reallocate memory among those four memory areas as
needed. Instead of returning an ORA-04031 “ Unable to allocate N bytes of shared memory ” error to a user when
the shared pool runs out of memory, the instance could instead choose to shrink the buffer cache by some number of
megabytes (a granule size) and increase the shared pool by that amount.

■ To use automatic shared memory management, the parameter STATISTICS_LEVEL must be set to TYPICAL
(default value) or ALL . If statistics collection is not enabled, the database will not have the historical information needed to
make the necessary sizing decisions.

Note

Over time, as the memory needs of the instance are ascertained, the size of the various SGA components would
become more or less fixed in size. The database also remembers the sizes of these four components across database
startup and shutdown so that it doesn't have to start all over again figuring out the right size for your instance each
time. It does this via four double-underscore parameters: __db_cache_size , __java_pool_size , __large_pool_size ,
and __shared_pool_size . During a normal or immediate shutdown, the database will record these values to the
stored parameter file and use them at startup to set the default sizes of each area.

■ This last feature, of storing the recommended values for the pools, only works if you are using stored
parameter files (also known as spfile ).

Note

Additionally, if you know you want a certain minimum value to be used for one of the five areas, you may set that
parameter in addition to setting the SGA_TARGET . The instance will use your setting as the lower bound, or the smallest
size that particular area may be.