认真就输

In this Document

APPLIES TO:

Oracle Database – Enterprise Edition – Version 11.1.0.6 and later
Information in this document applies to any platform.

PURPOSE

The purpose of this document is to demonstrate how Automatic Memory Management (AMM) manages memory in the SGA and PGA.

SCOPE

The intended audience of this document is experienced Database Administrators and Oracle Support engineers interested in Automatic Memory Management.

DETAILS

Automatic Memory Management (hereafter called AMM) is introduced in Oracle11g to automatically manage both the SGA and PGA for a database instance. It is an extension of Automatic Shared Memory Management (ASMM) which was introduced in Oracle10g, and which manages the SGA only.

The significant instance parameters used by AMM are:

• MEMORY_MAX_TARGET
• MEMORY_TARGET
• SGA_MAX_SIZE
• SGA_TARGET
• PGA_AGGREGATE_TARGET

AMM functionality is implemented by the Memory Manager process (hereafter called MMAN).

Mandatory parameters for AMM

AMM is enabled by the use of EITHER of these parameters:

• MEMORY_TARGET – defines the outer limits to which the sum of the SGA and PGA can grow.
• MEMORY_MAX_TARGET – defines the outer limit to which the MEMORY_TARGET can be manually, dynamically, extended (i.e. without a database restart).

Regardless of the Operating System used, when the instance starts up, an amount of memory equal to MEMORY_MAX_TARGET will be locked by MMAN.

MEMORY_MAX_TARGET will always be set, either explicitly in the parameter file, or implicitly by the MMAN background process and will always define the memory locked by the database instance. If MEMORY_MAX_TARGET is not set in the parameter file, it defaults to MEMORY_TARGET.

Optional parameters for AMM

All SGA memory parameters can be set in an AMM environment. If no SGA memory parameters are set, MMAN defaults in the following ratio:

60% to SGA_TARGET
40% to PGA_AGGREGATE_TARGET

Let’s look at the following parameters:

• SGA_MAX_SIZE: this parameter sets the upper limits of the SGA within MEMORY_TARGET
• SGA_TARGET: this parameter sets the lower limits for the SGA within MEMORY_TARGET
• PGA_AGGREGATE_TARGET: this parameter is just a target for the total private memory the instance will allow for all processes. In AMM, this is a movable target and will slide up ad down as free space in MEMORY_TARGET is available and as the processing needs change.

The sum of SGA and ‘used‘ PGA cannot exceed MEMORY_TARGET. The exception is PL/SQL collections (such as VARRAYs and other collection types) which will not honor the PGA_AGGREGATE_TARGET, but will continue to consume memory as long as more memory is requested by the procedure, and more memory is available on the server. This is memory in excess of MEMORY_MAX_TARGET.

If SGA_TARGET is explicitly set in the parameter file, it becomes the lower limit (minimum size) for the SGA. The PGA_AGGREGATE_TARGET will always get an amount of memory equal to MEMORY_TARGET – SGA_TARGET.

How to control the memory under MEMORY_TARGET?

There are some options available to the Database Administrator to determine how memory is allocated to the various memory components.

Give MMAN complete control

You can consider MEMORY_TARGET as one big area of memory for MMAN to use. When you set no other parameters than MEMORY_TARGET, you give MMAN complete control of the area. MMAN divides this area in a ratio or 60% to SGA and 40% to PGA. But these are only initial settings, and MMAN will freely move memory between the two areas depending on memory pressures. Within the 60%, MMAN will resize the SGA components as it did under ASMM, but the difference is MMAN will now increase SGA_TARGET if the SGA is in need of more memory. Under ASMM, SGA_TARGET, once breached, would give a ORA-4031.

Take control of memory yourself

Recall that MEMORY_TARGET manages both SGA and PGA memory – and without limitations, MMAN will freely move memory back and forth between the two. If you wish to constrain aggressive PGA growth, set SGA_TARGET in the parameter file, and this now becomes a minimum size for the SGA. In this case PGA growth cannot shrink the SGA beyond this size.

If you want to constrain aggressive SGA growth, set SGA_MAX_SIZE in the parameter file, and this becomes the ceiling for the SGA. An amount of memory equal to SGA_MAX_SIZE is carved out of MEMORY_TARGET, and the SGA must fit within it. This limits the amount SGA can grow to.

Any of the SGA components can be set if you wish to make sure you have a certain minimum amount of memory for that component. The significant SGA components are:

• the shared pool (managed by the SHARED_POOL_SIZE instance parameter)
• the buffer cache (managed by the DB_CACHE_SIZE instance parameter)
• the Streams pool (managed by the STREAMS_POOL_SIZE instance parameter)
• the Java pool (managed by the JAVA_POOL_SIZE instance parameter)
• the large pool (managed by the LARGE_POOL_SIZE instance parameter)

If you wish to make any component larger (e.g. the buffer cache in order to maximize transaction throughput and minimize I/O), set the corresponding component parameter (DB_CACHE_SIZE for the example given) to the value you require. But remember, the new component size has to fit within the SGA_TARGET along with all other pools. So either MMAN will adjust SGA_TARGET to a larger value in case you did not set SGA_TARGET explicitly, or, you will have to reset SGA_TARGET manually to a larger value to accommodate for the component enlargement.

AMM details

One of the most significant changes of AMM, is that memory that was originally shared (SGA), can be released, and used by the Operating system for private memory (PGA). The reverse is also true: memory that was private to a process, can be released and used as shared memory. This movement of memory is contained within MEMORY_TARGET and managed by MMAN.

There are some important consequences of this. On a system with many large SQL statements, for example parallel executions, processes often do large sorts and table joins. These operations are memory intensive, and can consume a lot of the free memory under MEMORY_TARGET. As more of these processes start up, and need more private memory, MMAN will turn to the SGA in an attempt to reallocate shared memory as private memory.

Under the covers, if the PGA needs more memory, MMAN will run through the SGA free lists looking for memory chunks that are not currently in use. MMAN coalesces these free chunks, until it creates one contiguous chunk that is the size of a granule. The granule is then unlocked from the SGA, and put back in circulation for the OS to use as private memory for the PGA. Various Operating System functions are invoked to unlock the shared memory and return it to the O/S. These memory lock and unlock routines may differ by hardware platform. Running truss (or any similar utility) may show the O/S routines being used:

If you consider that the memory eligible for PGA is approximately MEMORY_TARGET minus the SGA_TARGET, it becomes desirable to put a lower limit on SGA_TARGET. This prevents the PGA from taking too much memory from the SGA. The lower limit is set when the parameter file contains a value for SGA_TARGET. Without this setting, MMAN will continue to try to take any available memory from the SGA, as long as that memory is not currently in use (pinned).

On Linux the behavior of AMM can be demonstrated easily; when AMM is in use on Linux, the entire amount of MEMORY_TARGET creates /dev/shm segments when the instance starts up. This is visible if you run the following command:

For the SGA portion of MEMORY_TARGET, there is an equivalent amount of /dev/shm segments created. This appears as /dev/shm segments each the size of 1 granule. The PGA (or free memory) portion of memory_target appears as /dev/shm segments of 0 length.

For example, if MEMORY_MAX_TARGET=1G, the granule size is 4M, and there would be 256 /dev/shm segments created (256 * 4M = 1G). If there are no other memory parameters set, the initial SGA size will be 612M (60% of MEMORY_TARGET) and the initial PGA will be 412M (40% of MEMORY_TARGET). So there would be 153 /dev/shm segments with size of 4M (153 * 4M = 612M). The remaining /dev/shm segments will be zero length and represent free memory. These allocation can be seen if you query the V$SGAINFO fixed table. See the case studies that follow:

If you dynamically resize the SGA (using an ALTER SYSTEM SET SGA_TARGET command), you will not see an immediate change in the number of /dev/shm segments, nor in V$SGAINFO. The changes will only occur when memory pressures dictate that the SGA or PGA needs to give up memory.

As the database instance matures and workloads differ, these values will all change. If SGA grows, the number of /dev/shm segments of size 1 granule will increase with a corresponding decrease in the number of /dev/shm segments of 0 length.

For other platforms, the O/S mechanism for moving shared memory between SGA and PGA is different, and will not be discussed here.

Perhaps the best way to show the memory management is by means of examples, and this document will show a few cases below.

CASE 1: Only MEMORY_TARGET is set

The following instance parameters are set:

• MEMORY_TARGET=1G

In this case MEMORY_MAX_TARGET is not set, so it defaults to MEMORY_TARGET.

The following SQL statements highlight what is happening:

Summary:

1. Initially all memory (1GB) is assigned to shared memory. Here V$SGAINFO shows that ‘Maximum SGA size’ is 1GB. This is because SGA_MAX_SIZE is unset, and therefore defaults to MEMORY_TARGET.
2. V$SGAINFO also shows that 412M is free memory. This free memory can be used either for the PGA, or for resizing the SGA.
3. The initial ratio of memory allocation is 60% to SGA, 40% to PGA. This is the default if neither SGA_TARGET nor PGA_AGGREGATE_TARGET is set. This means, of the 1GB of MEMORY_TARGET, SGA got 60% (612M) and free memory was 40% (412M).
4. The V$MEMORY_DYNAMIC_COMPONENTS output shows SGA_TARGET was set to 612M and PGA_AGGREGATE_TARGET was set to 412M. The PGA_AGGREGATE_TARGET value of 412M does not mean there is already 412M of PGA, only that there is enough free memory for it to grow to 412M. If it needs to grow beyond 412M, MMAN will request memory from the SGA.

CASE 2: MEMORY_TARGET, SGA_TARGET and PGA_AGGREGATE_TARGET are set

The following instance parameters are set:

• MEMORY_TARGET=1G
• SGA_TARGET=300M
• PGA_AGGREGATE_TARGET=100M

In this case MEMORY_MAX_TARGET is not set, so it defaults to MEMORY_TARGET.

The following SQL statements highlight what is happening:

Summary:

1. Initially all memory (1GB) is assigned as shared memory. Here V$SGAINFO shows ‘Maximum SGA size’ is 1G. This is because SGA_MAX_SIZE is unset, so it defaults to MEMORY_TARGET.
2. V$SGAINFO also shows that 724M is free memory. This amount is the difference between the MEMORY_TARGET and the SGA_TARGET. This free memory can be used either for the PGA, or for resizing the SGA.
3. The V$MEMORY_DYNAMIC_COMPONENTS fixed table shows SGA_TARGET was set to 300M as requested, but PGA_AGGREGATE_TARGET was set to 724M, which is the remainder of the MEMORY_TARGET allocation. The PGA_AGGREGATE_TARGET value of 724M does not mean there is already 724M of PGA, only that there is enough free memory to grow to 724M.
4. Because SGA_MAX_SIZE was defaulted to 1G, in theory, this is also the maximum size of the SGA (if there was no PGA).

CASE 3: MEMORY_TARGET, SGA_MAX_SIZE, SGA_TARGET and PGA_AGGREGATE_TARGET are set

The following instance parameters are set:

• MEMORY_TARGET=1G
• SGA_MAX_SIZE=500M
• SGA_TARGET=300M
• PGA_AGGREGATE_TARGET=100M

In this case MEMORY_MAX_TARGET is not set, so it defaults to MEMORY_TARGET.

The following SQL statements highlight what is happening:

Summary:

1. Now that SGA_MAX_SIZE is set to 500M, the memory assigned as shared memory, shows up in V$SGAINFO as 500M under ‘Maximum SGA Size’.
2. The ‘Free SGA Memory Available’ in V$SGAINFO is the difference between the SGA_MAX_SIZE and SGA_TARGET (500M-300M = 200M).
3. The memory above SGA_MAX_SIZE (500M) is unavailable to the SGA so never shows up in V$SGAINFO.
4. The V$MEMORY_DYNAMIC_COMPONENTS fixed table shows the SGA_TARGET is 300M as set in the parameter file, but the PGA_AGGREGATE_TARGET is 724M, which is the difference between MEMORY_TARGET and SGA_TARGET.

   So the PGA has access to the memory above SGA_MAX_SIZE (500M) plus any free memory in the SGA (200M).

Summary of case studies

The above examples show that:

• In AMM, shared memory is available for use by both the SGA and PGA.
• MMAN will lock, unlock, and transfer memory freely between the SGA and the PGA as necessary.
• The exceptions are:
• When SGA_MAX_SIZE is set in the parameter file, MMAN will not transfer memory into the SGA that would cause it to exceed this size.
• When SGA_TARGET is set in the parameter file, MMAN will not transfer any memory out of the sga that will cause it to go below this size.

ORA-4030 and ORA-4031

How can the database run out of memory, if it can freely move memory around between SGA and PGA?

Recall that ORA-4030 occurs when there is no more memory for the PGA, and ORA-4031 occurs when there is no more memory for the SGA.

If the SGA allocation is totally consumed, and the PGA (free memory) is totally consumed, then you have run out of memory and either ORA-4030 or ORA-4031 is imminent. This means the MEMORY_TARGET is undersized.

Some things that can initiate memory shortages are limits set on the SGA:

• If SGA_MAX_SIZE is set, this is an upper limit. If the processing load requires more memory than this, you can get ORA-4031.
• If SGA_TARGET is set, this is a minimum size for the SGA. If the processing load requires a lot of private memory (PGA), and that memory requirement exceeds MEMORY_TARGET – SGA_TARGET, you can get a ORA-4030. But if the process needing the memory is PL/SQL, it can take additional free memory from the server. However, it is still a good idea to set SGA_TARGET so there is always some memory available for the SGA.

For ORA-4030, there are also O/S limits that come into play. Notably, the 4G limits on 32-bit platforms, and the ulimit soft settings for UNIX type systems which limit the maximum memory a process can access.