Oracle Automatic (Shared) Memory Management

Automatic Shared Memory Management

Automatic Shared Memory Management: Overview
Automatic Shared Memory Management (ASMM) simplifies the configuration of the System Global Area (SGA). ASMM uses memory advisor data to evaluate the best memory configuration, and then resizes the amount of memory to be allocated for the database buffer cache, shared pool, Java pool, large pool, and Streams pool by using the dynamic SGA feature. 
ASMM makes more effective use of available memory and thereby reduces the cost incurred for acquiring additional hardware memory resources, and significantly simplifies Oracle database administration with a more dynamic, flexible, and adaptive memory management scheme.
ASMM enables you to specify a total memory amount to be used for all SGA components. The Oracle database server periodically redistributes memory between the components given in the slide according to workload requirements. 
For example, in a system that runs high-concurrency OLTP workload during the day, which requires a large buffer cache, you would have to configure both the buffer cache and the large pool to accommodate your peak requirements. With ASMM, when the OLTP workload runs, the buffer cache is given the required memory to optimize buffer cache access. When the decision support system (DSS) batch job starts up later, the memory is automatically migrated to the large pool so that it can be used by parallel query operations without producing memory overflow errors.

Automatic Shared Memory Management (ASMM) adjusts the sizes of the SGA components on the fly as the workload changes.  Automatic Shared Memory Management (ASMM), a 10g and 11g feature, is meant to simplify SGA management.  Do not confuse ASMM with AMM (Automatic Memory Management).  ASMM was introduced in Oracle 10g.  AMM is new in 11g and will be addressed in this chapter.

The DBA specifies the amount of memory available to an instance via the sga_target parameter.  The Oracle database periodically redistributes memory between the components according to workload requirements.  This solves the allocation issues that are normally faced when using the manual method.  Under-sizing memory settings can lead to poor performance and out-of memory errors (ORA-4031).

If a non-zero value is specified for sga_target, the following six memory pools are automatically sized by Oracle: 

• Database buffer cache (Only the default pool)
• Shared pool
• Large pool
• Java pool
• Streams pool
• Fixed SGA and other internal allocations

When using ASMM, configuration of the following buffers remains manual:

• Log buffer
• KEEP/RECYCLE buffer caches
• Nonstandard block size buffer caches

When sga_target is set, the total size of manual SGA parameters are subtracted from the sga_target value and the balance is given to the auto-tuned SGA components.

Sga_target is also a dynamic parameter and can be changed through Enterprise Manager or with the ALTER SYSTEM command. However, the sga_target can be increased only up to the value of sga_max_size.

Important: statistics_level must be set to TYPICAL (default) or ALL to use Automatic Shared Memory Management.

Program Global Area (PGA)

A Program Global Area (PGA) is a memory region that stores the data and control information for the server processes. Each server process has a non-shared memory region created by Oracle when a server process is started. Access to the PGA is exclusive to that server process, and it is read and written only by Oracle code. Broadly speaking, PGA contains a private SQL area and a session memory area.

A private SQL area contains data such as bind information and runtime memory structures. Each session that issues a SQL statement has a private SQL area.  Session memory is the memory allocated to hold a session’s variables, logon information, and other information related to the session.

With the initialization parameter pga_aggregate_target, sizing of work areas for all dedicated sessions is made automatic, and all *_area_size parameters are ignored for these sessions.

Enabling Automatic Shared Memory Management

To enable automatic shared memory management, set the following initialization parameters:

• STATISTICS_LEVEL to TYPICAL or ALL
• SGA_TARGET to a nonzero valueThe SGA_TARGET parameter can be set to a value that is less than or equal to the value of the SGA_MAX_SIZE initialization parameter. Set the value of the SGA_TARGET parameter to the amount of memory that you intend to dedicate to the SGA.

Disabling Automatic Shared Memory Management

To disable automatic shared memory management, set the value of the SGA_TARGET parameter dynamically to 0 at instance startup.

This disables automatic shared memory management and the current auto-tuned sizes will be used for each memory pool. If necessary, you can manually resize each memory pool, as described in “Sizing the SGA Components Manually“.

Sizing the SGA Components Manually

If the system is not using automatic memory management or automatic shared memory management, then you must manually configure the sizes of the following SGA components:

• Database buffer cache The database buffer cache is sized using the DB_CACHE_SIZE initialization parameter, as described in “Configuring the Database Buffer Cache“.
• Shared pool The shared pool is sized using the SHARED_POOL_SIZE initialization parameter, as described in “Configuring the Shared Pool“.
• Large pool The large pool is sized using the LARGE_POOL_SIZE initialization parameter, as described in “Configuring the Large Pool“.
• Java pool The Java pool is sized using the JAVA_POOL_SIZE initialization parameter.
• Streams pool The Streams pool is sized using the STREAMS_POOL_SIZE initialization parameter.
• IM column store The IM column store is sized using the INMEMORY_SIZE initialization parameter.

The values for these parameters are also dynamically configurable using the ALTER SYSTEM statement.

Before configuring the sizes of these SGA components, take the following considerations into account:

SGA Sizing Unit

Memory for the buffer cache, shared pool, large pool, and Java pool is allocated in units of granules. If the SGA size is less than 1 GB, then the granule size is 4MB. If the SGA size is greater than 1 GB, the granule size changes to 16MB. The granule size is calculated and fixed when the database instance starts up. The size does not change during the lifetime of the instance.

To view the granule size that is currently being used for the SGA, use the V$SGA_DYNAMIC_COMPONENTS view. The same granule size is used for all dynamic components in the SGA.

Maximum Size of the SGA

The maximum amount of memory usable by the database instance is determined at instance startup by the value of the SGA_MAX_SIZE initialization parameter. You can expand the total SGA size to a value equal to the SGA_MAX_SIZE parameter. The value of the SGA_MAX_SIZE parameter defaults to the aggregate setting of all the SGA components.

If the value of the SGA_MAX_SIZE parameter is not set, then decrease the size of one cache and reallocate that memory to another cache if necessary. Alternatively, you can set the value of the SGA_MAX_SIZE parameter to be larger than the sum of all of the SGA components, such as the buffer cache and the shared pool. Doing so enables you to dynamically increase a cache size without having to decrease the size of another cache.

Note:

The value of the SGA_MAX_SIZE parameter cannot be dynamically resized.

Application Considerations

When configuring memory, size the memory caches appropriately based on the application’s needs. Conversely, tuning the application’s use of the memory caches can greatly reduce resource requirements. Efficient use of the memory caches also reduces the load on related resources, such as latches, CPU, and the I/O system.

For optimal performance, consider the following:

• Design the cache to use the operating system and database resources in the most efficient manner.
• Allocate memory to Oracle Database memory structures to best reflect the needs of the application.
• If changes or additions are made to an existing application, resize Oracle Database memory structures to meet the needs of the modified application.
• If the application uses Java, investigate whether the default configuration for the Java pool needs to be modified.

Automatic Memory Management: Overview

With Automatic Memory Management (AMM), the system causes an indirect transfer of memory from SGA to PGA (and vice versa). It automates the sizing of PGA and SGA according to your workload.

This indirect memory transfer relies on the OS mechanism of freeing shared memory. After memory is released to the OS, the other components can allocate memory by requesting memory from the OS.

Currently, this is implemented on Linux, Solaris, HP-UX, AIX, and Windows. Set your memory target for the database instance and the system then tunes to the target memory size, redistributing memory as needed between the system global area (SGA) and the aggregate program global area (PGA).

The slide displays the differences between the Automatic Shared Memory Management mechanism and the Automatic Memory Management.

The simplest way to manage memory is to allow the database to automatically manage and tune it for you. To do so (on most platforms), you have to set only a target memory size initialization parameter (MEMORY_TARGET) and a maximum memory size initialization parameter (MEMORY_MAX_TARGET). Because the target memory initialization parameter is dynamic, you can change the target memory size at any time without restarting the database. The maximum memory size serves as an upper limit so that you do not accidentally set the target memory size too high. Because certain SGA components either cannot easily shrink or must remain at a minimum size, the database also prevents you from setting the target memory size too low.

Oracle Database Memory Parameters

The slide displays the memory initialization parameters hierarchy. Although you have to set only MEMORY_TARGET to trigger Automatic Memory Management, you still have the ability to set lower-bound values for various caches. So if the child parameters are set by the user, they will be the minimum values below which that component is not auto-tuned.

The SGA is made up of several components, some are auto-tuned when ASMM is enabled, some are dynamic, and some are static. When Automatic Memory management is enabled the Automatic Shared Memory Management is also enabled.