Lesson 1

You have already covered how Oracle organizes its physical files and its background and server processes. Memory is the last piece of the architecture puzzle, and it is the piece that ties the other two together. Every file Oracle reads is eventually staged in memory, and every process that touches the database does its work against memory structures rather than against disk directly.
Oracle's memory divides into two top-level structures:

1. System Global Area (SGA), a single block of shared memory used by every session and background process in the instance.
2. Program Global Area (PGA), a private block of memory allocated separately to each server process and each background process.

That two-way split, shared versus private, is the foundation for everything else in this module. See Figure 1 below.

The SGA is created when the Oracle instance starts and is released when the instance shuts down. Every session connected to the database reads from and writes to the same SGA, which is what makes it possible for one session's cached data block to be reused by a completely different session moments later, instead of every session re-reading the same block from disk.
Because the SGA is shared, its size is set once at the instance level rather than per session. You can view its current size at any time with:

SELECT * FROM V$SGA;

That query returns the SGA broken into its major components, which is a useful first stop whenever you are troubleshooting memory-related performance issues.

The SGA is not one undifferentiated pool. It is divided into several sub-components, each with a distinct job:

1. Shared Pool: holds the library cache (parsed SQL and PL/SQL) and the data dictionary cache. This is the component most sensitive to poorly written, non-reusable SQL, because every unique statement text consumes its own slot.
2. Database Buffer Cache: holds copies of data blocks read from disk, split into default, KEEP, and RECYCLE pools so that frequently reused blocks can be protected from eviction by one-time large scans.
3. Redo Log Buffer: a small, fast buffer that holds redo entries until the Log Writer (LGWR) background process flushes them to the online redo log files on disk.
4. Large Pool: an optional area used for memory-heavy operations that would otherwise compete with the Shared Pool, including RMAN backup and restore, shared server session memory, and parallel query message buffers.
5. Java Pool: memory reserved for Java code and data when the database runs Java stored procedures inside the JVM.
6. Streams Pool: supports Oracle Streams and GoldenGate style capture-and-apply replication.
7. In-Memory Area: an optional area that holds a column-oriented copy of selected table data alongside the row-oriented buffer cache, letting analytic queries scan only the columns they need.
8. Vector Pool: an optional area introduced for AI Vector Search, holding vector embeddings and supporting structures used for similarity search queries against vector data.
9. Fixed SGA and Other Structures: a small internal area holding instance-level bookkeeping and pointers to the rest of the SGA components; it is not separately configurable.

The database buffer cache and the shared pool are the two components you will spend the most time tuning in practice, and later lessons in this module go into each of them in depth, particularly the shared pool.

Where the SGA is shared, the PGA is private. Every server process gets its own PGA, and no other process can read it. A PGA typically contains:

1. A Private SQL Area, holding bind variable values and the runtime state of an executing cursor.
2. SQL work areas, used for sort, hash join, bitmap merge, and bulk load operations.
3. Session memory, which under the default dedicated server connection model lives inside the private PGA.
4. Stack space, the operating-system-level call stack for the process itself.

That last point about session memory has an important exception. Under a shared server configuration, a fixed pool of server processes is shared across many client connections, so a session's memory cannot live in any one process's private PGA, since it might be serviced by a different process on the next call. In that configuration, session memory relocates into the SGA, specifically into the Large Pool if one is configured, or into the Shared Pool otherwise. This is one of the main reasons the Large Pool exists as its own structure rather than being folded into the Shared Pool. Unless you have configured shared server explicitly, your database is running dedicated server, and every session's PGA is fully private.

Sorting deserves its own explanation because it is one of the most common sources of PGA memory pressure. Any time a query includes ORDER BY, GROUP BY, a DISTINCT, a hash join, or a bitmap index merge, Oracle needs a work area in which to do that operation. Those work areas live inside the PGA of the session running the query, not in any shared SGA structure.
Older versions of Oracle required a database administrator to size these work areas by hand, through parameters such as SORT_AREA_SIZE and HASH_AREA_SIZE. Those parameters still exist for backward compatibility, but they are not the current recommended approach. Once PGA_AGGREGATE_TARGET is set above zero, which it is by default, Oracle sizes each session's work areas automatically, expanding and shrinking them as needed and reclaiming memory from idle sessions to give to active ones. This is why modern Oracle memory tuning is far less about hand sizing individual parameters and far more about setting sensible aggregate targets and letting the instance manage the details.

Oracle gives you three levels of automation to choose from, and it is worth knowing all three even if your environment only uses one:

1. MEMORY_TARGET / MEMORY_MAX_TARGET: the most hands-off option. A single target governs both the SGA and the total instance PGA as one combined pool, and Oracle shifts memory between them automatically as workload demands change.
2. SGA_TARGET / SGA_MAX_SIZE: governs only the SGA. Used when you want to size the PGA separately rather than let Oracle manage both pools together.
3. PGA_AGGREGATE_TARGET / PGA_AGGREGATE_LIMIT: governs the total PGA memory available across all sessions combined, which is what actually drives the automatic sort and hash work area sizing described above.

You can see how the PGA is actually being used right now with:

SELECT * FROM V$PGASTAT;

and you can see current SGA and PGA target settings with:

SHOW PARAMETER target;

Both are worth running early in any performance investigation, before diving into any single component.

Properly sizing the SGA and PGA has a direct, measurable impact on database performance. The SGA is the more significant of the two structures to understand first, since it is shared across the entire instance and its sub-components, the database buffer cache, the redo log buffer, and the shared pool, are where most tuning work happens in practice. This module goes into much more detail on each of those structures, with particular attention to the shared pool.
When you finish this module, you should be able to:

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

The knowledge you gain in this lesson will carry directly into the tuning issues you will encounter whenever you manage a real Oracle database, since nearly every performance problem eventually traces back to a memory structure being undersized, oversized, or simply misunderstood.