Database Memory Architecture Fundamentals
How a relational database splits memory into shared and private regions, and why that split explains almost every performance behavior you'll see later — using Oracle's SGA/PGA model as the clearest teaching example.
Learning objectives
- Explain the difference between memory that is shared across every connection and memory that is private to one session
- Describe what lives in the shared pool, buffer cache, and redo log buffer, and why each one is shared
- Describe what lives in a session's private memory and why it can never be shared
- Map this shared-vs-private split onto Postgres's shared_buffers and work_mem so the concept generalizes beyond one vendor
- Trace a single query's journey through both memory regions from connection to result
Story
Picture a database server as an office building. In the middle of the building there's one large shared room that every employee walks through during their workday. It holds the filing cabinet of recently used documents, the reference book of who sits where, and a photocopy tray stacked with pages someone copied five minutes ago that another employee might need again. Nobody owns this room. Everyone uses it, and whatever gets left there is fair game for the next person who walks in.
Each employee also has a personal desk a few steps away. It has their own notepad, their own calculator, their own half-finished scratch work. Nobody else touches it, and when the employee goes home for the day, the desk gets cleared out completely.
This is exactly how a database engine organizes its memory, and the two names you'll meet constantly are the SGA (System Global Area) for the shared room, and the PGA (Program Global Area) for the personal desk. Oracle made these terms famous, but the underlying split — one shared memory region for the whole instance, plus small private regions per connection — exists in essentially every mainstream RDBMS, just under different names.
Core mechanics
The SGA is allocated once, when the database instance starts, and it stays allocated as long as the instance is up. Every session that connects to the database can read from it and, depending on what it's doing, write to it. It holds three things you'll use constantly in later topics:
- The shared pool, which caches parsed SQL statements and their execution plans so the next session running the same query doesn't have to redo that work.
- The database buffer cache, which holds copies of data blocks read from disk, so a second read of the same block doesn't have to touch disk again.
- The redo log buffer, a small staging area for the stream of changes the database is about to permanently log — you'll meet this properly in the topic on undo and redo.
The PGA, by contrast, is private per session. It holds the memory a single connection needs to actually execute its own work: sort areas for ORDER BY and GROUP BY, hash areas for hash joins, and the session's own cursor state. Two sessions running the identical query each get their own PGA — there is no sharing here, by design, because sort buffers and in-flight bind values are inherently per-connection state. The moment a session disconnects, its PGA is torn down. Nothing in it outlives the connection.
Why does this split matter day to day? Because it tells you where to look when something is slow. If a query is slow because it's doing a lot of physical I/O, the first suspect is the buffer cache — is the working set of data simply too big to stay cached in the SGA? If a query is slow because of excessive sorting or a huge hash join spilling to disk, the first suspect is PGA sizing — does this session have enough private memory to do its sort or hash join in memory instead of writing temp segments to disk?
💻 Code example
-- Inspecting the shared, instance-wide memory areas (Oracle) SELECT pool, name, bytes/1024/1024 AS mb FROM v$sgastat WHERE pool = 'shared pool' ORDER BY bytes DESC FETCH FIRST 10 ROWS ONLY; -- Inspecting one session's own private memory usage SELECT n.name, round(s.value/1024/1024, 2) AS mb FROM v$sesstat s JOIN v$statname n ON n.statistic# = s.statistic# WHERE n.name IN ('session pga memory', 'session uga memory') AND s.sid = SYS_CONTEXT('USERENV', 'SID');
Core mechanics
If you've worked with Postgres, you've already touched this same split without necessarily naming it. Postgres's shared_buffers is Postgres's version of the buffer cache portion of the SGA — one shared pool of cached data pages, sized at instance startup, read by every backend process. Postgres's work_mem is the rough equivalent of a slice of PGA sort/hash area — it's the memory one backend process is allowed to use for a single sort or hash operation before it has to spill to temporary disk files.
The philosophies differ in an instructive way. Oracle (particularly with Automatic Memory Management, which you'll meet properly in the DML and commits topic) tends toward one big instance-level pool that the database itself subdivides and rebalances between shared and private uses as load shifts. Postgres leans more conservative and explicit: shared_buffers is a fixed shared pool, and work_mem is a per-operation ceiling that you size per session or per query, and a single complex query with several sorts and hashes can multiply that ceiling by the number of such operations it runs, which is a common and nasty memory-sizing surprise in Postgres that doesn't have a direct Oracle equivalent because Oracle's PGA_AGGREGATE_TARGET caps total private memory across the whole instance rather than per operation.
What carries over regardless of vendor is the mental model, not the parameter names: ask "is this a cost that benefits from being shared across many sessions, or a cost that is inherently private to one session's execution" and you'll correctly guess which memory pool governs it, in Oracle, Postgres, SQL Server's buffer pool, or anything else you'll encounter.
Nuance
It's tempting to think "bigger shared cache is always better," but that's not quite right. A cache only helps if the same blocks get reread often enough to justify holding them. A reporting workload that scans a 2TB table once and never touches it again gets almost no benefit from a bigger buffer cache for that scan — the data is read once and discarded. Sizing memory well means understanding your workload's actual reuse pattern, not just turning the dial up.
💻 Code example
-- Postgres equivalents, for comparison SHOW shared_buffers; -- the shared, instance-wide buffer cache SHOW work_mem; -- the per-operation private memory ceiling -- A single query with two sorts and a hash join can use up to -- 3x work_mem concurrently -- this has no direct Oracle PGA equivalent, -- since Oracle caps aggregate PGA across the whole instance instead.
Core mechanics
Let's trace a single SELECT statement end to end, naming which memory region does the work at each step.
- Your session connects. The database allocates a PGA for your session — private, empty, waiting.
- You submit a query. The database computes a hash of the SQL text and checks the shared pool (inside the SGA) for a matching, already-parsed version. If it finds one, this is a soft parse — cheap, fast, reusing someone else's prior work.
- If nothing matches, it's a hard parse: the optimizer builds an execution plan from scratch and stores it in the shared pool so future sessions running the same statement get a soft parse instead. This is expensive enough that the next topic is entirely about avoiding unnecessary hard parses.
- Executing the plan means reading data blocks. The database checks the buffer cache (also in the SGA) first. A block already cached there is a logical read — fast, no disk I/O. A block not cached triggers a physical read from disk, after which the block gets copied into the buffer cache for the next reader.
- If your query needs to sort or hash — an ORDER BY, a GROUP BY, a hash join — that work happens in your PGA, using memory nobody else can touch.
- Rows stream back to your client. When you disconnect, your PGA disappears entirely. The parsed plan and any cached blocks your query touched stay behind in the SGA for the next session.
Quick recap
- The SGA is one shared memory region per instance: shared pool (parsed SQL + plans), buffer cache (cached data blocks), and redo log buffer.
- The PGA is a private memory region per session: sort/hash work areas and session-specific cursor state, torn down on disconnect.
- Postgres's shared_buffers and work_mem draw the same shared-vs-private line as Oracle's SGA and PGA, just with different sizing philosophies.
- Every query touches both regions: shared pool and buffer cache to parse and read, PGA to sort and hash.
💻 Code example
-- A rough proxy for "is my working set staying cached" SELECT round(1 - (phy.value / (phy.value + log.value)), 4) AS buffer_cache_hit_ratio FROM v$sysstat phy, v$sysstat log WHERE phy.name = 'physical reads' AND log.name = 'session logical reads';
Want a visual for this concept?
Generate a diagram tailored to “Database Memory Architecture Fundamentals” — the AI picks whichever visual (flowchart, comparison, sequence, etc.) best fits.
Sign in to generate a visual →