Contents of the Oracle Program Global Area (PGA)

The Program Global Area (PGA) is a private memory region allocated to each Oracle server process. Unlike the System Global Area (SGA), PGA memory is never shared between processes. Its purpose is to store process-specific and session-specific information that cannot be safely or efficiently shared.

When an SQL statement is executed, shared information—such as the SQL text and execution plan—is stored in shared memory so that multiple processes can reuse it. In contrast, data that belongs to a specific execution context (such as bind values, cursor state, and intermediate results) is stored in the PGA.

High-Level Categories of PGA Contents

Under a standard dedicated server configuration, the PGA contains two broad classes of information:

1. Stack Space
   Stack space is used by the server process to store:
   • Local variables
   • Arrays and temporary data structures
   • Procedure and function call information
   This memory supports internal execution logic and is released when the process terminates.
2. Session-Specific Memory
   Session memory stores information that must persist for the lifetime of a user session, including:
   • PL/SQL variables and package state
   • Cursor execution state
   • Bind variable values
   • Private SQL areas
   In dedicated server mode, this memory resides in the PGA. In shared server mode, much of this session memory is relocated to the SGA.

Detailed PGA Components

The PGA is not a single flat structure; it is composed of several functional areas that support SQL execution and session management.

1. Private SQL Area

The private SQL area holds execution-specific information for a SQL statement, including:

• Bind variable values
• Runtime execution state (row counts, fetch position)
• Work area control structures

Each SQL statement has its own private SQL area. In a dedicated server environment, this area resides in the PGA. In a shared server environment, it is stored in the SGA so that multiple shared server processes can continue execution on behalf of the same session.

2. SQL Work Areas

SQL work areas are regions of PGA memory used to perform memory-intensive operations such as:

• Sort operations (ORDER BY, GROUP BY)
• Hash joins
• Bitmap index merges

These work areas are allocated dynamically and released when the operation completes. Their size and behavior are governed by automatic memory management.

3. Session Memory

Session memory supports the lifetime of a user session and includes:

• PL/SQL package variables
• Cursor metadata
• Session-level state information

This memory ensures continuity between SQL executions within the same session.

4. Stack Memory

Stack memory supports recursive execution and procedural logic. It grows and shrinks as needed and is released when the server process ends.

Instance PGA

The Instance PGA is the collective total of all PGAs allocated by all server processes in an Oracle instance. Each process owns its own PGA, but Oracle manages the aggregate size at the instance level.

PGA Memory Management (Modern Oracle)

Starting with Oracle 9i and significantly enhanced in later releases, PGA memory is managed automatically.

• PGA_AGGREGATE_TARGET
  Specifies the target size for total PGA memory across the instance.
• WORKAREA_SIZE_POLICY = AUTO
  Allows Oracle to dynamically size SQL work areas based on workload.
• MEMORY_TARGET
  When enabled, Oracle balances memory automatically between the PGA and SGA.

Modern Oracle releases dynamically resize work areas to maximize throughput while preventing runaway memory usage.

Shared Server (MTS) Considerations

The legacy Multi-Threaded Server (MTS), now known as Oracle Shared Server, moves session memory out of the PGA and into the SGA. While this can reduce memory usage for very high connection counts, it introduces additional complexity and overhead.

With modern 64-bit systems and abundant RAM, shared server configurations are now rare and are typically reserved for specific use cases such as large Java middle-tier connection pools.

Lesson Summary

The Oracle PGA is a private, per-process memory area that contains:

• Stack space
• Session memory
• Private SQL areas
• SQL work areas

Understanding these components is essential for diagnosing performance issues, tuning SQL workloads, and configuring memory correctly in modern Oracle environments.

Oracle PGA – Quiz