SQL> select sum(bytes/1024/1024) Current_SGA_SIZE_in_MB from v$sgastat;
CURRENT_SGA_SIZE_IN_MB
----------------------
1804.448437
SQL> select sum(bytes/1024/1024) MAX_SGA_SIZE_in_MB from v$sgainfo where name = 'Maximum SGA Size';
MAX_SGA_SIZE_IN_MB
------------------
2592.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
------------------------
788.078938
SQL> select (value/1024/1024) MAX_PGA_ALLOCATED_in_MB from v$pgastat where name = 'maximum PGA allocated';
MAX_PGA_ALLOCATED_IN_MB
-----------------------
1567.658203
SQL> select (value/1024/1024) PGA_TARGET_in_MB from v$pgastat where name = 'aggregate PGA target parameter';
PGA_TARGET_IN_MB
----------------
480
Memory usage of exisitng AMM:
- memory reserved for PGA/SGA: 8 GB
- current PGA size 790 MB
- current SGA size 1804 MB
- free memory for future PGA/SGA usage: ~ 2.5 GB
For switching ASMM this can be translated into
SGA_MAX_SIZE : 3 GB
SGA_TARGET : 3 GB
PGA_AGGREGATE_TARGET : 2 GB
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=3G scope=spfile sid='*';
SQL> alter system set SGA_TARGET=3G scope=spfile sid='*';
SQL> alter system set PGA_AGGREGATE_TARGET=2G 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 3G
sga_target big integer 3G
SQL> show parameter pga
NAME TYPE VALUE
------------------------ ----------- ------------------------------
pga_aggregate_target big integer 2G
--> ASMM enabled !
CURRENT_SGA_SIZE_IN_MB
----------------------
1804.448437
SQL> select sum(bytes/1024/1024) MAX_SGA_SIZE_in_MB from v$sgainfo where name = 'Maximum SGA Size';
MAX_SGA_SIZE_IN_MB
------------------
2592.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
------------------------
788.078938
SQL> select (value/1024/1024) MAX_PGA_ALLOCATED_in_MB from v$pgastat where name = 'maximum PGA allocated';
MAX_PGA_ALLOCATED_IN_MB
-----------------------
1567.658203
SQL> select (value/1024/1024) PGA_TARGET_in_MB from v$pgastat where name = 'aggregate PGA target parameter';
PGA_TARGET_IN_MB
----------------
480
Memory usage of exisitng AMM:
- memory reserved for PGA/SGA: 8 GB
- current PGA size 790 MB
- current SGA size 1804 MB
- free memory for future PGA/SGA usage: ~ 2.5 GB
For switching ASMM this can be translated into
SGA_MAX_SIZE : 3 GB
SGA_TARGET : 3 GB
PGA_AGGREGATE_TARGET : 2 GB
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=3G scope=spfile sid='*';
SQL> alter system set SGA_TARGET=3G scope=spfile sid='*';
SQL> alter system set PGA_AGGREGATE_TARGET=2G 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 3G
sga_target big integer 3G
SQL> show parameter pga
NAME TYPE VALUE
------------------------ ----------- ------------------------------
pga_aggregate_target big integer 2G
--> ASMM enabled !