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



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).


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:


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

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 :-


I) Only MEMORY_TARGET is set


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.



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.


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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s