What is Managed Automatically through 10g ASMM
The Automatic Shared Memory Management functionality is enabled when you set SGA_TARGET to a value greater than 0 in 10g+. ASMM will then attempt to manage what it thinks is the best size for the following components in the SGA:
Shared Pool
Large Pool
Java Pool
Buffer Cache (the default on managed by db_cache_size)
Streams Pool (10g R2+)
The other buffer caches (managed through parameters DB_nK_CACHE_SIZE, DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE), Log Buffer and Fixed SGA areas are not automatically tuned by MMAN. However these settings do affect the actual memory available to MMAN in the SGA_TARGET setting.
The SGA_TARGET can be changed dynamically up to the setting for SGA_MAX_SIZE. However, keep in mind there are limitations for some platforms on how LOCK_SGA works with memory at the OS level.
As an illustration how this works we’ll use an example where the SGA_TARGET is set to 4 GB.
In addition to specifying a value for SGA_TARGET you have also set values for DB_KEEP_CACHE_SIZE, LOG_BUFFER and DB_4K_CACHE_SIZE and for this example we’ll use the values 256M for db_keep_cache_size, 200M for the log_buffer size and 512M for the db_4k_cache_size.
We also elected to manually set the size for the shared pool to 1 GB (SHARED_POOL_SIZE=1G).
Now you want to know how does this affect MMAN. Well this means that MMAN can not manage or auto-tune the memory components will all 4 GB of memory. MMAN can only access 2,206,203,904 bytes.
The following breakdown serves to illustrate how the memory is allocated in the SGA:
Log Buffers 209,715,200
Keep Buffer Cache 268,435,456
4K Buffer Cache 536,870,912
Shared Pool 1,073,741,824
==============================
Total 2,088,763,393
Note: (by manually assigning a value to the shared pool you are telling Oracle that the size of the shared pool can not shrink below this value)
There are benefits to manually choosing a value for the auto-tuned components of the SGA. However it does have an impact on the amount of memory that MMAN has access to when it needs to grow and shrink the various components.
The following is a general rule of thumb for setting up ASMM:
SGA_TARGET = 256M* # of CPUs
The above rule of thumb makes a number of assumptions about your application’s memory usage. It is simply a starting point, you should identify a starting point and run queries to determine the appropriate size of the pools for your specific application. Some of the following assumptions are:
- Run queries and observe the memory trends to determine the minimum size for the various pools to meet the needs of your application
- Set the SGA_TARGET to 25% above the sum of the fixed and hard-coded settings
- Start with a set of hard-coded values for the pool parameter settings in your initial Oracle init file.
- Select a value for the SGA_MAX_SIZE equal to 25% above the setting for SGA_TARGET. This valuue won’t work for every environment. Memory for SGA_MAX_SIZE is allocated by Oracle at startup. By setting it to a value lower than SGA_MAX_SIZE it will provide you will the ability to bump up the size of the SGA_TARGET which may benefit your application performance.
After a reading a tremendous amount of articles, books and papers I’ve found that it is best to set a value as the minimum size for the above pools in your spfile rather than permitting Oracle to set them for you. You need to identify the base values as your starting point as you query, observe and determine what the values should be for your environment going forward.
As a starting point review the data returned by querying v$sga_dynamic_components and manually assign values to the pools at 70 to 75% of the current size. If there are indications of failed attempts to shrink the shared pool below that number over time, then decrease the default setting by another 10% and monitor the environment to determine if that value is more appropriate.
