SGA_TARGET
SGA_TARGET specifies the total size of all SGA components. If SGA_TARGET is specified, then the following memory pools are automatically sized:
Buffer cache (DB_CACHE_SIZE)
Shared pool (SHARED_POOL_SIZE)
Large pool (LARGE_POOL_SIZE)
Java pool (JAVA_POOL_SIZE)
If these automatically tuned memory pools are set to non-zero values, then those values are used as minimum levels by Automatic Shared Memory Management. You would set minimum values if an application component needs a minimum amount of memory to function properly.
The following pools are manually sized components and are not affected by Automatic Shared Memory Management:
Log buffer
Other buffer caches, such as KEEP, RECYCLE, and other block sizes
Streams pool
Fixed SGA and other internal allocations
The memory allocated to these pools is deducted from the total available for SGA_TARGET when Automatic Shared Memory Management computes the values of the automatically tuned memory pools.
SGA_MAX_SIZE
SGA_MAX_SIZE specifies the maximum size of the SGA for the lifetime of the instance.
Initial size of SGA at startup, dependent on the sizes of different pools in the SGA, such as buffer cache, shared pool, large pool, and so on.
SGA_TARGET vs SGA_MAX_SIZE
SGA_MAX_SIZE sets the overall amount of memory the SGA can consume but is not dynamic. SGA_TARGET, a new 10g feature used for Automatic Shared Memory Management allows for dynamic resizing of SGA
SGA_MAX_SIZE parameter is the max allowable size to resize the SGA Memory area parameters. If the SGA_TARGET is set to some value then the Automatic Shared Memory Management (ASMM) is enabled, the SGA_TARGET value can be adjusted up to the SGA_MAX_SIZE parameter, not more than SGA_MAX_SIZE parameter value.
i.e. If SGA_MAX_SIZE=4GB and SGA_TARGET=2GB, later period of time, if you want you can resize your SGA_TARGET parameter to the value of SGA_MAX_SIZE i.e. 4GB, you can't resize the SGA_TARGET value to more than 4GB.
Usually, sga_max_size and sga_target will be the same value, but there may be times when you want to have the capability to adjust for peak loads. By setting the Oracle sga_max_size parameter higher than sga_target, you allow dynamic adjustment of the sga_target parameter.
Note that sga_max_size is for Oracle 10g only, and in 11g and beyond, Oracle automatic memory management is configured using the memory_target and memory_max_target initialization parameters. The memory_target parameter specifies the amount of shared memory available for Oracle to use when dynamically controlling the SGA and PGA. The memory_max_target AMM parameter specifies the max size that memory_target may take.
You can enable and disable AMM by changing the value of these parameters to non-zero settings:
sga_target
sga_max_size
pga_aggregate_target
sga_max_size
SGA_TARGET specifies the total size of all SGA components. If SGA_TARGET is specified, then the following memory pools are automatically sized:
Buffer cache (DB_CACHE_SIZE)
Shared pool (SHARED_POOL_SIZE)
Large pool (LARGE_POOL_SIZE)
Java pool (JAVA_POOL_SIZE)
If these automatically tuned memory pools are set to non-zero values, then those values are used as minimum levels by Automatic Shared Memory Management. You would set minimum values if an application component needs a minimum amount of memory to function properly.
The following pools are manually sized components and are not affected by Automatic Shared Memory Management:
Log buffer
Other buffer caches, such as KEEP, RECYCLE, and other block sizes
Streams pool
Fixed SGA and other internal allocations
The memory allocated to these pools is deducted from the total available for SGA_TARGET when Automatic Shared Memory Management computes the values of the automatically tuned memory pools.
SGA_MAX_SIZE
SGA_MAX_SIZE specifies the maximum size of the SGA for the lifetime of the instance.
Initial size of SGA at startup, dependent on the sizes of different pools in the SGA, such as buffer cache, shared pool, large pool, and so on.
SGA_TARGET vs SGA_MAX_SIZE
SGA_MAX_SIZE sets the overall amount of memory the SGA can consume but is not dynamic. SGA_TARGET, a new 10g feature used for Automatic Shared Memory Management allows for dynamic resizing of SGA
SGA_MAX_SIZE parameter is the max allowable size to resize the SGA Memory area parameters. If the SGA_TARGET is set to some value then the Automatic Shared Memory Management (ASMM) is enabled, the SGA_TARGET value can be adjusted up to the SGA_MAX_SIZE parameter, not more than SGA_MAX_SIZE parameter value.
i.e. If SGA_MAX_SIZE=4GB and SGA_TARGET=2GB, later period of time, if you want you can resize your SGA_TARGET parameter to the value of SGA_MAX_SIZE i.e. 4GB, you can't resize the SGA_TARGET value to more than 4GB.
Usually, sga_max_size and sga_target will be the same value, but there may be times when you want to have the capability to adjust for peak loads. By setting the Oracle sga_max_size parameter higher than sga_target, you allow dynamic adjustment of the sga_target parameter.
Note that sga_max_size is for Oracle 10g only, and in 11g and beyond, Oracle automatic memory management is configured using the memory_target and memory_max_target initialization parameters. The memory_target parameter specifies the amount of shared memory available for Oracle to use when dynamically controlling the SGA and PGA. The memory_max_target AMM parameter specifies the max size that memory_target may take.
You can enable and disable AMM by changing the value of these parameters to non-zero settings:
sga_target
sga_max_size
pga_aggregate_target
sga_max_size
