Enable Automatic Shared Memory Management in Oracle

Enable Automatic Shared Memory Management in Oracle

The Oracle Automatic Shared Memory Management is a feature that automatically readjusts the 
sizes of the main pools (db_cache_size, shared_pool_size, large_pool_size, java_pool_size) 
based on existing workloads. Oracle Automatic Shared Memory Management is enabled by setting:

You must use an spfile for the init.ora values

sga_target parameter is set to a non-zero value

statistics_level parameter set to to TYPICAL (the default) or ALL

To switch to ASMM you need to set the initialization parameters SGA_TARGET to a non-zero 
value which must be less then or equal to value of parameter SGA_MAX_SIZE.

$ sqlplus / as sysdba

SQL> alter system set sga_max_size=35840m scope=spfile;

System altered.

SQL> alter system set sga_target=30720m scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 3.7413E+10 bytes
Fixed Size                  2236168 bytes
Variable Size            8053063928 bytes
Database Buffers         2.9259E+10 bytes
Redo Buffers               98414592 bytes
Database mounted.
Database opened.

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 35G
sga_target                           big integer 30G
SQL>

memory_target (starting in 11g):  If memory_target  is set, then AMM is enabled:  If memory_target is set to non zero value and :sga_target, sga_max_size and pga_aggregate_target are set to 0, then 60% of memory mentioned in memory_target is allocated to SGA and rest 40% is kept for PGA.sga_target and pga_aggregate_target are set to non-zero values, then these values will be considered minimum values.sga_target is set to non zero value and pga_aggregate_target is not set. still these values will be auto-tuned and pga_aggregate_target will be initialized with value of (memory_target-sga_target).pga_aggregate_target is set and sga_target is not set. still both parameters 

will be auto-tuned. The sga_target will be initialized to a value of (memory_target-pga_aggregate_target).