In Oracle10g we need to set TWO parameter for automatic memory management.
- SGA_TARGET = xxx G
- PGA_AGGREGATE_TARGET = xxx G
then oracle will automatically allocated size for Buffer cache, Shared pool, large pool etc.
Now In Oracle 11g will reduce two parameter (SGA_TARGET, PGA_AGGREGATE_TARGET) and introduce one new parameter called “MEMORY_TARGET“. Which use for SGA or PGA size configuration.
When we specify MEMORY_TARGET parameter for instance then we no need to specify SGA_TARGET or PGA_AGGREGATE_TARGET parameter for memory configuration. Oracle will automatically allocate memory for memory parameter.
How to enable automatic memory management?
As previous parameter SGA_MAX_SIZE in 11g also one new parameter MEMORY_MAX_TARGET which is maximum limit for instance memory for oracle database.
SQL> alter system set memory_max_target=200M scope=SPFILE;
System altered.
AMM Configuration
The Database Configuration Assistant (DBCA) allows you to configure automatic memory management during database creation
AMM Tuning
In addition to the existing memory management V$ views, Oracle 11g includes four new V$ views to support automatic memory management:
- V$MEMORY_CURRENT_RESIZE_OPS
- V$MEMORY_DYNAMIC_COMPONENTS
- V$MEMORY_RESIZE_OPS
- V$MEMORY_TARGET_ADVICE
The amount of memory allocated to each dynamic component is displayed using the V$MEMORY_DYNAMIC_COMPONENTS view.
The V$MEMORY_CURRENT_RESIZE_OPS and V$MEMORY_RESIZE_OPS views provide information on current and previous component resize operations.
The V$MEMORY_TARGET_ADVICE view provides information to help tune the MEMORY_TARGET parameter. It displays a range of possible MEMORY_TARGET settings, as factors of the current setting, and estimates the potential DB Time to complete the current workload based on these memory sizes.
