Configure ASMM and AMM in Oracle

Jephe Wu - http://linuxtechres.blogspot.com

Objective: understanding ASMM and AMM in Oracle
Environment: Oracle 10g and 11g

References:  Oracle support - Automatic Memory Management (AMM) on 11g [ID 443746.1]


Concepts:
The SGA is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance.
A PGA is a memory region that contains data and control information for a server process. It is nonshared memory created by Oracle Database when a server process is started

There is one PGA for each server process. Background processes also allocate their own PGAs.

ASMM: Automatic Shared Memory Management (ASMM) was introduced in 10g. You enable the automatic shared memory management feature by setting the SGA_TARGET parameter to a non-zero value.

AMM:  Automatic Memory Management is being introduced in 11g

SGA: contains database buffer cache, redo log buffer, shared pool, large pool, Java pool and streams pool

The shared pool portion of the SGA contains the library cache, the dictionary cache, the result cache, buffers for parallel execution messages, and control structures.


Configuration:

1. AMM in 11g:
Two new parameters have been introduced named MEMORY_MAX_TARGET and MEMORY_TARGET. To do so (on most platforms), you set only a target memory size initialization parameter (MEMORY_TARGET) and optionally a maximum memory size initialization parameter (MEMORY_MAX_TARGET).

SQL>ALTER SYSTEM SET MEMORY_MAX_TARGET = 800M SCOPE = SPFILE; (must bounce database if change it)
SQL>ALTER SYSTEM SET MEMORY_TARGET = 800M SCOPE = SPFILE; (dynamically can be changed actually)
SQL>ALTER SYSTEM SET SGA_TARGET =0 SCOPE = SPFILE;
SQL>ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0 SCOPE = SPFILE;

if using pfile, modify parameter in pfile as follows:

MEMORY_MAX_TARGET = 808M
MEMORY_TARGET = 808M
SGA_TARGET =0
PGA_AGGREGATE_TARGET = 0

a. if you don't specify memory_max_target, it will be same as memory_target by default.
b. if you don't specify memory_target, only memory_max_target, then memory_target will be 0 by default, then you can change it dynamically after startup instance.
c. if you also set sga_target or pga_aggregate_target, the values act as minimum values for the sizes of the SGA or instance PGA.

2. ASMM in 10g
If you'd like to disable AMM, only enable ASMM, then
sql>alter system set memory_max_target=0 scope=spfile;

SQL>Alter system set MEMORY_TARGET=0 scope=both;
SQL>Alter system set SGA_TARGET=500M scope=both;

3. manual SGA management
set SGA_TARGET and MEMORY_TARGET to 0 and set value for other SGA components upto value of SGA_MAX_SIZE.

sga_target vs sga_max_size , is like memory_taget vs memory_max_target in 11g.

4. pga_aggregate_target
With automatic PGA memory management, you set a target size for the instance PGA by defining value for parameter named PGA_AGGREGATE_TARGET and sizing of SQL work areas is automatic and all *_AREA_SIZE initialization parameters are ignored for these sessions

5. best practise:

in 11g, set AMM(memory_target and memory_max_target, unset others)
in 10g, set ASMM(sga_target,sga_max_size and pga_aggregate_target)

6. References and Commands:
a. show sga
b. show parameter sga
c. show parameter pga
d. select * from v$sgainfo
e. ipcs -ma (os command)
f. select name, value from v$parameter
         where name in ('sga_max_size', 'shared_pool_size', 'db_cache_size',
         'large_pool_size','java_pool_size');
g. show parameter shared_pool