Automatic Memory Management (AMM) under Oracle 11g R1 (1)

Bkjia.com exclusive Translation]Oracle has made great efforts in simplifying memory management over the past few years. Since Oracle 9i implements automatic PGA management through the PGA_AGGREGATE_TARGET parameter, Oracle 10g implements automatic SGA management through the SGA_TARGET parameter, oracle 11g is surprisingly capable of fully automatic management of all the memory blocks of the database. It makes dynamic management of SGA and PGA a reality.

At the time of writing this article, automatic memory management AMM has been supported on mainstream platforms, including Linux, Windows, Solaris, HP-UX, AIX.

AMM Parameters

Automatic Memory Management is configured with two initialization parameters:

MEMORY_TARGET: the total amount of shared memory that Oracle can use when SGA and PGA are dynamically controlled. this parameter is dynamic, so the total amount of memory provided to Oracle can be dynamically increased, it can also be dynamically reduced. It cannot exceed the size set by the MEMORY_MAX_TARGET parameter. The default value is 0.

MEMORY_MAX_TARGET: this parameter defines the maximum value of MEMORY_TARGET that can be reached without restarting the instance. If the MEMORY_MAX_TARGET value is not set, it is equal to the value of MEMORY_TARGET by default.

When dynamic memory management is used, SGA_TARGET and PGA_AGGREGATE_TARGET represent the minimum settings of their respective memory regions. To allow Oracle to fully control memory management, these two parameters should be set to 0.

AMM Configuration

During database creation, Database Configuration assistant DBCA allows you to configure automatic memory management.

When creating a database manually, you only need to set the appropriate MEMORY_TARGET and MEMORY_MAX_TARGET initialization parameters before creating the database.

Enabling automatic memory management on a system is actually very simple. You do not need to do too much in advance. You can use the following formula for calculation:

MEMORY_TARGET=SGA_TARGET+GREATEST(PGA_AGGREGATE_TARGET,"maximumPGAallocated")

The following query statement shows you the relevant information and how to calculate the required value:

-- Individual values.COLUMN name FORMAT A30COLUMN value FORMAT A10SELECT name, valueFROM v$parameterWHERE name IN ('pga_aggregate_target', 'sga_target')UNIONSELECT 'maximum PGA allocated' AS name, TO_CHAR(value) AS valueFROM v$pgastatWHERE name = 'maximum PGA allocated';-- Calculate MEMORY_TARGETSELECT sga.value + GREATEST(pga.value, max_pga.value) AS memory_targetFROM (SELECT TO_NUMBER(value) AS value FROM v$parameter WHERE name = 'sga_target') sga, (SELECT TO_NUMBER(value) AS value FROM v$parameter WHERE name = 'pga_aggregate_target') pga, (SELECT value FROM v$pgastat WHERE name = 'maximum PGA allocated') max_pga;

Suppose we need to set 5 GB, then we can execute the following statement:

CONN / AS SYSDBA-- Set the static parameter. Leave some room for possible future growth without restart.ALTER SYSTEM SET MEMORY_MAX_TARGET=6G SCOPE=SPFILE;-- Set the dynamic parameters. Assuming Oracle has full control.ALTER SYSTEM SET MEMORY_TARGET=5G SCOPE=SPFILE;ALTER SYSTEM SET PGA_AGGREGATE_TARGET=0 SCOPE=SPFILE;ALTER SYSTEM SET SGA_TARGET=0 SCOPE=SPFILE;-- Restart instance.SHUTDOWN IMMEDIATE;STARTUP;

After the database is restarted, The MEMORY_TARGET parameter can be changed without restarting the instance. For example:

ALTER SYSTEM SET MEMORY_TARGET=4G SCOPE=SPFILE;