Memory Architecture

Introduction to Oracle Memory Structures

Oracle uses memory to store information such as the following:

- Program code

- Information about a connected session, even if it is not currently active

- Information needed during program execution (for example, the current state of a query from which rows are being fetched)

- Information that is shared and communicated among Oracle processes (for example, locking information)

- Cached data that is also permanently stored on peripheral memory ( for example, data blocks and redo log entries)

The basic memor structures associated with Oracle include:

- System Global Area (SGA), which is shared by all server and background processes.

- Program Global Areas (PGA), which is private to each server and background process; there is on PGA for each process.

Overview of the System Global Area

A system global area(SGA) is a group of shared memory structures that contain data and control information for one Oracle database instance. If multiple users are concurrently connected to the same instance, then the data in the instance's SGA is shared among the users. Consequently, the SGA is sometimes called the shared global area.

An SGA and Oracle processes constitute an Oracle instance. Oracle automatically allocates memory for an SGA when you start an instance, and the operating system reclaims the memory when you shut down the instance. Each instance has its own SGA.

The SGA is read/write. All users connected to a multiple-process database instance can read information contained within the instance's SGA, and serveral processes write to the SGA during execution of Oracle.

The SGA contains the following data structures: Database buffer cache, Redo log buffer, Shared pool, Java pool, Large pool(optional), Streams pool, Data dictionary cache, Other miscellaneous information

Part of the SGA contains general information about the state of the database and the instance, which the background processes need to access; this is called the fixed SGA. No user data is stored here. The SGA also includes information communicated between processes, such as locking information.

If the system uses shared server architecture, then the request and response queues and some contents of the PGA are in the SGA.

The size of the SGA is determined by several initialization parameters. The following parameters have the greatest effect on SGA size:

Database Buffer Cache

The database buffer cache is the portion of the SGA that holds copies of data blocks read from datafiles. All user processes concurrently connected to the instance share access to the database buffer cache.

The database buffer cache and the shared SQL cache are logically segmented into multiple sets. This organization into multiple sets reduces contention on multiprocessor systems.

Redo Log Buffer

The redo log buffer is a circular buffer in the SGA that holds information about changes made to the database. This information is stored in redo entries. Redo entries contain the information necessary to reconstruct, or redo, changes made to the database by INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP operations. Redo entries are used for database recovery, if necessary.

Redo entries are copied by Oracle database processes from the user's memory space to the redo log buffer in the SGA. The redo entries take up continuous, sequential space in the buffer. The background process LGWR writes the redo log buffer to the active redo log file (or group of files) on disk.

Shared Pool

The shared pool portion of the SGA contains the library cache, the dictionary cache, buffers for parallel execution messages, and control structures.

The total size of the shared pool is determined by the initialization parameter SHARED_POOL_SIZE. The default value of this parameter is 8MB on 32-bit platforms and 64MB on 64-bit platforms. Increasing the value of this parameter increases the amount of memory reserved for the shared pool.

- Library Cache The library cache includes the shared SQL areas, private SQL areas (in the case of a shared server configuration), PL/SQL procedures and packages, and control structures such as locks and library cache handles.

Shared SQL areas are accessible to all users, so the library cache is contained in the shared pool within the SGA.

- Dictionary Cache The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and users. Oracle accesses the data dictionary frequently during SQL statement parsing. This access is essential to the continuing operation of Oracle.

The Data dictionary is accessed so often by Oracle that two special locations in memory are designated to hold dictionary data. One area is called the data dictionary cache, also known as the row cache because it holds data as rows instead of buffers (which hold entire blocks of data). The other area in memory to hold dictionary data is the library cache. All oracle user processes share these two caches for access to data dictionary information.

Large Pool

The database administrator can configure an optional memory area called the large pool to provide large memory allocations for:

- Session memory for the shared server and the Orarcle XA interface (used where transactions interact with more than one database)

- I/O server processes

- Oracle backup and restore operations

Java Pool

Java pool memory is used in server memory for all session-specific Java code and data within the JVM. Java pool memory is used in different ways, depending on what mode the Oracle server is running in.

The Java Pool Advisor statistics provide information about library cache memory used for Java and predict how changes in the size of the Java pool can affect the parse rate. The Java Pool Advisor is internally turned on when statistics_level is set to TYPICAL or higher. These statistics reset when the advisor is turned off.

Streams Pool

In a single database, you can specify that Streams memory be allocated from a pool in the SGA called the Streams pool. To configure the Streams pool, specify the size of the pool in bytes using the STEAMS_POOL_SIZE initialization parameter. If a Streams pool is not defined, then one is created automatically when Streams is first used.

If SGA_TARGET is set, then the SGA memory for the Streams pool comes from the global pool of SGA. If SGA_TARGET is not set, then SGA for the Streams pool is transferred from the buffer cache. This transfer takes place only after the first use of Streams. The amount transferred is 10% of the shared pool size.

Other SGA Initialization Parameters

You can use several initialization parameters to control how the SGA uses memory.

Physical Memory - The LOCK_SGA parameter locks the SGA into physical memory.

SGA Starting Address - The SHARED_MEMORY_ADDRESS and HI_SHARED_MEMORY_ADDRESS parameters specify the SGA's starting address at runtime. These parameters are rarely used. For 64-bit platforms, HI_SHARED_MEMORY_ADDRESS specifies the high order 32 bits of the 64-bit address.

Extended Buffer Cache Mechanism - The USE_INDIRECT_DATA_BUFFERS parameter enables the use of the extended buffer cache mechanism for 32-bit platforms that can support more than 4 GB of physical memory. On platforms that do not support this much physical memory, this parameter is ingored.

Overview of the Program Global Areas

A program global area (PGA) is a memory region that contains data and control information for a server process. It is a nonshared memory created by Oracle when a server process is started. Access to it is exclusive to that server process and is read and written only by Oracle code acting on behalf of it. The total PGA memory allocated by each server process attached to an Oracle instance is also referred to as the aggregated PGA memory allocated by the instance.