Program Global Area

Program Global Area (PGA)

🧠 Introduction
The Program Global Area (PGA) is like a private workspace for each Oracle process.
Unlike the SGA, which is shared by all, the PGA is nonshared — dedicated to a single server or background process.

Think of it like a developer having their own laptop (PGA), while the whole team shares a server (SGA).

🧩 What is the PGA?
The PGA is a memory region created when a server process or background process starts and is automatically deallocated when the process ends.

It holds session-specific data and control information that only that process can access.

🧠 PGA Components (Dedicated Server Mode)

🔹 1. SQL Work Areas

Used during SQL execution for memory-intensive operations:

  • Sort Area: Handles sorting for ORDER BY, GROUP BY, etc.
  • Hash Area: Used in hash joins to match rows between tables
  • Bitmap Merge Area: Merges results from multiple bitmap index scans

🔹 2. User Global Area (UGA)

Stores session-specific information such as:

  • Login/session variables
  • Temporary values for packages/procedures
  • OLAP Pool: Caches OLAP (Online Analytical Processing) data pages (similar to DB blocks)

📝 In dedicated server mode, UGA is part of the PGA.
In shared server mode, UGA moves to the Large Pool (in the SGA).

🔹 3. Private SQL Area

Each SQL statement gets its own private workspace to store:

  • Parsed SQL text
  • Bind variable values
  • Execution state (e.g., current row, fetch status)

It has two main areas:

  • Persistent Area: Stores bind variables (placeholder values for SQL statements)
  • Runtime Area: Manages state during query execution

🔁 Cursors act as pointers to Private SQL Areas, helping the database track query execution for each session.

🔄 PGA in Shared Server Mode

In shared server architecture:

  • Many client sessions share one server process
  • The UGA is moved to the Large Pool (in the SGA)
  • The PGA is left with:
    ✔ SQL Work Areas
    ✔ Private SQL Area

This architecture helps scale to more users with fewer server processes, conserving memory.

🛠️ DBA Tip of the Day
Want to reduce sorting or memory-intensive slowdowns?
Tune your PGA using the PGA_AGGREGATE_TARGET or PGA_AGGREGATE_LIMIT parameters.

🔍 Show PGA Parameters

SHOW PARAMETER pga;

✅ Query: View PGA Memory Statistics

COLUMN name FORMAT A40
COLUMN value FORMAT 9999999999

SELECT * FROM v$pgastat;

✅ Query: View SQL Work Area Histogram

COLUMN operation FORMAT A30
COLUMN onepass FORMAT 999999
COLUMN multipass FORMAT 999999
COLUMN optimal FORMAT 999999
COLUMN memory_used_in_kb FORMAT 999999999

SELECT * FROM v$sql_workarea_histogram;