Home > Automatic Shared Memory Management > Switch to ASMM in 10g

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:

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: