Category: Automatic Shared Memory Management

Switch to ASMM in 10g

Oracle Automatic Shared Memory Management is enabled by setting:

  1. A spfile used to specify init.ora values
  2. sga_target parameter is set to a non-zero value
  3. statistics_level parameter set to to TYPICAL (the default) or ALL
  4. 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:

Automatic Shared Memory Management – ASMM

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 for optimal performance.

About Automatic Shared Memory Management:

Automatic Shared Memory Management simplifies SGA memory management. You specify the total amount of SGA memory available to an instance using the SGA_TARGET initialization parameter and Oracle Database automatically distributes this memory among the various SGA components to ensure the most effective memory utilization. When automatic shared memory management is enabled, the sizes of the different SGA components are flexible and can adapt to the needs of a workload without requiring any additional configuration. The database automatically distributes the available memory among the various components as required, allowing the system to maximize the use of all available SGA memory. Oracle Database remembers the sizes of the automatically tuned components across instance shutdowns if you are using a server parameter file (SPFILE). As a result, the system does need to learn the characteristics of the workload again each time an instance is started. It can begin with information from the past instance and continue evaluating workload where it left off at the last shutdown.

The benefits of ASMM are:

  • Reduces the change of running out of shared pool memory
  • Uses available memory optimally
  • Improves database performance by constantly matching memory allocations and instance memory needs

Based on workload information, automatic shared memory tuning:

  • Captures statistics periodically in the background
  • Uses the different memory advisories
  • Performs ?what-if? analyses to determine best distribution of memory
  • Moves memory to where it is most needed
  • Has no need to configure parameters for the worst-case scenario
  • Resurrects component sizes from last shutdown if SPFILE is used

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 (The other value of this parameter is BASIC, which will not allow changing the memory pools automatically.)
  • shared_pool_size must be set to a non-zero value

Oracle10g has introduced special double underscore hidden parameter to control ASMM:

  • __db_cache_size
  • __shared_pool_size
  • __large_pool_size

Once enabled, Oracle ASMM will morph the pool areas within the confines of sga_max_size.

When ASMM is enabled, then the following memory pools are automatically sized:

1 .Buffer cache (DB_CACHE_SIZE)
2. Shared pool (SHARED_POOL_SIZE)
3. Large pool (LARGE_POOL_SIZE)
4. Java pool (JAVA_POOL_SIZE)

The following pools are manually sized components and are not affected by ASMM.

1. DB_KEEP_CACHE_SIZE
2. DB_RECYCLE_CACHE_SIZE
3. DB_nK_CACHE_SIZE (non-default block size)
3. STREAMS_POOL_SIZE
4. LOG_BUFFER

Reference Links: