5 Managing Database Memory

This section provides background information on memory management in Oracle Database Express Edition and describes how to adjust memory allocation.

The following topics are covered:

Automatic Memory Management
About Memory Management
Example: Changing SGA and PGA Aggregate Sizes (Advanced Users)

Automatic Memory Management

Oracle Database XE uses automatic memory management, which you cannot disable. With automatic memory management in Oracle Database XE, the database dynamically exchanges memory between the System Global Area (SGA) and the instance Program Global Area (PGA) as needed to meet processing demands. The database also dynamically tunes the sizes of the individual SGA components and the sizes of the individual PGAs.

For an overview of Oracle Database memory management, including the SGA and PGA, see "About Memory Management".

About Memory Management

This section provides background information on memory management in Oracle Database Express Edition (Oracle Database XE). It includes the following topics:

Memory Allocation Overview
SGA Components
SGA and PGA Sizes

Memory Allocation Overview

To support database operation, Oracle Database XE needs to start a set of processes, called background processes, and needs to allocate some memory in the host computer. The background processes and allocated memory together make up an Oracle instance.

There are two types of memory that the Oracle instance allocates:

System global area (SGA)—A shared memory area that contains data buffers and control information for the instance. The SGA is divided into separate buffer areas and data pools. These are described in "SGA Components".
Program global area (PGA)—A memory area used by a single Oracle server process. An Oracle server process is a process that services a client's requests. Oracle Database XE creates a new server process whenever it receives a new database connection request. Each new server process then allocates its own private PGA area. The PGA is used to process SQL statements and to hold logon and other session information.

Figure 5-1 illustrates memory allocation in Oracle Database XE.

Figure 5-1 Memory Allocation in Oracle Database XE

Description of "Figure 5-1 Memory Allocation in Oracle Database XE"

The amount of memory allocated to the SGA and PGA directly affects the performance of your database. The SGA and PGA sizes are configured automatically when you install Oracle Database XE. See "SGA and PGA Sizes" for a discussion of when you might change them.

SGA Components

The SGA has several components, as listed in the following table. Oracle Database XE automatically tunes the individual sizes of these components for optimal performance.

Component	Description
Buffer cache	The buffer cache is the component of the SGA that acts as the buffer to store any data being queried or modified. All clients connected to the database share access to the buffer cache. The buffer cache helps avoid repeated access from the physical disk, a time-consuming operation.
Shared pool	The shared pool caches operational information and code that can be shared among users. For example: SQL statements are cached so that they can be reused. Information from the data dictionary, such as user account data, table and index descriptions, and privileges, is cached for quick access and reusability. Stored procedures are cached for faster access.
Redo log buffer	The redo log buffer improves performance by caching redo information (used for instance recovery) until it can be written at once and at a more opportune time to the physical redo log files that are stored on disk. Redo information and redo log files are discussed in "Online Redo Log Files".
Large pool	The large pool is an optional area that is used for buffering large I/O requests for various server processes.

SGA and PGA Sizes

Caution:

Oracle Database XE uses Automatic Memory Management, which usually provides the best memory configuration based on your resources and workload. Do not set any memory-related parameters unless you fully understand the consequences.

If you change any memory-related parameter values, test them fully before implementing the changes in a production environment.

The default si zes for the SGA and PGA are set upon installation, based on the total amount of physical memory in your system. Rather than changing the sizes of individual SGA components, you can change the overall size of the SGA by setting a parameter called SGA Target, and Oracle Database XE automatically adjusts the sizes of the individual SGA components, continuously tuning these sizes to optimize performance. Similarly, rather than changing the size of individual PGAs, you can change the total amount of memory allocated for the collection of PGAs, and Oracle Database XE adjusts individual PGA sizes as needed. The collection of PGAs is known as the PGA Aggregate. You change the PGA Aggregate maximum size by setting a parameter called PGA Aggregate Target.

Note:

Oracle Database XE always allocates the full amount of memory specified by the SGA Target parameter. That is, the current SGA size is always equal to SGA Target. In contrast, the current size of the PGA Aggregate may be less than the amount specified by the PGA Aggregate Target parameter. The database allocates more memory for the PGA Aggregate as needed, up to the maximum indicated by PGA Aggregate Target.

The maximum amount of memory that Oracle Database XE allows for the SGA and PGA Aggregate is 1 gigabyte (GB). If you attempt to change memory allocation so that the sum of the SGA size and PGA Aggregate size exceeds 1 GB, Oracle Database XE issues an error message. (For SGA changes, the error message does not appear until you restart the database.)

The only circumstanc es under which you should need to change SGA and PGA Aggregate sizes are the following:

You add physical memory to the computer running Oracle Database XE and want to allocate more to the database.

In this case, increase both the SGA and PGA Aggregate sizes, maintaining roughly the original ratio of SGA size to PGA Aggregate size.
You receive an error due to insufficient memory.

If the error message indicates insufficient memory for an SGA component, increase the SGA size. Examples of such errors include the following:
```
ORA-04031: unable to allocate n bytes of shared memory
ORA-00379: no free buffers available in buffer pool...
```
If the error message indicates insufficient memory for a process, increase the PGA Aggregate size. An example of such an error is the following:
```
ORA-04030: out of process memory when trying to allocate n bytes
```
If you are not sure whether the insufficient memory error involves the SGA or PGA, increase both SGA and PGA Aggregate sizes, maintaining roughly the original ratio of SGA size to PGA Aggregate size.

For SGA size changes, you must shut down and restart the database for the changes to take effect. For PGA Aggregate size changes, there is no need to restart the database.

Example: Changing SGA and PGA Aggregate Sizes (Advanced Users)

This example is for advanced users. It assumes that you have sufficient knowledge to make a sound decision to override the Oracle Database XE default memory management, and that you will test the changes thoroughly before implementing them in a production environment.

Suppose you just upgraded the computer running Oracle Database Express Edition (Oracle Database XE) to add 1 gigabyte (GB) or more of system memory, and you want to increase the memory for the database by 250 MB. Of this 250 MB, you want to add 200 MB to the SGA and 50 MB to the PGA Aggregate.

To accomplish this, you can the DBA navigator in SQL Developer to check the current values of the SGA_TARGET and PGA_AGGREGATE TARGET initialization parameters, and then use the ALTER SYSTEM command to reset the values of these parameters, as follows:

In SQL Developer, click View, then DBA to display the DBA navigator.
If a connection to the SYSTEM user does not already exist in that navigator, add one by clicking the Connections node, selecting Add Connection, and completing the actions for adding the SYSTEM account connection.
In the DBA navigator, expand the SYSTEM connection, expand Database Configuration, and click Initialization Parameters.

Note the current values for pga_aggregate_target and sga_target, and calculate the desired new values. Assume for this example that the desired new values are 140 megabytes for pga_aggregate_target and 472 megabytes for sga_target.
In the Connections navigator, select the SYSTEM connection; and if a SQL Worksheet is not already open for that connection, right-click SYSTEM and select Open SQL Worksheet.

In the SQL Worksheet for the SYSTEM connection, enter the following command lines:

ALTER SYSTEM SET pga_aggregate_target = 140 M;
ALTER SYSTEM SET sga_target = 472 M;

In the SQL Worksheet for the SYSTEM connection, click the Run Script icon to execute these statements.
At the next convenient time, shut down and restart the database to enable the SGA size changes to take effect.