14.4. Automatic Memory Management

Oracle performance (and, for that matter, computer performance in general) can be summed up in two basic tenets:

• Memory is fast.

• Disk is slow.

In fact, accessing data from memory tends to be approximately 10,000 times faster than accessing data from disk. So why is disk usage so prevalent if it is so slow? This is because of a couple of other basic tenets:

• Memory is expensive.

• Disk is cheap.

The terms "expensive" and "cheap" carry a deeper connotation than simply the purchase price. They also refer to the availability of the item. In fact, it might be more accurate to say that memory is scarce, while disk is plentiful.

For example, consider Oscar, the avid fisherman. He lives an hour away from the nearest lake, and he is growing tired of the long drive. Oscar decides to buy a house on the lakefront, thereby making access to the lake nearly instantaneous. When he contacts his realtor, however, he is shocked to discover that only one small property is for sale, and the price is astronomical.

Lakefront property is limited and, therefore very expensive. In the same way, servers are limited in the amount of memory that they can physically accommodate. Likewise, CPUs are limited in the amount of memory that they can address. Once these limits are reached on a server, the price of memory is a moot point. No more can be added, just as no new property can be added to the lakefront.

Memory management, therefore, is a critical element in any Oracle environment. If the database has insufficient memory to meet its needs, performance will suffer. Conversely, if too much memory is allocated to a little-used memory structure, valuable resources are wasted.

To make matters worse, Oracle uses a myriad of memory pools, each specific to a particular class of memory allocation requests. For example, the Java pool handles Java-related requests, while the library cache (part of the shared pool) stores SQL statements and PL/SQL procedures and packages. And because memory needs fluctuate constantly as workloads change, monitoring memory allocation and usage could easily become a full-time job.

To meet these challenges, Oracle 10g introduced Automatic Shared Memory Management (ASMM) and Automatic PGA Memory Management (APMM). Instead of allocating memory to a myriad of diverse memory structures, and constantly monitoring those components for changes in demand, Oracle's automatic memory management features greatly simplify the process of memory management.

In the following sections, you will learn how to configure Oracle's ASMM functionality to provide dynamic SGA memory allocation and management for the database. You'll also learn to configure APMM to provide dynamic PGA memory allocation and management.

14.4.1. Oracle Memory Usage

Oracle classifies available memory into one of two memory structures: the System Global Area (SGA) or the Program Global Area (PGA). Within each of these structures, Oracle divides available memory into smaller structures known as pools, each designated to satisfy a particular class of memory allocation request.

In the following sections, you will be given an overview of the SGA and the PGA. You will also learn how Oracle simplifies the management of these memory structures through ASMM and APMM.

14.4.1.1. An Overview of the SGA

The System Global Area (SGA) is the largest single memory structure in an Oracle database instance. It handles the majority of Oracle's memory needs. Internally, the SGA is divided into a series of smaller memory pools such as the database buffer cache, shared pool, large pool, Java pool, streams pool, and the redo log buffer. Each of these pools handles specific types of memory requests, as discussed next.

14.4.1.1.1. Database Buffer Cache

The database buffer cache is a shared memory component that stores data blocks retrieved from datafiles. When an Oracle operation requires a data block from a datafile, it will first check to see if a copy of the block is currently residing in the database buffer cache. If the block is found, the operation does not need to perform a disk read operation to retrieve the block. This situation is referred to as a cache hit, and the I/O operation is classified as a logical I/O (LIO).

If the block is not found in the buffer cache, it is referred to as a cache miss and results in a physical I/O (PIO) operation (a disk read) being performed. As you should well know, LIOs are much faster than PIOs. However, keep in mind that blocks retrieved through LIOs had to have been retrieved by a PIO at some time in the past.

Memory in the database buffer cache is tracked by two internal lists: the write list and the least recently used (LRU) list.

The write list tracks dirty blocks. These are blocks whose data has been modified. When a DML statement is executed, the blocks are modified in the buffer cache, not in the datafile itself. They must be written back to the datafile at some point. The write list keeps track of these blocks until a database writer (DBW0) process can write them back to the datafile.

The LRU list is a FIFO (first in, first out) list used to age out the least recently used blocks from the buffer cache. The LRU list also tracks unused memory buffers in the database buffer cache.

When a block needs to be read into the buffer cache, Oracle must first find a free buffer in which to store the block. The process searches the LRU list, beginning at the least recently used end. It will search until it finds a free buffer. If it cannot find any, it will signal the DBW0 process to flush any dirty blocks back to disk in order to make room. If no dirty blocks exist, the least recently used block will be aged out to make room. The block will then be written to the buffer, and the buffer moves to the most recently used end of the LRU list.

The exception to this rule is when a full table scan operation is performed. Blocks retrieved from a full table scan are added to the least recently used end of the LRU list, so they will be aged out quickly. This is because full table scan blocks are generally scanned quickly and are no longer needed. This functionality can cause problems for small tables that are accessed frequently, such as lookup tables. Small tables are meant to be accessed via full table scans. This is because a full table scan will outperform an index lookup on a very small table.

Because Oracle 10g will put these blocks at the least recently used end of the LRU list, they will age out quickly. As a result, the next time that the table is accessed, Oracle 10g may likely have to perform PIO to retrieve the blocks again. In this situation, you can add the CACHE clause to the table (either via ALTER TABLE or in the CREATE TABLE statements) to circumvent this behavior. You can also choose to pin the table in memory.

14.4.1.1.2. Shared Pool

The shared pool contains the library cache and the data dictionary cache, as well as buffers for parallel execution messages and other control structures.

14.4.1.1.3. Library Cache

The library cache holds shared SQL areas, PL/SQL procedures and packages, and associated control structures such as library cache handles and locks. In a shared server environment, the library cache also holds private SQL areas.

When a SQL statement is parsed, the resultant parse tree and execution plan are stored (along with the statement itself) in a shared SQL area. Therefore, future executions of the statement need not be parsed, but can be executed immediately. The library cache uses a special LRU algorithm to age out shared SQL areas to make room for new entries.

PL/SQL program units are treated similarly to SQL statements within Oracle. A shared area is used to store the compiled and parsed code, while a private area is used to hold session-specific values such as program arguments and variables. SQL statements executed within a PL/SQL program unit will utilize shared SQL areas, just like individual SQL statements.

14.4.1.1.4. Data Dictionary Cache

The data dictionary cache stores rows from data dictionary tables and views. Because it stores rows, rather than blocks, it is also referred to as the row cache.

Because data dictionary objects are queried very frequently, caching data dictionary rows is crucial to performance. Therefore, it was allotted its own cache dedicated solely to data dictionary data.

14.4.1.1.5. Redo Log Buffer

The redo log buffer holds information about all changes made to the database via INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP operations. The information is stored as a series of timestamped records called redo entries. These redo entries are what make point-in-time recovery operations possible.

The redo log buffer holds these changes until the log writer (LGWR) process can write the redo entries to redo log files.

14.4.1.1.6. Java Pool

The Java pool provides memory for all session-specific Java code and data within the Java Virtual Machine (JVM). An internal advisor known as the Java Pool Advisor maintains statistics on Java memory usage, which can be used to manage and tune the Java pool.

14.4.1.1.7. Large Pool

The large pool is an optional memory area that can be allocated to increase performance for the following:

• Shared server environments

• Oracle XA Distributed Transaction Processing

• Oracle backup and restore operations

Memory requests in these situations tend to be larger (several hundred kilobytes, on average). Rather than forcing the shared pool to deal with these requests, the large pool can handle them, and do so more efficiently.

14.4.1.1.8. Streams Pool

The streams pool is an optional pool dedicated to handling memory requests for Oracle streams. Like the large pool, the streams pool can relieve the shared pool of the added overhead of dealing with streams memory requests. However, in environments where Oracle streams functionality is not utilized, the pool is unnecessary.

14.4.1.1.9. Fixed SGA

The fixed SGA area stores information relating to the state of the database instance and is primarily accessed by Oracle's background processes. Dynamic performance data is stored in fixed tables (V$ tables) in this area.

With all of these diverse structures requiring memory allocation and with the constant fluctuations in workload requirements, manual memory management of the SGA is a difficult and time-consuming task. ASMM simplifies these tasks by dynamically sizing SGA elements to ensure that each has the resources needed to meet demand.

14.4.1.2. An Overview of the PGA

A Program Global Area (PGA) stores data and control information for a single server process. Therefore, every session and background process has an associated PGA.

Because PGA memory is specific to a single process, it is not shared with any other process. Internally, each PGA houses two smaller memory structures: the private SQL area and the SQL work area.

Private SQL area

The private SQL area is used to hold runtime memory structures and bind variables for SQL and PL/SQL executed by the process.

SQL work area

The SQL work area provides memory in which SQL operations can be performed. Complex queries often perform memory-intensive operations such as sort operations, hash-joins, bitmap merges, bitmap creates, and bulk load operations. If insufficient memory is available to these operations, they will have to utilize disk for temporary segment storage to complete the operation. There is a huge performance penalty when this situation occurs.

Appropriate sizing of the SQL work area has historically been problematic due to the fact that every process would receive an identical allocation. For example, let's say that your current SORT_AREA_SIZE initialization parameter is set to 64K. You notice that large sort operations seem to take excessive time and suspect (correctly so) that disk sorts are occurring. To remedy the situation, you boost the SORT_AREA_SIZE setting to 4MB. With the new setting, the sorts can be performed in memory and run much faster.

So what's the problem? The problem lies in the fact that this increase in sort space is allocated to every server process, not just to the handful of users who run large sorts. On a relatively busy system, it is not uncommon to have over 500 server processes running. This being the case, 2GB (500 × 4MB) of memory is consumed.

To alleviate PGA memory management issues, Oracle's APMM functionality can be implemented to allow Oracle to automatically manage PGA memory allocation.

14.4.1.3. An Overview of Automatic Memory Management

To alleviate memory sizing issues, Oracle 10g provides two automatic memory management features:

• Automatic Shared Memory Management (ASMM) simplifies configuration of the SGA by allowing you to specify the total size of the SGA without worrying about the underlying memory structures. ASMM handles all the details of monitoring memory usage by the internal structures and allocating memory to each in order to meet demands.

• Automatic PGA Memory Management (APMM) eliminates the problems of PGA sizing by allowing you to specify a single memory allocation for the entire database instance. APMM allocates memory to sessions on an as-needed basis to meet their SQL work area needs.

14.4.1.4. Using ASMM

To take advantage of Oracle's ASMM functionality requires the setting of only a single initialization parameter: SGA_TARGET. The SGA_TARGET parameter specifies the total memory size for the SGA. The memory allocated through this setting is managed by Oracle to meet the needs of all the underlying memory structures.

By default, the SGA_TARGET parameter is set to a value of zero. A setting of zero indicates that ASMM is not enabled for the instance. In this case, Oracle looks to the values set in traditional initialization parameters such as SHARED_POOL_SIZE, JAVA_POOL_SIZE, and so on.

A non-zero value in the SGA_TARGET parameter indicates that ASMM is enabled. It is also imperative that the STATISTICS_LEVEL parameter is set to a value of either TYPICAL (the default value) or ALL. If the STATISTICS_LEVEL parameter is set to value of BASIC, ASMM will not be enabled.

ASMM divides the SGA memory pools into two groups: automatically sized components and manually sized components.

Automatically sized components are SGA memory pools for which ASMM will provide automatic sizing functionality. These are considered by Oracle to be the SGA components that have the most impact on database performance.

The automatically sized components are

• Database buffer cache

• Shared pool

• Large pool

• Java pool

Keep in mind that some of these higher level components also contain subcomponents that are also automatically sized. For example, both the library cache and data dictionary cache are contained in the shared pool; therefore, they are also automatically sized by ASMM.

In previous versions of Oracle, the initialization parameters DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, and JAVA_POOL_SIZE were used to size these SGA components. These parameters are, in fact, still valid initialization file parameters in Oracle 10g. However, the purpose that they serve depends on whether ASMM is enabled or not.

If ASMM is not enabled, these parameters will behave as they did in previous versions of Oracle. This means that the values set for these parameters will define the amount of memory allocated to each component.

However, when ASMM is enabled, a non-zero value in any of these parameters is interpreted as a minimum memory allocation for the component. For example, if the value for the SGA_TARGET parameter is 12G, and the value for the DB_CACHE_SIZE parameter is 4G, ASMM will never shrink the database buffer cache to a size smaller than 4GB, even if the memory would be better utilized elsewhere.

Manually sized components are SGA components that are not automatically sized by ASMM. These components continue to be sized using the traditional initialization parameter settings.

These components are

• Streams pool

• Keep buffer cache

• Recycle buffer cache

• Other caches

Unlike the automatically sized components, these components are sized based strictly on the setting of their associated initialization file parameters. These parameters include

• DB_KEEP_CACHE_SIZE

• DB_RECYCLE_CACHE_SIZE

• DB_nK_CACHE_SIZE (where n= 2, 4, 8, 16, or 32)

• STREAMS_POOL_SIZE

Any memory allocations made to these components are taken from the total memory allocated in the SGA_TARGET parameter. For example, suppose your initialization file contained the following directives:

S6A_TAR6ET=10G
DB_RECYCLE_CACHE_SIZE=1G

The recycle cache would be statically sized at 1GB. That amount is deducted from the total SGA_TARGET amount, leaving 9GB to be allocated to the automatically sized components.

As you have already seen, ASMM can be implemented by simply setting the initialization parameter SGA_TARGET to a non-zero value. This can be done in the init.ora file, or by using the ALTER SYSTEM statement.

ASMM can also be configured through the EM Database Control application by following these steps:

14.4.1.5. Using APMM

APMM functionality is implemented through the use of the PGA_AGGREGATE_TARGET initialization parameter. The memory allocated to APMM through this parameter is dynamically managed by Oracle and allocated to meet the SQL work area requirements of all Oracle sessions.

APMM is always enabled (although the functionality can be overridden). If the PGA_AGGREGATE_TARGET parameter is not explicitly set, it will default to either 10MB or 20 percent of the SGA size, whichever is larger.

Historically, PGA memory allocation has been done through the use of the following initialization parameters:

• SORT_AREA_SIZE

• HASH_AREA_SIZE

• BITMAP_MERGE_AREA_SIZE

• CREATE_BITMAP_AREA_SIZE

In Oracle 10g, these parameters are still valid, although their use is not encouraged. These parameters can be used in conjunction with the WORKAREA_SIZE_POLICY parameter to override the functionality of APMM.

When a query is executed within a session, APMM needs to allocate a SQL work area for the session. Before doing so, it will reference the WORKAREA_SIZE_POLICY initialization parameter to determine how to handle the request. The WORKAREA_SIZE_POLICY parameter can be set to either AUTO or MANUAL. The values of this parameter have the following effect:

AUTO

If the WORKAREA_SIZE_POLICY initialization parameter is set to AUTO, APMM will dynamically allocate memory as needed to fulfill the request. This is the default setting and is highly recommended.

In this situation, the historical PGA memory parameters are ignored completely. They have no meaning.

MANUAL

If the WORKAREA_SIZE_POLICY initialization parameter is set to MANUAL, APMM will allocate memory for the operation based on the value of the corresponding historical PGA memory parameter. For instance, if a hash-join operation is required, the HASH_AREA_SIZE parameter setting will determine the amount of memory allocated.

This setting effectively overrides the APMM functionality. Its use is strongly discouraged by Oracle.

While Oracle discourages overriding APMM functionality, it is worth mentioning that the WORKAREA_SIZE_POLICY parameter can be set at the session level as well as at the instance level. Therefore, it is possible to override APMM for only a single session. This may be useful for specific tasks.

14.4.1.5.1. Implementing APMM

To implement APMM, simply set the total memory allocation for APMM in the initialization file, as in this example:

PGA_AGGRE6ATE_TARGET=2G

The value of PGA_AGGREGATE_TARGET can also be modified dynamically by using the ALTER SYSTEM statement:

SQL> alter system set PGA_AGGREGATE_TARGET = 2G;

System altered.

Either method allocates 2GB of memory to APMM to be shared by all sessions in the instance. For an online transaction processing (OLTP) system, this may be too large of a value. However, for a decision support system (DSS), the SQL work area needs tend to be much greater.

14.4.1.6. Implementing APMM Through EM Database Control

APMM can also be implemented through the EM Database Control application by following these steps: