8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Automatic Memory Management (AMM) in Oracle Database 11g Release 1
Don't use this feature! Although on the surface it looks like an improvement from a simplicity perspective, it is a bad feature. Instead use the SGA_TARGET and PGA_AGGREGATE_TARGET parameters to manage your memory. Even Oracle have distanced themselves from it. In later releases it can't be selected in the DBCA for memory configurations in excess of 4G. The rest of this article is only left for historical purposes.
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.
At the time of writing, Automatic Memory Management (AMM) is only supported on the major platforms (Linux, Solaris, Windows, HP-UX, AIX).
AMM Parameters
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 tmpfs size=1200m 0 0
AMM Configuration
The Database Configuration Assistant (DBCA) allows you to configure automatic memory management during database creation.
When creating the database manually, simply set the appropriate MEMORY_TARGET
and MEMORY_MAX_TARGET
initialization parameters before creating the database.
Enabling automatic memory management on a system that didn't previously use it is a simple task. Assuming you want to use a similar amount of memory to your current settings you will need to use the following calculation.
MEMORY_TARGET = SGA_TARGET + GREATEST(PGA_AGGREGATE_TARGET, "maximum PGA allocated")
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.
-- Individual values. COLUMN name FORMAT A30 COLUMN value FORMAT A10 SELECT name, value FROM v$parameter WHERE name IN ('pga_aggregate_target', 'sga_target') UNION SELECT 'maximum PGA allocated' AS name, TO_CHAR(value) AS value FROM v$pgastat WHERE name = 'maximum PGA allocated'; -- 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;
Once the database is restarted the MEMORY_TARGET
parameter can be amended as required without an instance restart.
ALTER SYSTEM SET MEMORY_TARGET=4G SCOPE=SPFILE;
AMM Tuning
In addition to the existing memory management V$ views, Oracle 11g includes four new V$ views to support automatic memory management:
The amount of memory allocated to each dynamic component is displayed using the V$MEMORY_DYNAMIC_COMPONENTS
view.
COLUMN component FORMAT A30 SELECT component, current_size, min_size, max_size FROM v$memory_dynamic_components WHERE current_size != 0; COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE ------------------------------ ------------ ---------- ---------- shared pool 197132288 192937984 197132288 large pool 4194304 4194304 4194304 java pool 41943040 41943040 41943040 SGA Target 318767104 285212672 318767104 DEFAULT buffer cache 71303168 41943040 75497472 PGA Target 104857600 104857600 138412032 6 rows selected. SQL>
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.
SELECT * FROM v$memory_target_advice ORDER BY memory_size; MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION ----------- ------------------ ------------ ------------------- ---------- 303 .75 3068 1.0038 2 404 1 3056 1 2 505 1.25 3056 1 2 606 1.5 3056 1 2 707 1.75 3056 1 2 808 2 3056 1 2 6 rows selected. SQL>
Enterprise Manager includes the memory management configuration and advisor functionality in the "Memory Advisors" screen (Advisor Central > Memory Advisors).
Clicking the "Advice" button displays the "Memory Size Advice" screen, which contains a graphical representation of the information from the V$MEMORY_TARGET_ADVICE
view.
Considerations Before Using AMM
When you have large SGA sizes you can get considerable benefits from using HugePages. Automatic Memory Management and HugePages on Linux are not compatible, which means AMM is probably not a sensible option for any large systems. Instead, Automatic Shared Memory Management and Automatic PGA Management should be used as they are compatible with HugePages.
Even so, AMM is the default for all ASM instances and should be left that way. From a database perspective, it still may be relevant for smaller, less important databases.
For more information see:
- Using Automatic Memory Management
- MEMORY_TARGET
- MEMORY_MAX_TARGET
- Oracle Database 11g Automatic Memory Management - Part I. Linux Hugepages Support
- Oracle Database 11g Automatic Memory Management - Part II. Automatically Stupid?
- Oracle Database 11g Automatic Memory Management - Part III. Automatically Automatic?
- Oracle 11g internals part 1: Automatic Memory Management
Hope this helps. Regards Tim...