Oracle 12c provides a single parameter allowing the database instance to automatically manage all memory pools called MEMORY_TARGET.  When MEMORY_TARGET is set to a given value, oracle process grabs that much memory and allocates it to the various memory pools.  MEMORY_TARGET parameter is dynamic and can be adjusted with the database online.   MEMORY_MAX_TARGET parameter is an optional method to specify the maximum amount of memory for oracle to use.   MEMORY_MAX_TARGET is not dynamic and cannot be changed without restarting database.  MEMORY_MAX_TARGET serves as an upper limit to MEMORY_TARGET so the parameter is not set too high.

 

AMM (Automatic memory management) is the database instance configuration which manages and distributes memory allocations between SGA and PGA automatically and redistributes based on changing requirements.   Databases created by DBCA will have AMM enabled by default.

Automatic Shared Memory Management ASMM was the version before Oracle 12c and AMM.  In ASMM the total amount of memory for SGA is specified by SGA_TARGET parameter, oracle will automatically distribute the memory specified by SGA_TARGET to the pools:

LOG_BUFFER, DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE, and DB_nK_CACHE_SIZE.

Any memory allocated by parameters to above 4 pools will be subtracted from SGA_TARGET allocation and the rest is distributed amongst: SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE, DB_CACHE_SIZE, STREAMS_POOL_SIZE.   BOTH ASMM and AMM use database statistics and cannot be used if the parameter STATISTICS_LEVEL is set to BASIC.

 

 

Setup AMM by changing the following initialization parameters:

 

ALTER SYSTEM SET  MEMORY_TARGET = nM;

ALTER SYSTEM SET  SGA_TARGET = 0;

ALTER SYSTEM SET  PGA_AGGREGATE_TARGET = 0;

 

Optionally, you can also set the MEMORY_MAX_TARGET parameter:

 

ALTER SYSTEM SET MEMORY_MAX_TARGET = nM scope = SPFILE;

Restart the database.

 

 

Oracle Initialization Parameters concerning Memory Management:

 

MEMORY_TARGET – When set the Oracle grabs the memory at startup and gives 60% to SGA and 40% to PGA by default.   It redistribute memory as needed.   IF not set AMM is not in use, even if a value is set for MEMORY_MAX_TARGET.

 

MEMORY_MAX_TARGET – Sets the maximum amount of memory that Oracle will grab.   If not set, it default to MEMORY_TARGET.

 

SGA_TARGET  – Parameter is not needed if using AMM, if it is defined the value becomes the minimum amount of memory allocated by AMM.

 

PGA_AGGREGATE_TARGET – Parameter is not needed if using AMM, if it is defined the value becomes the minimum amount of memory allocated by AMM.

 

 

Larry Catt

OCP