CONFIGURING AUTOMATIC SHARED MEMORY MANAGEMENT

AUTOMATIC SHARED MEMORY MANAGEMENT

In Oracle 10g, the memory components are collectively known as the System Global area. Some of the memory components also referred to as pools need to have their size configured properly. The main pools are the shared pool, the large pool, the database cache size and the Java Pool. The appropriate individual sizes need to configured based on the overall SGA size. This can be quite a challenge by itself. Under-sizing may lead to poor performance and out-of-memory errors, while over-sizing may result in wasting of memory.

In Oracle 10g, all you need to specify is the total amount of memory to be used by all SGA components. The database uses this value to redistribute memory between the various components based on the current workload of the database.

•The new initialization parameter (SGA_TARGET) can include all the memory in the SGA, manually and automatically seized components and any internal allocations during startup.

•The initialization parameters used in Oracle 9i and earlier namely DB_CACHE_SIZE, LARGE_POOL_SIZE, SHARED_POOL_SIZE and JAVA_POOL_SIZE are now considered auto-tuned SGA parameters. When the SGA_TARGET is set, the total for the manual SGA size parameters is subtracted from the SGA_TARGET to obtain the amount of memory that can be allocated to auto-tuned memory components.

•The Automatic Shared Memory Management feature uses a background process called Memory Manager (MMAN). This background process is responsible for coordinating the sizes of the various memory components dynamically based on the current workload. This process checks the current workload on the database every few minutes, to ensure that memory is always present wherever needed.

•The basic method used by automatic shared memory is given below:
•Statistics are gathered periodically
•Different memory advisories are used to make recommendations
•"What If" analysis is performed to determine best distribution of memory.
•Memory is moved to wherever it is required.





GUIDELINES


•Automatic Shared memory management is configured by setting the SGA_TARGET initialization parameter. If the parameter is set to the default value of 0, then automatic shared memory management is disabled. If set to a non-zero value then Oracle automatically sizes of the shared pool, the large pool, the java pool and the Database buffer cache accordingly.

•Within the SGA the buffers whose sizes can be configured manually are the Log Buffer (LOG_BUFFER), Keep and Recycle Pools (DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE), A streams pool (STREAMS_POOL_SIZE) which is new Oracle 10g , fixed SGA and other internal allocations and caches to accommodate non-standard tablespace blocks (DB_nk_CACHE_SIZE, n=2,4,8,16,32).

•The initialization parameter STATISTICS_LEVEL must be set to TYPICAL or ALL.

•You can query the values of the auto-tuned parameters from the V$PARAMETER dynamic performance view. If you set a value for the SGA_TARGET and do not specify sizes for the auto-tuned size parameters, the value that is displayed by the V$PARAMETER view against these parameters is 0.

•The SGA_TARGET should be less than the SGA_MAX_SIZE initialization parameter. It can be reduced upto a minimum value defined for an individual auto-tuned component. Any change you make to the SGA_TARGET only affects the auto-tuned initialization parameters. You can dynamically increase the SGA_TARGET to the absolute maximum specified by adjusting the value of SGA_MAX_SIZE.

•You can disable automatic shared memory tuning by setting the SGA_TARGET to value of 0. If this is done, the values taken by the individual auto-tuned memory components freeze to their current sizes. They do not take any defaults or previous values.

•When an auto-tuned parameter is resized, the resize results in a change to the size of the component only if the new value is larger than the present size of the component. If you change JAVA_POOL_SIZE to 2G. This change will take effect immediately ensuring there is always 2G for this parameter, however if you reduce to 1G, where the current size is 1.5G. The change may not occur immediately, and the memory tuning algorithm can reduce to this value if it needs to.




CONFIGURING AUTOMATIC SHARED MEMORY MANAGEMENT.



You can configure Automatic Shared Memory Management either:
•Manually
•Using Enterprise Manager Console
•The Enterprise Manager can be used to configure Automatic Shared Memory Management. To do so, select the Database -> Administration Tab -> Memory Parameters -> SGA Tab -> Enable button. Specify the total SGA size in Megabytes.

•The ALTER SYSTEM command may be used to configure Automatic Shared Memory Management manually.

SQL> ALTER SYSTEM SET SGA_TARGET = 500M SCOPE=spfile;

Also modify the initialization parameter listed below to zero, or completely remove them from the parameter file.

SHARED_POOL_SIZE=0
LARGE_POOL_SIZE=0
JAVA_POOL_SIZE=0
DB_CACHE_SIZE=0
DATA DICTIONARY

Once configured you can check the sizes with the following:

SQL> SELECT CURRENT_SIZE FROM V$BUFFER_POOL;

Display output

SQL> SELECT POOL, SUM(BYTES)/1024/1024 Mbytes FROM V$SGASTAT GROUP BY POOL;

The total sum of values of the above two queries will be the same as the value you set for SGA_TARGET.