Incorrect MEMORY_MAX_TARGET (> Available RAM) Can Lead To Database Hangs

In this Document

APPLIES TO:

SYMPTOMS

Database is running very slow and appears to hang.

CHANGES

Memory_max_target set much higher than available RAM

CAUSE

Memory_max_target set outside of physical RAM causing Oracle to spill over to Virtual Memory, thus slowing system down.

SOLUTION

1. Best practice is to never allocate more to the SGA and PGA than total RAM.  It is suggested to allocate between 50% - 75% of RAM to the SGA+PGA. This leaves memory for the OS and background processes to utilize. Please adapt for the total SGA+PGA size if multiple instances are running.

2. When you set memory_max_target to 33GB, Oracle tries to allot that memory on startup and spills into virtual memory or swap.

3. An instance spilling into virtual memory causes paging and dramatically slows the performance of Oracle.

4. You also need to unset sga_max_size to properly implement AMM in 11g.  By default DBCA sets sga_max_size equal to memory_max_target during installation.  This is not always the best configuration and can lead to ORA-4030 or ORA-4031 errors.


--------------------------------------------------------------------------
Oracle Database Performance Tuning Guide
11g Release 2 (11.2)
Part Number E10821-05

7.1.6.2 Fit the SGA into Main Memory

Because the purpose of the SGA is to store data in memory for fast access, the SGA should be within main memory. If pages of the SGA are swapped to disk, then the data is no longer quickly accessible. On most operating systems, the disadvantage of paging significantly outweighs the advantage of a large SGA.

Note:
You can use the LOCK_SGA parameter to lock the SGA into physical memory and prevent it from being paged out. The database does not use the MEMORY_TARGET and MEMORY_MAX_TARGET parameters when the LOCK_SGA parameter is enabled.

To see how much memory is allocated to the SGA and each of its internal structures, enter the following SQL*Plus statement:

SHOW SGA
---------------------------------------------------------------------------
Oracle Database Reference
11g Release 2 (11.2)
Part Number E10820-04

SGA_MAX_SIZE
Property Description
Parameter type Big integer
Syntax SGA_MAX_SIZE = integer [K | M | G]
Default value Initial size of SGA at startup, dependent on the sizes of different pools in the SGA, such as buffer cache, shared pool, large pool, and so on.
Modifiable No
Range of values 0 to operating system-dependent

SGA_MAX_SIZE specifies the maximum size of the SGA for the lifetime of the instance.

When either MEMORY_TARGET or MEMORY_MAX_TARGET is specified, the default value of SGA_MAX_SIZE is set to the larger of the two parameters. This causes more address space to be reserved for expansion of the SGA. On 32-bit platforms, this can lead to a substantial reduction in address space usable for PGA. On these machines, workloads like data mining applications (where a single process uses large amounts of PGA) may result in error ORA-04030 with larger MEMORY_TARGET values.
---------------------------------------------------------------------------
Oracle Database Concepts
11g Release 2 (11.2)
Part Number E10713-05

Table 18-1 Memory Management Methods
---------------------------------------------------------------------------
Oracle Database Readme
11g Release 2 (11.2)
Part Number E11015-06

37.3 Oracle Database Reference

The text for the SGA_MAX_SIZE initialization parameter in the Oracle Database Reference should include the following:

When either MEMORY_TARGET or MEMORY_MAX_TARGET is enabled, SGA_MAX_SIZE default value is set to the larger of the two specified parameters on non-Windows 32-bit operating system. On Windows 32-bit operating system, due to address space pressure, the default value of SGA_MAX_SIZE is calculated based on the total available virtual address space along with MEMORY_TARGET and MEMORY_MAX_TARGET.
To calculate the value on Windows 32-bit, SGA_MAX_SIZE larger of:

60% of MEMORY_TARGET if set and
60% of MEMORY_MAX_TARGET if set and
25% of total available virtual address space
---------------------------------------------------------------------------
Oracle Database Administrator's Guide
11g Release 2 (11.2)
Part Number E10595-07

SGA and Virtual Memory

For optimal performance in most systems, the entire SGA should fit in real memory. If it does not, and if virtual memory is used to store parts of it, then overall database system performance can decrease dramatically. The reason for this is that portions of the SGA are paged (written to and read from disk) by the operating system.

See your operating system documentation for instructions for monitoring paging activity. You can also view paging activity from the Performance property page of the Host page of Enterprise Manager.

If you do not specify SGA_MAX_SIZE, then Oracle Database selects a default value that is the sum of all components specified or defaulted at initialization time. If you do specify SGA_MAX_SIZE, and at the time the database is initialized the value is less than the sum of the memory allocated for all components, either explicitly in the parameter file or by default, then the database ignores the setting for SGA_MAX_SIZE and chooses a correct value for this parameter.

Choose the value for MEMORY_TARGET that you want to use.

This can be the minimum value that you computed in step 2, or you can choose to use a larger value if you have enough physical memory available.

If neither is set, they will be auto-tuned without any minimum or default values. We will have a policy of distributing the total memory set by memory_target parameter in a fixed ratio to the the SGA and PGA during initialization. The policy is to give 60% for sga and 40% for PGA at startup.
---------------------------------------------------------------------------

Note:781630.1 - How to configure ASMM (Auto Memory) on 10g and 11g - Best Practices 
Note:223730.1 - Automatic PGA Memory Management
Note:443746.1 - Automatic Memory Management(AMM) on 11g
Unpublished Note:452512.1 - 11g MEMORY_TARGET Parameter Dependency
---------------------------------------------------------------------------

If you wish to monitor the decisions made by Automatic Memory Management following views can be useful

* V$MEMORY_DYNAMIC_COMPONENTS has the current status of all memory components
* V$MEMORY_RESIZE_OPS has a circular history buffer of the last 800 SGA resize requests

REFERENCES