Automatic Memory Management Oracle 11GR2
In 11GR2 there is a new feature Automatic Memory Management by using which both the SGA and PGA are managed.
If there are so many sessions due to which PGA is used heavily and there is less usage of SGA then the server will release the memory from the SGA and allocate the memory to PGA and same for SGA.
The parameters used by the AMM (Automatic Memory Management) is
Memory_max_target
Memory_target
SGA_MAX_SIZE
SGA_TARGET
PGA_AGGREGATE_TARGET
AMM functionality is implemented by the Memory Manager process (hereafter called MMAN).
[oracle@DWH_UAT_DB2:~]$ps -ef | grep -i mman
oracle 5898436 1 0 Feb 18 – 5:17 asm_mman_+ASM
oracle 12910636 1 0 Jul 17 – 0:08 ora_mman_OGURI
AMM is enabled by the use of either of these parameters:-
MEMORY_TARGET – defines the outer limits to which the sum of the SGA and PGA can grow.
MEMORY_MAX_TARGET – defines the outer limit to which the MEMORY_TARGET can be manually, dynamically, extended (i.e. without a database restart).
EX:-
If we have set the memory_max_target to 15G and memory_target to 10G , in future if we want to extend memory_target to 11G , we can do that without restarting the database.
alter system set memory_target=11G; — No need to restart the database
Regardless of the Operating System used, when the instance starts up, an amount of memory equal to MEMORY_MAX_TARGET will be locked by MMAN. If MEMORY_MAX_TARGET is not set in the parameter file, it defaults to MEMORY_TARGET.
All SGA memory parameters can be set in an AMM environment. If no SGA memory parameters are set, MMAN defaults in the following ratio:
60% to SGA_TARGET
40% to PGA_AGGREGATE_TARGET
If SGA_TARGET is explicitly set in the parameter file, it becomes the lower limit (minimum size) for the SGA. The PGA_AGGREGATE_TARGET will always get an amount of memory equal to
(MEMORY_TARGET – SGA_TARGET).
If you want to control the PGA from occupying the SGA , we can set the sga_target , so the pga can not increase more than the (memory_max_target-sga_target).The space allocated to the sga_target is intact.
Memory management :-
Scenarios
I) Only MEMORY_TARGET is set
MEMORY_TARGET=1G
In this case MEMORY_MAX_TARGET is not set, so it defaults to MEMORY_TARGET.
SQL> select * from v$sgainfo where name like ‘Maximum SGA%’ or name like ‘Free SGA%’;
NAME BYTES RES
——————————– ———- —
Maximum SGA Size 1068937216 No
Free SGA Memory Available 432013312
SQL> select component,current_size from v$memory_dynamic_components where component like ‘%Target%’;
COMPONENT CURRENT_SIZE
—————————————————————- ————
SGA Target 641728512
PGA Target 432013312
1) The PGA is (MEMORY_TARGET-SGA TARGET). V$SGAINFO also shows that 412M is free memory. This free memory can be used either for the PGA, or for resizing the SGA.
2) The initial ratio of memory allocation is 60% to SGA, 40% to PGA. This is the default if neither SGA_TARGET nor PGA_AGGREGATE_TARGET is set. This means, of the 1GB of MEMORY_TARGET, SGA got 60% (612M) and free memory was 40% (412M).
3) If PGA needs to extend more than the 412 M it will get the memory from the SGA.
II) MEMORY_TARGET, SGA_TARGET and PGA_AGGREGATE_TARGET are set
MEMORY_TARGET=1G
SGA_TARGET=300M
PGA_AGGREGATE_TARGET=100M
In this case MEMORY_MAX_TARGET is not set, so it defaults to MEMORY_TARGET.
Here by default the SGA get the 60% of MEMORY_TARGET(612M) and PGA(412M).
If there are more processes or sessions and require more PGA it can get until 700M it can not get more. Same as SGA can not get more than 900M because the 100M is fixed for PGA , it can not be taken by the PGA.
NOTE :
1) When SGA_MAX_SIZE is set in the parameter file, MMAN will not transfer memory into the SGA that would cause it to exceed this size.
2) When SGA_TARGET is set in the parameter file, MMAN will not transfer any memory out of the sga that will cause it to go below this size.
Viswamitra's blog 