Memory Structures - Expert Oracle Database Architecture - page 150

In Oracle9 i , the various SGA components must be manually sized by the DBA. Starting in Oracle 10 g and above,
however, there is an option to consider: automatic shared memory management, whereby the database instance
will allocate and reallocate the various SGA components at runtime in response to workload conditions. Moreover,
starting in Oracle 11 g , there's another option: automatic memory management, whereby the database instance will
not only perform automatic SGA memory management and automatic PGA memory management, it will also decide
the optimum size of the SGA and PGA for you—reallocating these allotments automatically when deemed reasonable.
Using the automatic shared memory management with Oracle 10 g and above is simply a matter of setting the
SGA_TARGET parameter to the desired SGA size, leaving out the other SGA-related parameters altogether. The database
instance will take it from there, allocating memory to the various pools as needed and even taking memory away from
one pool to give to another over time.
When using automatic memory management with Oracle 11 g and above, you simply set the MEMORY_TARGET .
The database instance will then decide the optimal SGA size and PGA size—and those components will be set up
appropriately and do their own automatic memory management within their respective boundaries. Further, the
database can and will resize the SGA and PGA allocations as the workload changes over time.
Regardless of whether you are using automatic or manual memory management, you'll find that memory is
allocated to the various pools in the SGA in units called granules . A single granule is an area of memory of 4MB, 8MB,
or 16MB in size. The granule is the smallest unit of allocation, so if you ask for a Java pool of 5MB and your granule size
is 4MB, Oracle will actually allocate 8MB to the Java pool (8 being the smallest number greater than or equal to 5 that
is a multiple of the granule size of 4). The size of a granule is determined by the size of your SGA (this sounds recursive
to a degree, as the size of the SGA is dependent on the granule size). You can view the granule sizes used for each pool
by querying V$SGA_DYNAMIC_COMPONENTS . In fact, we can use this view to see how the total SGA size might affect the
size of the granules:
EODA@ORA12CR1> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 256M
EODA@ORA12CR1> select component, granule_size from v$sga_dynamic_components;
COMPONENT GRANULE_SIZE
---------------------------------------------------------------- ------------
shared pool 4194304
large pool 4194304
java pool 4194304
streams pool 4194304
DEFAULT buffer cache 4194304
KEEP buffer cache 4194304
RECYCLE buffer cache 4194304
DEFAULT 2K buffer cache 4194304
DEFAULT 4K buffer cache 4194304
DEFAULT 8K buffer cache 4194304
DEFAULT 16K buffer cache 4194304
DEFAULT 32K buffer cache 4194304
Shared IO Pool 4194304
Data Transfer Cache 4194304
ASM Buffer Cache 4194304
15 rows selected.