Saturday, October 20, 2012

0 AMM ( Automatic Memory Management )

Oracle 9i introduce PGA_AGGREGATE_TARGET to help automatically manage PGA. 10g introduce SGA_TARGET to help automatically SGA. From 11g, we have AMM. AMM is kind of combine both together and help manage both  SGA and PGA.

AMM let oracle automatic decide the size of each memory components ( Share pool, data buffer cache …). To enable the AMM, we need to set the MEMORY_TARGET. If the MEMORY_TARGET bigger than 0, that means enable the AMM.

show parameter target


Since total memory of the oracle instance require is the combination of SGA and PGA, we need to set the MEMORY_TARGET at least of the current size of the SGA+PGA.

Determine the PGA size

v$pgastat stores the state for the PGA usage. The pga usage will change over time after database is running. To ensure we allocate enough memory for PGA, we need to find out the max PGA memory ever used.

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


Enable AMM

  • Determine SGA and PGA size: SGA_MAX_SISE + maximum PGA allocated
  • set the memory target: the MEMORY_MAX_TARGET is the max memory oracle instance can use. We only need to set the MEMORY_TARGET. The default value for MEMORY_MAX_TARGET is same as MEMORY_TARGET. MEMORY_MAX_TARGET is static parameter.
alter system set MEMORY_TARGET=1024M scope=spfile;
alter system set SGA_TARGET=0;
alter system set PGA_AGGREGATE_TARGET=0 scope=spfile;
shutdwon immediate


After startup the instance, the MEMORY_TARGET set as same as MEMORY_MAX_TARGET.



v$memory_target_advice is the new view to help DBA determine what the impact would do if we increase/decrease the MEMORY_TARGET.

SELECT * FROM v$memory_target_advice ORDER BY memory_size;

In the example here, the MEMORY_TARGET is 1G, even we reduce it to 512MB , The ESTD_DB_TIME is the same. Therefor , to avoid wasting memory, we can reduce the memory.



V$MEMORY_DYNAMIC_COMPONENTS is the new view that show the size of the dynamic components.

COLUMN component FORMAT A24
SELECT  component, current_size, min_size, max_size,user_specified_size FROM v$memory_dynamic_components



V$MEMORY_CURRENT_RESIZE_OPS and V$MEMORY_RESIZE_OPS shows current and previous memory size change under AMM. V$SGA_INFO shows the SGA size.

RES = YES means that AMM can automatically adjust the size.




Post a Comment


SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates