Jump to Navigation

241 - Auto Memory Management (AMM)

1. MEMORY_TARGET

MEMORY_TARGET specifies the Oracle system-wide usable memory. The database tunes memory to the MEMORY_TARGET value,
reducing or enlarging the SGA and PGA as needed.

2. MEMORY_MAX_TARGET

MEMORY_MAX_TARGET specifies the maximum value to which a DBA can set the MEMORY_TARGET initialization parameter.
See the description of MEMORY_TARGET for more information about how the settings of MEMORY_MAX_TARGET and
MEMORY_TARGET affect each other.

3. MEMORY_TARGET and MEMORY_MAX_TARGET

MEMORY_MAX_TARGET >= MEMORY_TARGET
If you set MEMORY_TARGET and not set MEMORY_MAX_TARGET,、Oracle tries to use the value of MEMORY_MAX_TARGET for MEMORY_TARGET.
If you do not set MEMORY_TARGET and MEMORY_MAX_TARGET,、Oracle set MEMORY_MAX_TARGET to "0" MEMORY_MAX_TARGET > 0
or not set MEMORY_TARGET, DBA can change the value of MEMORY_TARGET.

4. Note

* If you set LOCK_SGA, MEMORY_MAX_TARGET and MEMORY_TARGET are disabled
* If Huge Pages on Linux, MEMORY_MAX_TARGET and MEMORY_TARGET are disabled.
* Unless /dev/shm >= MEMORY_TARGET, You get an error like ORA-00845.

MEMORY_TARGET = 0
It is working on ASMM (Oracle 10g)

MEMORY_TARGET > 0 AND SGA_TARGET > 0 AND PAG_AGGREGATE_TARGET > 0
SGA_TARGET + PAG_AGGREGATE_TARGET <= MEMORY_TARGET <= MEMORY_MAX_TARGET

MEMORY_TARGET > 0 AND SGA_TARGET > 0 AND PAG_AGGREGATE_TARGET = 0
PAG_AGGREGATE_TARGET = MEMORY_TARGET - SGA_TARGET

MEMORY_TARGET > 0 AND SGA_TARGET = 0 AND PAG_AGGREGATE_TARGET > 0
SGA_TARGET = min(MEMORY_TARGET - PAG_AGGREGATE_TARGET , SGA_MAX_SIZE)

MEMORY_TARGET > 0 AND SGA_TARGET = 0 AND PAG_AGGREGATE_TARGET = 0
SGA_TARGET = MEMORY_TARGET*60%
PAG_AGGREGATE_TARGET = MEMORY_TARGET*40%

5. V$MEMORY_TARGET_ADVICE

V$MEMORY_TARGET_ADVICE provides information about how the MEMORY_TARGET parameter
should be sized based on current sizing and satisfaction metrics. SIZE_FACTOR=1 is the current configuration.

SQL> select * from v$memory_target_advice order by memory_size_factor;

6. Show components which are managed by AMM.

SQL> col component format a20

SQL> select component, current_size curr_size, min_size, max_size,user_specified_size uss, granule_size gs from v$memory_dynamic_components where current_size > 0;

7. Show maximum PGA allocated from database started.

SQL> select value from v$pgastat where name='maximum PGA allocated';

memory_target = sga_target + max(pga_aggregate_target, maximum PGA allocated)

 

 



Main menu 2

Story | by Dr. Radut