Oracle Automatic Shared Memory Management is enabled by setting:
- A spfile used to specify init.ora values
- sga_target parameter is set to a non-zero value
- statistics_level parameter set to to TYPICAL (the default) or ALL
- shared_pool_size must be set to a non-zero value
Check different component sizes from v$parameter
select name,value
from v$parameter
where name in('shared_pool_size','large_pool_size',
'java_pool_size','db_cache_size','sga_target');
Switch to ASMM:
$ sqlplus / as sysdba SQL> alter system set sga_max_size=1G scope=spfile; System altered. SQL> alter system set sga_target=500m scope=both; System altered. SQL> ALTER SYSTEM SET SHARED_POOL_SIZE = 0; System altered. SQL> ALTER SYSTEM SET LARGE_POOL_SIZE = 0; System altered. SQL> ALTER SYSTEM SET JAVA_POOL_SIZE = 0; System altered. SQL> ALTER SYSTEM SET DB_CACHE_SIZE = 0; System altered. SQL> ALTER SYSTEM SET STREAMS_POOL_SIZE = 0; System altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 1223540 bytes Variable Size 738198668 bytes Database Buffers 327155712 bytes Redo Buffers 7163904 bytes Database mounted. Database opened. SQL> show parameter sga_target NAME TYPE VALUE ---------------- ------------------ ------------- sga_target big integer 500M SQL> show parameter sga_max_size NAME TYPE VALUE ------------------ ------------------ ------------- sga_max_size big integer 1G
Disable ASSM:
Disabling ASSM can be done by chaning SGA_TARGET parameter to zero value. But we need to set the below parameter with proper sizing when we disable ASMM
1. shared_pool_size
2. large_pool_size
3. java_pool_size
4. db_cache_size
SQL> alter system set sga_target=0 scope=both; System altered.
We may need to disable ASMM if there is significant amount of memory resizing activity that cause the performance issue in database. This frequent memory resizing might happen when the environment is mixed of DSS and OLTP environment.
Source / Reference Links: