Automatic Memory Management (AMM) in Oracle
Automatic Memory Management (AMM) in Oracle
Oracle has made great strides in simplifying memory management over the last few versions of the database. Oracle 9i automated PGA management by introducing PGA_AGGREGATE_TARGET parameter. Oracle 10g continued this trend by automating SGA management using the SGA_TARGET parameter. Oracle 11g takes this one step further by allowing you to allocate one chunk of memory, which Oracle uses to dynamically manage both the SGA and PGA.
Automatic memory management is configured using two new initialization parameters:
- MEMORY_TARGET: The amount of shared memory available for Oracle to use when dynamically controlling the SGA and PGA. This parameter is dynamic, so the total amount of memory available to Oracle can be increased or decreased, provided it does not exceed the
MEMORY_MAX_TARGET
limit. The default value is "0". - MEMORY_MAX_TARGET: This defines the maximum size the
MEMORY_TARGET
can be increased to without an instance restart. If theMEMORY_MAX_TARGET
is not specified, it defaults toMEMORY_TARGET
setting.
When using automatic memory management, the
SGA_TARGET
and PGA_AGGREGATE_TARGET
act as minimum size settings for their respective memory areas. To allow Oracle to take full control of the memory management, these parameters should be set to zero.
If you are using UNIX/Linux, before you consider using AMM you should check the current size of your shared memory file system. On Linux you do this by issuing the following command.
# df -k /dev/shm Filesystem 1K-blocks Used Available Use% Mounted on tmpfs 1029884 350916 678968 35% /dev/shm #
The shared memory file system should be big enough to accommodate the
MEMORY_TARGET
and MEMORY_MAX_TARGET
values, or Oracle will throw the following error.ORA-00845: MEMORY_TARGET not supported on this system
To adjust the shared memory file system size issue the following commands, specifying the required size of shared memory.
# umount tmpfs # mount -t tmpfs shmfs -o size=1200m /dev/shm
Make the setting permanent by amending the "tmpfs" setting of the "/etc/fstab" file to look like this.
tmpfs /dev/shm t mpfs size=1200m 0 0
The following queries show you how to display the relevant information and how to combine it in a single statement to calculate the required value.
-- Calculate MEMORY_TARGET SELECT sga.value + GREATEST(pga.value, max_pga.value) AS memory_target FROM (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;
Assuming our required setting was 5G, we might issue the following statements.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;
Comments
Post a Comment