Archive
Switch to ASMM in 10g
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:
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:
- ASMM – datadisk.co.uk
- DBA-Oracle.com
- docs.oracle.com
- Memory Configuration and Use