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:

About Memory Management
Viewing Current Memory Allocation
Example: Changing SGA and PGA Aggregate Sizes

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

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.

Viewing Current Memory Allocation

To view current memory allocation:

Access the Database Home Page.

See "Accessing the Database Home Page" for instructions.
In the Usage Monitor at the right-hand side of the page, examine the Memory bar graph.

Description of the illustration usage_monitor.gif

Note:
If the Usage Monitor does not appear on the Database Home Page, click the Customize link near the upper right-hand corner of the page, and then enable the Usage Monitor.

Example: Changing SGA and PGA Aggregate Sizes

Suppose you just upgraded the computer running Oracle Database Express Edition (Oracle Database XE) from 1 gigabyte (GB) to 1.5 GB of system memory, and you want to allocate roughly half (250 MB) of the additional memory to the database. Of this 250 MB, you want to add 200 MB to the SGA and 50 MB to the PGA Aggregate.

To change SGA and PGA Aggregate sizes:

Access the Database Home Page.

See "Accessing the Database Home Page" for instructions.
Click the Administration icon, and then click the Memory icon.

If prompted for administrator credentials, enter the SYSTEM user name and password or another administrator user name and password, and then click Login. See "About Administrative Accounts and Privileges" for more information.

The Memory page appears, displaying SGA Target, PGA Aggregate Target, the amount of memory that is currently allocated to the SGA and the PGA Aggregate, and the totals of the target allocations and current allocations.

Description of the illustration memory_page.gif
In the Tasks list, click Configure SGA.
On the SGA page, in the SGA Target (in MB) field, enter 472.

Description of the illustration memory_page_sga.gif

The value 472 is the sum of the current SGA size (272) plus the 200 that you want to add.
Click Apply Changes.

A confirmation message appears.
Click the Program Global Area link to switch to the PGA page.
In the PGA Aggregate Target field, enter 140, and then click Apply Changes.

The value 140 is the sum of the current PGA Aggregate Target size (90) plus 50.
At the next convenient time, shut down and restart the database to enable the SGA size changes to take effect.

See "Starting Up and Shutting Down" for instructions.