Sunday, April 27, 2008

Automatic Shared Memory Management

Automatic Shared Memory Management puts Oracle in control of allocating memory within the SGA. The SGA_TARGET parameter sets the amount of memory available to the SGA. This parameter can be altered dynamically up to a maximum of the SGA_MAX_SIZE parameter value. Provided the STATISTICS_LEVEL is set to TYPICAL or ALL and the SGA_TARGET is set to a value other than "0" Oracle will control the memory pools which would otherwise be controlled by the following parameters:

  1. BD_CACHE_SIZE (default block size)
  2. SHARED_POOL_SIZE
  3. LARGE_POOL_SIZE
  4. JAVA_POOL_SIZE

If these parameters are set to a non-zero value they represent the minimum size for the pool. These minimum values may be necessary if you experience application errors when certain pool sizes drop below a specific threshold.
The following parameters must be set manually and take memory from the quota allocated by the SGA_TARGET parameter:
  1. DB_KEEP_CACHE_SIZE
  2. DB_RECYCLE_CACHE_SIZE
  3. DB_nK_CACHE_SIZE(non-default block size)
  4. STREAMS_POOL_SIZE
  5. LOG_BUFFER

Enable Automatic Shared Memory Management

suppose we want to set sga_max_size = 1.5 GB and sga_target =1.2 GB

sql> shutdown immediate;
database shutdown.
sql> startup mount;
database mounted.
sql> alter system set SGA_MAX_SIZE = 1500M scope=spfile;
database altered.
sql> shutdown immediate;

database shutdown.
sql> startup ;
database open.
sql> alter system set SGA_TARGET = 800M scope=both;
database altered.


From now oracle dynamically manage the size of SHARED_POOL_SIZE,Buffer Cache, LARGE_POOL_SIZE,JAVA_POOL_SIZE and if needed oracle increase sga_target upto 1.5BG .

No comments: