Lesson 1

Understanding Oracle's Memory Architecture

Now that you know about files and processes, it is time to learn about the last piece of the architecture puzzle, which is memory.
Oracle uses several memory structures: the

System Global Area,
Program Global Areas,
and Sort Areas.

See Figure 1 below

Evolution of Oracle's Memory Architecture

Oracle's Memory Architecture has evolved since the earlier versions. While the System Global Area (SGA) is a persistent component, Program Global Areas (PGA) and Sort Areas have been replaced by more granular memory structures.
The current Oracle Memory Architecture consists of the following main components:

System Global Area (SGA): Shared memory region used by all sessions and background processes. It includes various sub-components such as shared pools, buffer cache, redo log buffer, and control structures.
Process-Specific Memory Areas (PSMAs): Each session and background process has its own Process-Specific Memory Area. This includes the Private SQL Area (which holds parse trees, execution plans, and session-specific data), User Global Area (UGA), stack space, and other session-specific buffers and caches.
Oracle Database Buffer Cache (DB Cache): Stores frequently accessed data blocks in memory to improve performance. It is managed by the Buffer Manager Background Process (BMP).
Redo Log Buffer: Stores redo entries in memory before they are written to the redo log files. It is managed by the Log Writer (LGWR) Background Process.
Java Pool: Allocates and manages memory for Java Virtual Machine (JVM). It is used by Oracle JVM-based products, such as Oracle Database and Oracle WebLogic Server.
Shared Memory Structures: Various shared memory structures are used for communication and coordination between Oracle processes, such as the shared hash table, enqueue structures, and latch structures.
Memory Targets: Oracle provides automatic memory management through Memory Targets. It includes various target parameters that allow database administrators to specify the desired memory allocation for different components.

It's worth noting that Oracle's Memory Architecture may vary slightly depending on the specific Oracle version and configuration.

The uploaded image represents the architecture of Oracle Database Memory and Process Structures, particularly focusing on the System Global Area (SGA) and Program Global Area (PGA).
**Structure and Features:**

**System Global Area (SGA)**:

The **SGA** is a shared memory region that contains data and control information for an Oracle database instance.

It includes several key memory components:

**Java Pool**: Stores Java code and data within the database.

**Shared Pool**: Contains SQL execution plans, parsed SQL statements, and PL/SQL code.

**Buffer Cache**: Caches data blocks read from disk for quick access.

**Streams Pool**: Manages memory for Oracle Streams.

**Redo Buffer**: Temporarily stores redo log entries before writing to redo logs.

**Large Pool**: Used for memory-intensive operations like backup and restore, shared server processes, and parallel execution.

**Other Components**: Includes various miscellaneous memory structures.

**Program Global Area (PGA)**:

The **PGA** is a private memory region allocated to each server process.

It stores data related to session-specific operations such as:

**Sorting operations**

**Session memory**

**Stack space**

Each **server process** and **background process** has its own PGA.

**Processes**:

**Server Processes (1, 2, 3)**:

These processes handle user connections and execute SQL statements.

Each server process interacts with the SGA and its associated PGA.

**Background Processes**:

Essential for database operation (e.g., DBWR, LGWR, PMON, SMON).

Communicates with the SGA and manages database tasks asynchronously.

**Key Insights:**

The architecture separates **shared memory (SGA)** from **private memory (PGA)** to optimize performance.

The **server processes** fetch data from SGA and manage user queries.

**Background processes** handle database maintenance and logging.

This image is a conceptual representation of Oracle Database Instance Architecture, illustrating how memory and processes interact. Figure 1: Oracle Database Memory Structures consisting of 1) Server Processes and 2) Background Process interacting with the System Global Area

Understanding and properly sizing the System Global Area, Program Global Areas, and Sort Areas can have big impact on overall database performance.
The System Global Area is the most significant structure, and the most important to understand. You've already seen, in the previous module, that it's divided into several smaller areas known as the database buffer cache, the redo log buffer, and the shared pool. This module goes into more detail about each of those structures, particularly the shared pool.
When you are done with this module, you should be able to:

Describe the memory structures that make up the SGA
Describe how the database buffer cache manages data blocks
Describe the purpose of the three types of buffer pools
Describe the function of the redo log buffer.
Identify the function of the large pool
Describe how each session gets its own memory
List the contents of the PGA
Describe how Oracle uses memory for sorting

The knowledge that you gain in this lesson will enable you to understand many of the tuning issues that will arise when you manage an Oracle database.