Automatic Shared Memory Management in Oracle 10g ( ASMM)
OVERVIEW:
It used to be a challenge to size the various memory pools that comprise the Oracle system global area (SGA) for optimal performance. In real world most of the Oracle Databases are of Hybrid type. They are OLTP in day time when the accessibility is high and in the night time, many batch jobs run on the same database to extract data or to run big reports. This will be a combination of the OLTP, OLAP, DSS and HYBRID type of database. Since various types of applications require the underlined memory pools differently, and since the workload can vary minute by minute, manually resizing the SGA components could feel like a never-ending task.
If there is not enough free SGA to allocate for a specific pool when needed, the database will give out-of-memory error, such as
ORA-04031: unable to allocate …
The requirement for the database varies from time to time. During OLTP operation (when most of the users accessing the data) the response time should be minimum. During night time the turn around time should be short when response time is not so important.
The combination of these kinds of requirement makes the task of the DBA a little bit tougher. DBA needs to set the SGA in such a way that demands meet in a balance. But this is tough job. To ease such issue, Oracle has come up with Automatic Shared Memory Management feature in Oracle 10g which will adjust the requirement of the memory component as per demand of the system.
Oracle Database 10g, ASMM automatically sizes many of the memory pools while the database is running, allocating and de-allocating memory as needed. As the workload composition changes, Oracle Database 10g enlarges the appropriate pools and reduces the sizes of other automatically sized pools accordingly. In short, ASMM can save the DBA a lot of trouble—and improve overall performance as well.
The main advantage ASMM is that Oracle Database server itself will adjust the following SGA parameters as per the requirement of the system.
· DB_CACHE_SIZE
· JAVA_POOL_SIZE
· LARGE_POOL_SIZE
· SHARED_POOL_SIZE
Remaining components of the SGA are not adjustable by this method. They need to be manually adjusted. They are
- LOG_BUFFER
- DB_KEEP_CACHE_SIZE
- DB_RECYCLE_CACHE_SIZE
- DB_nK_CACHE_SIZE (n = 2, 4, 8, 16, 32, and nk is non-standard Data block size.))
- STREAMS_POOL_SIZE (In Oracle 10g Release 2. STEAMS_POOL_SIZE can be automatically managed.)
The ASSM feature will lessen the involvement of DBA to set the different SGA components from time to time based on the demand of the workload. It will improve the performance of the database also reduce the cost factor by elimination of adding additional System resource (Memory, etc).
The STATISTICS_LEVEL initialization parameter must be set to TYPICAL (the default) or ALL. That sets the statistics collection level of the database. (The other value of this parameter is BASIC, which will not allow changing the memory pools automatically.)
Oracle 10g has introduced a new parameter by name SGA_TARGET.
DBA has to set up the SGA_TARGET to a non-zero value that will enable the automatic memory allocation. The SGA_TARGET can be configured dynamically by issuing “Alter system” command or by setting that in Parameter file.
DBA needs to set the below mentioned memory parameters to 0 in the parameter file or remove them completely from the parameter file.
DB_CACHE_SIZE = 0
JAVA_POOL_SIZE = 0
LARGE_POOL_SIZE = 0
SHARED_POOL_SIZE =0
DBA can also do resizing of the autotuned pools to 0, as follows:
SQL> alter system set sga_target=160M scope=both;
System altered.
SQL> alter system set db_cache_size=0;
System altered.
SQL> alter system set shared_pool_size=0;
System altered.
……..
The ORACLE database will share that memory into separate component as mentioned below. The individual pools within the SGA will be dynamically configured based on the workload. From time to time, based on the composition of the work load, the pools will expand to accommodate the increase in demand or shrink to accommodate the expansion in another pool. This expansion or contraction occurs automatically without the DBA's intervention.
· DB_CACHE_SIZE
· JAVA_POOL_SIZE
· LARGE_POOL_SIZE
· SHARED_POOL_SIZE
There are few components of SGA that can’t be automatically tuned; those components need to be set manually. Manually sized components consume memory from the target value first, and then the remainder of SGA memory is spread across the various auto sized pools. In other words, if DBA sets parameter values for any of these below mentioned manually tuned pools, Oracle Database 10g subtracts their sizes from SGA_TARGET first, before allocating memory to the automatically allocated pools.
- DB_KEEP_CACHE_SIZE
- DB_RECYCLE_CACHE_SIZE
- DB_nK_CACHE_SIZE (n = 2, 4, 8, 16, 32 and nk is non-standard Data block size)
- LOG_BUFFER
- STREAMS_POOL_SIZE (In Oracle 10g Release 2. STEAMS_POOL_SIZE can be automatically managed.)
The SGA_TARGET can’t cross the size of SGA_MAX_SIZE. SGA_MAX_SIZE can’t be resized dynamically. To reset the SGA_MAX_SIZE, the Oracle Instance needs to be renounced.
Setting a Minimum Value for variable pools:
If DBA doesn't set the values for each of the automatically tuned pools to zero after switching to ASMM, whatever value DBA had set for a parameter will function as a lower threshold value for that pool—ASMM won't go below a nonzero value, even if it needs the memory for another automatically tuned pool.
Suppose, DBA has set up the following parameters in parameter file after switching to ASSM feature.
LARGE_POOL_SIZE = 16M
JAVA_POOL_SIZE = 8 M
In that case, LARGE_POOL_SIZE will never go below 16 MB and JAVA_POOL_SIZE will never go below 8MB.
DBA can also do that by dynamically using ALTER SYSTEM as shown below.
alter system set large_pool_size = 16M; alter system set java_pool_size = 8M;
Of course, these pools are still governed by Automatic Shared Memory Management—their sizes will shrink or expand based on demand. The values DBA has specified explicitly put a lower limit on the pool size; they will never sink below this limit.
