Calculate the current memory usage for our AMM RAC database and switch RAC database to ASMM
SQL> select sum(bytes/1024/1024) Current_SGA_SIZE_in_MB from v$sgastat;
CURRENT_SGA_SIZE_IN_MB
----------------------
904.844437
SQL> select sum(bytes/1024/1024) MAX_SGA_SIZE_in_MB from v$sgainfo where name = 'Maximum SGA Size';
MAX_SGA_SIZE_IN_MB
------------------
1592.84766
SQL> show parameter memory_max_target;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 1600M
SQL> select (value/1024/1024) Current_PGA_IN_USE_in_MB from v$pgastat where name = 'total PGA inuse';
CURRENT_PGA_IN_USE_IN_MB
------------------------
122.085938
SQL> select (value/1024/1024) MAX_PGA_ALLOCATED_in_MB from v$pgastat where name = 'maximum PGA allocated';
MAX_PGA_ALLOCATED_IN_MB
-----------------------
167.658203
SQL> select (value/1024/1024) PGA_TARGET_in_MB from v$pgastat where name = 'aggregate PGA target parameter';
PGA_TARGET_IN_MB
----------------
480
Our current AMM uses the following memory
- memory reserved for PGA/SGA: 1600 MByte
- current PGA size 120 MB
- current SGA size 904 MB
- free memory for future PGA/SGA usage: ~ 600 MByte
For switching ASMM this can be translated into
SGA_MAX_SIZE : 1400 MByte
SGA_TARGET : 1000 MByte
PGA_AGGREGATE_TARGET : 480 Mbyte
For further tuning check : V$PGA_TARGET_ADVICE
Execute the following commands.
Disable AMM
SQL> alter system reset memory_max_target scope=spfile sid='*';
SQL> alter system reset memory_target scope=spfile sid='*';
Enable ASMM
SQL> alter system set SGA_MAX_SIZE=1400m scope=spfile sid='*';
SQL> alter system set SGA_TARGET=1000m scope=spfile sid='*';
SQL> alter system set PGA_AGGREGATE_TARGET=480m scope=spfile sid='*';
Reboot database and verify that we have switched from AMM to ASMM
SQL> show parameter memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 0
memory_target big integer 0
--> AMM disabled
SQL> show parameter sga
NAME TYPE VALUE
------------------------ ----------- ------------------------------
sga_max_size big integer 1408M
sga_target big integer 912M
SQL> show parameter pga
NAME TYPE VALUE
------------------------ ----------- ------------------------------
pga_aggregate_target big integer 480M
--> ASMM enabled !
Review impact on OS resources after switchging for AMM to ASMM
The switch from AMM to ASMM frees space in /dev/shm but allocates shared memomry for the SGA
$ ipcs -m
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x00000000 3145746 oracle 640 16777216 41
0x00000000 3178515 oracle 640 1459617792 41
0xe1106fe8 3211284 oracle 640 2097152 41
$ df -h
Filesystem Size Used Avail Use% Mounted on
tmpfs 2.0G 200M 1.9G 10% /dev/shm
10/10 – thanks.
Thanks!
Thanks.
Great article!
Thank you.
One important disadvantage about AMM, is that it is not (as of today) not compatible with Linux Hugepages virtual memory, and this is why it only allocates ‘tmpfs’ (/dev/shm) – Linux’s legacy virtual shared memory.
Contrast this with ASMM, which can address either of these two Linux virtual shared memory mechanisms, and of the two, prefers Hugepages – being the more modern of the two.
That said, even with ASMM, a small amount of ‘tmpfs’ can still be addressed/used for special-purpose functions – i.e., Oracle JIT Compiler used for caching Java native code.
Thanks!! 🙂