Oracle Memory: SGA and PGA Explained
Two Kinds of Memory
| Memory | Shared or private | Used for |
|---|---|---|
| SGA | Shared by all sessions | Caching data blocks, SQL and redo |
| PGA | Private to one server process | Sorts, hash joins, session variables |
Parts of the SGA
| Component | What it does |
|---|---|
| Database buffer cache | Keeps recently used data blocks so Oracle avoids reading the disk |
| Shared pool | Holds parsed SQL (library cache) and dictionary information |
| Redo log buffer | Holds change records before LGWR writes them to disk |
| Large pool | Memory for backups (RMAN), shared server and parallel work |
| Java pool / Streams pool | Used only when those features are in use |
Simple example: The buffer cache is your desk. Papers you used recently stay on the desk, so you find them fast. The shared pool is the sticky notes with answers to questions you have already solved.
How Memory is Managed
| Parameter | Meaning |
|---|---|
SGA_TARGET | Total SGA size. Oracle auto-tunes the parts inside it (ASMM). |
SGA_MAX_SIZE | The ceiling that SGA_TARGET can grow to |
PGA_AGGREGATE_TARGET | Soft limit for all PGA memory together |
MEMORY_TARGET | One setting for SGA + PGA (AMM). Not used with HugePages on Linux. |
Production tip: Most Linux production databases use SGA_TARGET and PGA_AGGREGATE_TARGET with HugePages, instead of MEMORY_TARGET.
Check Your Memory
sql
SHOW PARAMETER sga
SHOW PARAMETER pga
SELECT name, ROUND(bytes/1024/1024) AS mb
FROM v$sgainfo
ORDER BY bytes DESC;
SELECT component, ROUND(current_size/1024/1024) AS mb
FROM v$sga_dynamic_components
WHERE current_size > 0;
Check PGA Use
sql
SELECT name, ROUND(value/1024/1024) AS mb
FROM v$pgastat
WHERE name IN ('total PGA allocated','total PGA inuse','maximum PGA allocated');
Resize Memory
sql
ALTER SYSTEM SET sga_target = 1G SCOPE = BOTH;
ALTER SYSTEM SET pga_aggregate_target = 400M SCOPE = BOTH;
Free edition note: Oracle Free uses up to 2 GB of RAM, so keep the SGA and PGA together under that limit when you practice.
Rule of Thumb
- Start with about 60-70% of server RAM for Oracle if the server only runs the database.
- Split roughly 75-80% to the SGA and 20-25% to the PGA for typical OLTP systems.
- Never let SGA + PGA exceed physical RAM. Swapping makes Oracle extremely slow.
Conclusion
The SGA caches shared data and SQL, the PGA is each session's private workspace. Use SGA_TARGET and PGA_AGGREGATE_TARGET and watch the V$ views.
Quick Summary: Buffer cache = data blocks, shared pool = SQL, redo buffer = changes. PGA = private sort memory.

