Interview Question 13 : What is PGA or Private Global Area? How it is different from SGA ?

 

What Is PGA (Private Global Area)?

Definition

PGA (Private Global Area) is a private memory area allocated by Oracle to each server process to store session‑specific and process‑specific data.

In simple terms:

• PGA = memory that belongs to one user session/process
• It is not shared with other sessions

---

What Does PGA Contain?

PGA holds data that must not be shared with other users.

Main Components of PGA

1️⃣ Session Information

• Session variables
• Cursor state
• Bind variable values
• Logon information

---

2️⃣ Sort Area

Used for:

• ORDER BY
• GROUP BY
• DISTINCT
• Hash joins
• Bitmap merge operations

✅ If sort area is large enough → done in memory
❌ If not → spills to TEMP tablespace

---

3️⃣ Work Areas

Used during:

• Hash joins
• Sort joins
• Bitmap operations

---

Key Characteristics of PGA

✅ Private to a server process
✅ Not visible to other sessions
✅ Allocated outside SGA
✅ Released when the process ends
✅ Most critical for query performance involving sorting and joins

---

How PGA Is Managed

Oracle manages PGA automatically using:

pga_aggregate_target

pga_aggregate_limit

• Oracle dynamically allocates memory to sessions
• Prevents one session from consuming all memory

✅ DBA controls total memory, not per user (usually)

---

What Is SGA (System Global Area)?

Definition

SGA (System Global Area) is a shared memory area that stores data and control information used by all server and background processes.

SGA is:

• Allocated at instance startup
• Shared by all sessions
• Central to database performance

---

What Does SGA Contain?

Major Components of SGA

1️⃣ Database Buffer Cache

• Stores data blocks read from disk
• Reduces physical I/O

---

2️⃣ Shared Pool

• SQL execution plans
• Parsed SQL
• Data dictionary cache

---

3️⃣ Redo Log Buffer

• Records changes made by transactions
• Used for recovery

---

4️⃣ Large Pool (Optional)

• Shared server
• RMAN
• Parallel execution

---

5️⃣ Java Pool / Streams Pool (Optional)

---

Key Characteristics of SGA

✅ Shared by all sessions
✅ Exists at instance level
✅ Allocated at startup
✅ Improves scalability and performance
✅ Critical for concurrency

---

PGA vs SGA – Key Differences (Most Important Part)

Aspect	PGA	SGA
Full Form	Private Global Area	System Global Area
Scope	Per process/session	Per instance
Sharing	❌ Not shared	✅ Shared
Lifetime	Ends when session ends	Exists while instance is up
Storage	Outside SGA	Main instance memory
Contents	Sorts, session data	Cache, SQL plans, redo
Controlled by	pga_aggregate_target	sga_target / memory_target

---

Dedicated Server vs Shared Server Impact

Dedicated Server

• Each session has its own PGA
• High memory usage if many sessions
• Faster execution

Session 1 → PGA 1
Session 2 → PGA 2
Session N → PGA N

---

Shared Server

• Sessions share server processes
• PGA usage is lower overall
• More scalable

✅ PGA usage depends on server mode

---

When Does PGA Become a Problem?

Symptoms of PGA Issues

• Excessive TEMP usage
• Slow sorts and joins
• ORA‑04030 (out of process memory)
• ORA‑4036 / ORA‑4030

---

DBA Fixes

✅ Increase PGA target
✅ Optimize SQL to reduce sorting
✅ Avoid Cartesian joins
✅ Use proper indexing

---

Real‑Life Analogy (Very Clear)

Office Analogy

• SGA = Shared office resources

  • Whiteboard
  • Common files
  • Shared tools

• PGA = Personal desk & drawer

  • Personal notes
  • Temporary work
  • Private files

Everyone uses the office tools (SGA),
but each employee has their own desk (PGA).

---

Interview‑Perfect One‑Line Answers ⭐

• PGA:

  PGA is private memory allocated to a server process for session‑specific operations such as sorting, hashing, and cursor management.

• SGA:

  SGA is shared memory used by all database processes to cache data blocks, SQL execution plans, and control information.

---

Final Summary (Easy to Remember)

🔥 SGA improves concurrency by sharing memory
🔥 PGA improves performance by giving private working space

✅ SGA = shared memory for all
✅ PGA = private memory per session

✅ PGA Spills to TEMP, Monitoring PGA/SGA, and Memory Management

This builds directly on your PGA vs SGA understanding.

---

1️⃣ What Is a PGA Spill to TEMP?

Concept

A PGA spill to TEMP occurs when a session needs more private memory (PGA) than Oracle can allocate, so Oracle uses the TEMP tablespace on disk as overflow.

This happens during memory‑intensive operations such as:

• ORDER BY
• GROUP BY
• DISTINCT
• Hash joins
• Bitmap merge operations

---

How It Works

SQL operation
   ↓
Uses PGA memory
   ↓
PGA insufficient
   ↓
Spill to TEMP (disk I/O)
   ↓
Slower execution

✅ In‑memory PGA = fast
❌ TEMP (disk) = slow

---

2️⃣ Why PGA Spills Are Bad (DBA Perspective)

Impact	Effect
Disk I/O	TEMP tablespace grows
Performance	Queries slow down
Concurrency	More contention
Users	Poor response time

🔑 Heavy TEMP usage almost always indicates PGA pressure or inefficient SQL

---

3️⃣ Common Causes of PGA → TEMP Spills

✅ Large result sets
✅ Poor join order
✅ Missing indexes
✅ Cartesian joins
✅ Low pga_aggregate_target
✅ Many concurrent sorting queries

---

4️⃣ How Oracle Manages PGA Memory

Oracle uses Automatic PGA Management.

Key Parameters

pga_aggregate_target → Desired total PGA usage

pga_aggregate_limit → Hard upper limit

Oracle:

• Dynamically allocates PGA to active sessions
• Reduces memory for some sessions when pressure increases
• Forces spills instead of crashing the instance

✅ This protects overall stability

---

5️⃣ Monitoring PGA Usage (Very Important)

Overall PGA Usage

SELECT name, value

FROM v$pgastat;

Key stats:

• total PGA allocated
• maximum PGA allocated
• over allocation count

✅ High over‑allocation count = memory pressure

---

Session‑Level PGA Usage

SELECT

s.sid,

s.username,

p.pga_used_mem,

p.pga_alloc_mem

FROM v$session s

JOIN v$process p ON s.paddr = p.addr

ORDER BY p.pga_used_mem DESC;

✅ Identify top PGA consumers

---

6️⃣ Monitoring TEMP Usage (Critical in Production)

SELECT

s.sid,

s.username,

t.blocks * 8 / 1024 AS temp_mb

FROM v$sort_usage t

JOIN v$session s ON t.session_addr = s.saddr

ORDER BY temp_mb DESC;

✅ Shows who is spilling to TEMP right now

---

7️⃣ How DBAs Fix Excessive PGA Spills

✅ Option 1: Increase PGA (Hardware Permitting)

ALTER SYSTEM SET pga_aggregate_target = 20G;

✅ Reduces spills
❌ Increases memory demand

---

✅ Option 2: Tune SQL (Preferred)

• Reduce rows early (filters)
• Avoid SELECT *
• Fix joins
• Add proper indexes
• Eliminate unnecessary sorts

✅ Best long‑term fix

---

✅ Option 3: Fix Join Method

• Replace nested loops on large data
• Improve hash join efficiency

📌 Optimizer usually gets this right if stats are correct

---

8️⃣ SGA Monitoring (Quick but Important)

Buffer Cache & Shared Pool

SELECT * FROM v$sga;

SGA Components

SELECT component, current_size

FROM v$sga_dynamic_components;

✅ Helps balance PGA vs SGA allocation

---

9️⃣ Memory Management Modes in Oracle

1️⃣ Automatic Memory Management (AMM)

memory_target

• Oracle manages SGA + PGA together
• Simple but less predictable

---

2️⃣ Automatic Shared Memory Management (ASMM)

sga_target

pga_aggregate_target

✅ Recommended for production ✅ Better DBA control

---

3️⃣ Manual Memory Management

• Rarely used
• High risk
• Not recommended

---

🔟 PGA vs SGA – Real Production Comparison

Scenario	Memory Used
Many users connected	PGA grows
Same SQL executed by many	SGA helps
Heavy sorts	PGA critical
Read‑heavy workload	SGA critical
Batch jobs	PGA spikes

---

1️⃣1️⃣ Interview‑Ready Q&A

Q: What happens if PGA is insufficient?

✅ Oracle spills to TEMP tablespace

---

Q: Does increasing PGA always fix performance?

❌ No — SQL tuning may be required

---

Q: Which memory is released when a session ends?

✅ PGA

---

Q: Which memory remains until instance shutdown?

✅ SGA

---

1️⃣2️⃣ Fault Symptoms (Know These)

Error	Meaning
ORA‑04030	Out of process memory
ORA‑4036	PGA memory limit exceeded
Huge TEMP growth	Frequent PGA spills

---

One‑Line DBA Golden Rules ⭐

• PGA is per session; SGA is per instance
• PGA spills = disk I/O = slow queries
• Tune SQL before throwing more memory
• TEMP usage is a PGA health indicator

---

✅ Final Architecture Mental Model

SGA (Shared, Cached, Reused)
 ├─ Buffer Cache
 ├─ Shared Pool
 └─ Redo Buffer

PGA (Private, Temporary, Per Session)
 ├─ Sort Area
 ├─ Hash Area
 └─ Cursor State

1) SGA Tuning (Buffer Cache vs Shared Pool) — Practical & Measurable
A. Goals of SGA Tuning
Buffer Cache: Minimize physical I/O (more logical reads from memory).
Shared Pool: Maximize plan/cursor reuse and reduce hard parses/latch/mutex contention.
B. Buffer Cache Tuning
Key views/metrics
-- Overall logical vs physical reads
SELECT name, value FROM v$sysstat
WHERE name IN ('session logical reads','physical reads');

-- Buffer cache size & components
SELECT component, current_size, min_size, max_size
FROM v$sga_dynamic_components
WHERE component LIKE '%buffer%';

-- Object-level buffer cache usage (top buffers)
SELECT o.owner, o.object_name, bh.status, COUNT(*) buffers
FROM v$bh bh JOIN dba_objects o ON bh.objd = o.data_object_id
GROUP BY o.owner, o.object_name, bh.status
ORDER BY buffers DESC FETCH FIRST 20 ROWS ONLY;
What to look for
High physical reads relative to session logical reads → cache maybe too small or access pattern unsuitable for caching (large scans).
Single hot objects dominating v$bh → consider KEEP/RECYCLE pools.
Actions
Increase db_cache_size (or sga_target under ASMM) cautiously.
Use multiple buffer pools:
KEEP for small, frequently accessed lookup tables.
RECYCLE for large, scan-heavy tables (ETL/reporting).
ALTER TABLE my_lookup STORAGE (BUFFER_POOL KEEP);
ALTER TABLE my_fact STORAGE (BUFFER_POOL RECYCLE);
Ensure queries avoid unnecessary large full scans in OLTP (index/selectivity fixes).
C. Shared Pool Tuning
Key views/metrics

-- Parse activity (soft/hard)
SELECT name, value FROM v$sysstat
WHERE name IN ('parse count (total)', 'parse count (hard)');

-- Library cache efficiency
SELECT namespace, pins, reloads, invalidations
FROM v$librarycache;

-- SQL reuse indication
SELECT executions, parse_calls, sql_id, substr(sql_text,1,80) txt
FROM v$sql
WHERE executions > 100
ORDER BY parse_calls DESC FETCH FIRST 20 ROWS ONLY;
What to look for
High hard parses → shared pool pressure, missing binds, too many distinct SQLs.
Library cache reloads/invalidations → shared pool sizing/contention.
Actions
Increase shared_pool_size (or sga_target).
Enforce bind variables in applications to improve cursor reuse.
Avoid excessive literal SQL and dynamic SQL variants.
Pin critical packages if necessary (rare in modern versions).
Review mutex/latch waits (AWR/ASH) — if present, consider SQL normalization and shared pool growth.
D. Redo Log Buffer (Quick sanity)
If you see log buffer space waits, consider increasing log_buffer. Most systems are fine with defaults unless very high DML bursts.
E. ASMM vs AMM
ASMM (recommended): sga_target + pga_aggregate_target — clear control over shared vs private memory.
AMM: memory_target manages SGA+PGA together; simpler but less predictable for production tuning.
2) TEMP Outage RCA — Real Incident Walkthrough
🔴 Symptom
TEMP tablespace fills up rapidly.
Queries slow; some sessions error.
Alerts show space pressure on TEMP.
🧭 RCA Steps (Do this quickly)
Identify current consumers
SELECT s.sid, s.username, t.blocks*8/1024 AS temp_mb, t.sql_id
FROM v$sort_usage t JOIN v$session s ON t.session_addr = s.saddr
ORDER BY temp_mb DESC;
Check SQL causing spills
SELECT sql_id, sql_text FROM v$sql
WHERE sql_id IN (<top sql_ids>);
Confirm PGA pressure
SELECT name, value FROM v$pgastat
WHERE name IN ('total PGA allocated','maximum PGA allocated','over allocation count');
TEMP space & autoextend
SELECT tablespace_name, SUM(bytes)/1024/1024 AS mb
FROM dba_temp_files GROUP BY tablespace_name;

SELECT tablespace_name, file_name, autoextensible, increment_by
FROM dba_temp_files;
✅ Resolution Options (Choose based on urgency)
Immediate relief:
Add TEMP file (with prudence, ensure storage headroom).
Temporarily throttle batch/report jobs.
Kill/pace runaway sessions (confirm with business).
ALTER DATABASE TEMPFILE '/u02/temp02.dbf' SIZE 40G AUTOEXTEND ON NEXT 1G MAXSIZE 200G;
Permanent fixes:
Increase pga_aggregate_target to reduce spills (validate RAM availability).
Tune offending SQL to reduce sort set size (push filters earlier, remove SELECT *, add appropriate indexes).
Avoid massive hashes/sorts where not needed; fix join order.
Partition large tables so scans/aggregations operate on smaller sets.
Lesson: TEMP exhaustion is usually a symptom of PGA pressure or inefficient SQL, not just a storage issue.
3) Oracle vs PostgreSQL Memory — Quick Comparison
Topic
Oracle
PostgreSQL
Shared memory
SGA: buffer cache, shared pool, redo buffer, pools
Shared buffers (data cache), work_mem mostly per session/process
Private memory
PGA per server process/session
Per-backend memory; work_mem, maintenance_work_mem for operations
Sorts/joins
Primarily PGA, spills to TEMP
Primarily work_mem, spills to pg_temp
Management style
ASMM/AMM; rich instrumentation (v$pgastat, v$sga_dynamic_components)
Config-centric; tuning via shared_buffers, effective_cache_size, work_mem
Plan cache
Shared Pool caches cursors/plans; bind reuse critical
Plans cached per session; less centralized plan cache; prepared statements help
Parallelism
Mature PQ with granules; RAC considerations
Parallel workers; simpler executor model; no shared cache fusion
Bottom line: Oracle provides fine-grained shared vs private memory controls and instrumentation; PostgreSQL is simpler but requires careful tuning of work_mem vs shared_buffers for workloads to avoid spills and contention.
4) Memory-Related Interview Traps (with crisp answers)
Q: If TEMP is full, should we just add more TEMPfiles?
A: Only as a short-term relief. Root cause is PGA pressure / inefficient SQL. Tune SQL or increase pga_aggregate_target.
Q: Does higher buffer cache always mean faster queries?
A: No. Large scan workloads may bypass caching benefits; you need RECYCLE/KEEP pools and workload-appropriate access paths.
Q: Hard parses are high — increase shared pool?
A: Check for literal SQL/missing binds first. Increasing shared pool helps, but SQL normalization is the primary fix.
Q: We enabled AMM; can we stop thinking about SGA/PGA?
A: No. AMM is convenient but can rebalance unpredictably. ASMM is preferred for production control.
Q: TEMP spills disappeared after doubling PGA. Is the job done?
A: Validate system memory headroom, check overall concurrency, and still tune SQL to prevent future regression.
Q: What error indicates out-of-PGA?
A: ORA‑04030 (out of process memory) / ORA‑4036 (PGA limit exceeded).
5) Quick Memory Tuning Checklist (Print-ready)
A. Assess
 v$pgastat → over-allocation, total/maximum PGA.
 v$sort_usage → top TEMP consumers (sid/sql_id).
 v$sysstat → logical vs physical reads.
 v$librarycache → reloads/invalidations.
 AWR/ASH → latch/mutex, gc waits (RAC).
B. Act
 Right-size pga_aggregate_target and sga_target (ASMM).
 Configure KEEP/RECYCLE pools for cache behavior.
 Enforce bind variables; reduce SQL variants.
 Tune SQL to reduce sort/hash set size; index appropriately.
 Partition large tables; consider parallel plans carefully.
C. Validate
 Compare TEMP and PGA usage before/after.
 Re-check physical vs logical reads.
 Review parse counts and plan reuse.
 Document root cause, fix, and outcomes.