Autmatic Memory Management AMM
The basic memory structures associated with Oracle Database include:
System Global Area (SGA)
The SGA is a group of shared memory structures, known as SGA components that contain data and control information for one Oracle Database Instance. The SGA is shared by all server and background processes.
Program Global Area (PGA)
A PGA is a memory region that contains data and control information for a server process. It is a nonshared memory region created by Oracle when a server process is started. Access to the PGA is exclusive to the server process that was instantiated. There is one PGA for each server process. Background processes also allocate their own PGAs and the total PGA memory allocated for all background and server processes attached to an Oracle Instance is referred to as the total Instance PGA memory and the collection of the individual PGAs is referred to as the total instance PGA or just Instance PGA.
This memory region is the one that contains the global variables, data structures and control information for a server process. An example of this type of data is the runtime area of a cursor. Each time that a cursor s executed a new runtime area is generated for that cursor in the PGA memory region of the server process mapped to that cursor.
Performance of complex long running queries typical of PeeopleSoft depend to a large extent on the memory available in the Program Global Area (PGA) which is known as the work area.
Below is a figure that attempts to illustrate the relationships among Oracle memory structures:
Evolution of Memory Management Features
Memory Management has evovled with each new Oracle Database version.
Oracle 9i
Beginning with Oracle 9i which introduced the dynamic SGA infrastructure that allowed for the sizing of the Buffer Cache, Shared Pool and the Large Pool without having to shutdown and restart the Oralce Database. Key features introduced in this version:
- Dynamic Memory resizing
- DB_CACHE_SIZE instead of DB_BLOCK_BUFFERS
- DB_nK_CACHE_SIZE for multiple block sizes
- PGA_AGGREGATE_TARGET this is the initial version of Automatic PGA Memory Management
Oracle Database 10g
Automatic Shared Memory Management known as ASMM was introduced in Oracle version 10g. You have the option of enabling automatic shared memory management feature by setting the SGA_TARGET parameter to a non-zero value. The degree of how much control you give Oracle for ASMM is control by supplying zero values for several key Oracle initialization parameters. You can permit Oracle to have full control or you can elect to manually control the minimimum size of several memory structures.
Oracle Database 11g
Automatic Memory Management (AMM) was introduced in Oracle verion 11g. Note the missing “S” in the acronym. The new Oracle initialization parameters were created for use with AMM – MEMORY_MAX_TARGET and MEMORY_TARGET.
The most important SGA components are described below:
Notes: RESULT_CACHE_MAX_SIZE is a new parameter that was introduced as part of the 11g Memory Architecture. The result cache is comprised of the SQL query result cache and PL/SQL function result cache, which share the same memory structures. Results of queries and query fragments can now be cached in memory in the SQL query result cache. The database can then use those cached results to answer future executions of those queries and query fragments. Similarly the PL/SQL Function Result can also be cached.
In order to these features you have to set the Oracle Initialization Parameter – RESULT_CACHE_MODE which determines the behavior of the SQL query result cache behavior. The possible values are MANUAL and FORCE.
Oracle Database 11g supports various memory management methods, which are set by choosing values for the Oracle initialization parameter settings. Oracle recommends that you use the Automatic Memory Management method and the following lists illustrates their recommendations:
1. Automatic Memory Management – for both the SGA and Instance PGA
2. Automatic Shared Memory Management – for the SGA
3. Manual Shared Memory Management – for the SGA
4. Automatic PGA Memory Management – for the Instance PGA
5. Manaual PGA Memory Management – for the Instance PGA
1. Automatic Memory Management – for both the SGA and Instance PGA
Starting with Oracle 11g, Oracle can now manage both the SGA memory and the instance PGA memory completely automatically. You set the value for the total memory size that is to be dedicated to the Oracle Instance and Oracle takes that value and dynamically sets the values for the various memory structures and manages the exchange of memory between the SGA and the Instance PGA as needed to meet processing demands. This capability is referred to as Automatic Memory Management. By using this method the database also dynamically tunes the sizes of the individual SGA components and the sizes of the individual PGAs.
Two new parameters were introduced in Oracle 11g named MEMORY_MAX_TARGET and MEMORY_TARGET. You set only a target memory size (MEMORY_TARGET) and optionally a maximum memory size initialization parameter (MEMORY_MAX_TARGET).
If you create a database with the Database Configuration Assistant (DBCA) and choose the basic installation option, automatic memory management is enabled. If you choose advanced installation, the DBCA enables you to select memory management.
Switching to Automatic Memory Management
1. Check the current values configured for SGA_TARGET and PGA_AGGREGATE_TARGET.
In SQL*Plus connect as SYSDBA and display the following values for SGA_TARGET and PGA_AGGREGATE_TARGET:
SQL> show parameter target
archive_lag_target
db_flashback_retention_target
fast_start_io_target
fast_start_mttr_target
memory_max_target
memory_target
pga_aggregate_target
sga_target
Add the values together for pga_aggregate_target and sga_target.
2. Decide on a maximum amount of memory that you want to allocate to the database which will determine the maximum value for the sum of the SGA and instance PGA sizes.
3. Change the parameter in the Oracle initialization parameter file using spfile and for our example 1800M for MEMORY_MAX_TARGET and 400M for the PGA_AGGREGATE_TARGET.
SQL>
alter system set memory_max_target = 1800M scope=spfile;
alter system set memory_target = 1800M scope=spfile;
alter system set sga_target = 0 scope=spfile;
alter system set pga_aggregate_target = 0 scope=spfile;
If you have decided to not implement the spfile parameter file and still use a pfile (which you should move away from) then edit the initORCLSID.ora and set the parameters manually:
MEMORY_MAX_TARGET=1800M
MEMORY_TARGET=1800M
SGA_TARGET=0
PGA_AGGREGATE_TARGET=0
In the event that you do not specify any value for MEMORY_MAX_TARGET and decide to only use the MEMORY_TARGET then Oracle automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET.
If you omit the MEMORY_TARGET and include a value for MEMORY_MAX_TARGET, the MEMORY_TARGET parameter parameter defaults to zero. After startup you can then change the value for MEMORY_TARGET to a non-zero value and provide that the number does not exceed the value of MEMORY_MAX_TARGET this allows you to dynamically set the value for MEMORY_TARGET without having the restart Oracle.
MEMORY_MAX_TARGET however is not a dynamic parameter, it is a static one and if you desire to change its value then you will have to bounce the Oracle DB to affect the change.
4. Shutdown and startup the database:
SQL> shutdown immediate;
startup mount
show parameter
The above steps suggest setting the values for SGA_TARGET and PGA_AGGREGATE_TARGET to zero so that the sizes of the SGA and Instance PGA are tuned up and down dynamically as Oracle determines they need to be without restrictions. You can omit the statements that set these parameter values to zero and leave either or both of the values as positive numbers. If you do this then these values act as minimum values for the sizes of the SGA or Instance PGA.
Note: In the case you set any parameter value to a value which is higher than MEMORY_TARGET, then you will get the following error when attempting to start up Oracle:
Monitoring and Tuning Automatic Memory Management
The dynamic performance view v$memory_dynamic_components displays the current sizes of all dynamically tuned memory components including the total sizes of the SGA and Instance PGA. The view v$memory_target_advice provices tuning advice for the MEMORY_TARGET initialization parameter.
sql> select * from v$memory_target_advice order by memory_size;
You can also query the v$memory_resize_ops which has a circular history buffer of the last 800 SGA resize requests.
3. Automatic Shared Memory Management – for the SGA
If you want to exercise more direct control over the size of the SGA, you can choose not to use AMM (Automatic Memory Management) 11g’s Memory Model and enable Automatic Shared Memory Management (ASMM) 10g’s Memory Model. This feature was introduced in 10g with a parameter known as SGA_TARGET. When automatic SGA memory is enabled, the sizes of the different SGA components are flexible and can adapt to the needs of the current workload without requiring any additional or manual intervention and/or configuration.
If you enabled AMM and you decide you want to switch to ASMM you can follow the steps below:
SQL> alter system set memory_target = 0 scope=both;
SQL> alter system set sga_target = 500M scope=both;
4. Manual Shared Memory Management – for the SGA
To gain complete control over the individual SGA component sizes you can disable both auto automatic memory management and automatic shared memory management. In this mode you have to set the sizes of several individual SGA components, thereby determing the overall SGA size. You then manually tune these individual SGA components on an ongoing basis.
In this case you set SGA_TARGET and MEMORY_TARGET to 0 and set the value for other SGA components up to the value of SGA_MAX_SIZE.
<4. Automatic PGA Memory Management – for the Instance PGA
With using Automatic Memory Management, PGA memory is allocated based upon the value of MEMORY_TARGET. In case you enable Automatic Shared Memory Management (ASMM) or manual Shared Memory Management, you also implicitly enable Automatic PGA Memory Management.
Automatic/Manual PGA Memory Management is decided by the inialization parameter WORKAREA_SIZE_POLICY which is a session- and system-level parameter that can take one of two values MANUAL or AUTO, the default is AUTO.
With Automatic PGA Memory Management, you set a target size for the Instance PGA by defining a value for PGA_AGGREGATE_TARGET and the sizing of the SQL work areas is determined automaitically and all *_AREA_SIZE Oracle Initialization parameters are ignored for these sessions. This feature is available from 9i onwards.
At any given time, the total amount of PGA memory available to active work areas on the instance is automatically derived from the parameter PGA_AGGREGATE_TARGET. This amount is set to the value of PGA_AGGREGATE_TARGET minus the PGA memory allocated for other purposes like session memory. The resulting PGA memory is then alloted to individual active work areas based on their specific memory requirements.
5. Manual PGA Memory Management – for the Instance PGA
In the case where you want to manually specify the maximum work area size for each type of SQL operator such as sort or hash-join then you can enable Manual PGA Memory Management.
To accomplish this set the WORKAREA_SIZE_POLICY value to MANUAL and also specify values for *_area_size such as SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE AND CREATE_BITMAP_AREA_SIZE, etc.
The Oracle 11g Database supports Manual PGA Memory Management but Oracle strongly recommends against using that model and leave Automatic PGA Memory Managemnet to Oracle itself.
The following table summarizes the various Memory Management Models that are available in Oracle 11g:
The Automatic Memory Management (AMM) Model feature uses background processes named Memory Managment (MMAN) which was introduced in Oracle 10g and which participated in Automatic Shared Memory Management (ASMM) using SGA_TARGET. MMAN serves as the SGA Memory Broker and is responsible for coordinating all of the memory activies including the sizing of the memory components. The SGA Memory Broker keeps track of the sizes of the components and maintains the pending memory resize operations and queues.
