Oracle DBA · Lesson 10B
PGA Workareas and Temporary I/O
Lesson 10A covered specialty SGA pools. This cut is about private workareas: when a sort or hash fits in PGA, when it spills to TEMP, and how the aggregate target and limit differ.
Watch on YouTube
Oracle DBA Lesson 10B — PGA Workareas and Temporary I/O
AI Ops Pro
When private workarea memory is not enough
A SQL sort or hash needs a workarea inside the server process’s private PGA. If that workarea can hold the intermediate data, the operation finishes in memory. If it cannot, Oracle spills to the temporary tablespace — TEMP I/O. A “slow query” is often temp I/O, not CPU.
- PGA — private per process (not shared SGA).
- Workarea — the slice of PGA for that sort/hash (and similar) operation.
- Spill — intermediate data goes to TEMP when the workarea is too small.
Target guides — it does not cap
PGA_AGGREGATE_TARGET is automatic workarea guidance. It helps size workareas across the instance. It is not a hard ceiling. Total PGA can still exceed the target because of workload demand, untunable memory, and process activity. Treat a breach as a signal to measure — not as proof the parameter “failed.”
Limit is separate enforcement
PGA_AGGREGATE_LIMIT is a separate aggregate threshold. When the instance exceeds the limit, Oracle can abort calls and terminate eligible sessions (documented exemptions apply). It is not an equal per-session allowance. Casual lowering can create an outage.
Mental model: the target guides; the limit enforces.
Read v$pgastat with unit and time meaning
Counters are not interchangeable. Read VALUE together with UNIT, and know whether the name is current, peak, or cumulative. Compare samples around real workload — do not manufacture pressure with giant Cartesian sorts or by casually cutting the limit.
SHOW PARAMETER pga_aggregateTeaching peek — confirm both target and limit before you change either.
SELECT name, value, unit
FROM v$pgastat
WHERE name IN (
'total PGA allocated',
'maximum PGA allocated',
'over allocation count',
'extra bytes read/written'
);Classify each row as current / peak / cumulative. Pair samples with the SQL and concurrency that were running.
Measure safely
- Use ordinary existing activity as the workload.
- Take two samples (before and after) and classify counters.
- Identify the SQL + concurrency; explain TEMP I/O from those facts.
- No giant Cartesian sort · no casual limit reduction just to “see what happens.”
Gotchas
- PGA workareas are private — the large pool does not replace them for dedicated-server sorts/hashes.
- Spill is intermediate TEMP I/O for that operation, not “the whole PGA moved to disk.”
PGA_AGGREGATE_TARGET≠ hard cap;PGA_AGGREGATE_LIMIT≠ per-session fair share.- Read unit + interval + workload context before you tune.
Quick quiz
Choose one answer, then submit. You’ll see the correct answer and a short why.
No comments:
Post a Comment