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