Temp Usage, Sorts, Hashes, Spill Behavior, PGA Work Areas, and Parallel Execution Pressure

Learning outcomes

A large ServiceHub report suddenly writes gigabytes to TEMP. The DBA adds a bigger tempfile and the report still takes the same time. TEMP capacity prevented ORA-01652, but it did not remove the sort/hash spill. Oracle SQL workareas try to run optimal in PGA; with insufficient memory they use one-pass or multi-pass algorithms that write/read TEMP. Parallel execution can multiply the number of simultaneous workareas because many PX workers process the statement.

1. TEMP is workspace, not automatically the root cause

Sorts, hash joins/aggregations, bitmap operations, temporary LOBs and other operations can allocate temporary segments. A bigger TEMP tablespace may keep the statement alive but cannot make a multipass hash/sort become optimal. Diagnose the SQL/workarea first.

SELECT  username,  session_addr,  sql_id,  segtype,  tablespace,  blocks,  con_idFROM v$tempseg_usageORDER BY blocks DESC;

2. Active workareas expose memory and spill state

SELECT  sid,  sql_id,  sql_plan_line_id,  operation_type,  work_area_size,  expected_size,  actual_mem_used,  max_mem_used,  number_passes,  tempseg_sizeFROM v$sql_workarea_activeORDER BY tempseg_size DESC NULLS LAST;

NUMBER_PASSES=0 indicates an optimal in-memory execution; one or more passes means external/TEMP work. A snapshot after the statement finishes may miss the active view, so preserve SQL ID and inspect historical cursor workarea data while the cursor remains available.

3. Workarea histogram should be read as interval deltas

SELECT  low_optimal_size,  high_optimal_size,  optimal_executions,  onepass_executions,  multipasses_executionsFROM v$sql_workarea_histogramORDER BY low_optimal_size;

The histogram is cumulative since instance startup. A few ancient multipass executions do not prove today's incident. Snapshot before/after the workload, just like Chapter 22's wait/time model.

4. Reproducible Free lab dataset

BEGIN  EXECUTE IMMEDIATE 'DROP TABLE sh23_workarea_a PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/BEGIN  EXECUTE IMMEDIATE 'DROP TABLE sh23_workarea_b PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE sh23_workarea_a ASSELECT  LEVEL AS id,  MOD(LEVEL,25000) AS join_key,  MOD(LEVEL,5000) AS group_key,  RPAD('a',220,'a') AS payloadFROM dualCONNECT BY LEVEL <= 120000;CREATE TABLE sh23_workarea_b ASSELECT  LEVEL AS id,  MOD(LEVEL,25000) AS join_key,  RPAD('b',220,'b') AS payloadFROM dualCONNECT BY LEVEL <= 120000;BEGIN  DBMS_STATS.GATHER_TABLE_STATS(USER,'SH23_WORKAREA_A');  DBMS_STATS.GATHER_TABLE_STATS(USER,'SH23_WORKAREA_B');END;/

5. Controlled educational spill: temporarily use MANUAL workarea sizing

Automatic workarea sizing (WORKAREA_SIZE_POLICY=AUTO) is the recommended normal mode. To make the mechanism visible on a small Free lab, this session temporarily switches to MANUAL with deliberately tiny sort/hash areas. This is an educational fault injection, not a production recommendation.

ALTER SESSION SET workarea_size_policy=MANUAL;ALTER SESSION SET sort_area_size=65536;ALTER SESSION SET hash_area_size=131072;SELECT /*+ gather_plan_statistics use_hash(b) */       a.group_key,       COUNT(*) AS row_count,       SUM(LENGTH(a.payload)+LENGTH(b.payload)) AS bytes_seenFROM sh23_workarea_a aJOIN sh23_workarea_b b  ON b.join_key=a.join_keyGROUP BY a.group_keyORDER BY bytes_seen DESC;SELECT *FROM TABLE(  DBMS_XPLAN.DISPLAY_CURSOR(    NULL,NULL,'ALLSTATS LAST +MEMSTATS +IOSTATS'  ));

Look for memory/TEMP columns on sort/hash operations. Depending on the exact optimizer plan and available resources, the statement may use one-pass or multi-pass workareas. The lab's purpose is to create pressure, not to promise a fixed number of TEMP bytes.

6. Verify cursor workarea outcome

SELECT  operation_type,  policy,  estimated_optimal_size,  estimated_onepass_size,  last_memory_used,  last_execution,  last_degree,  last_tempseg_size,  max_tempseg_sizeFROM v$sql_workareaWHERE sql_id=:sql_idORDER BY child_number,operation_id;

LAST_EXECUTION values such as OPTIMAL, ONE PASS and MULTI-PASS describe the last execution's workarea behavior. Use the SQL ID from the actual workload statement, not the diagnostic query that follows it.

7. Repair the injected condition first

ALTER SESSION SET workarea_size_policy=AUTO;

After restoring AUTO, rerun under the same data/statistics/cache/concurrency conditions and compare. Do not increase instance PGA simply because the artificial MANUAL experiment spilled.

8. Query/schema changes can remove the workarea

If a report sorts because it requests an order satisfied by an index, or hashes millions of rows because a join is nonselective/badly estimated, memory is not the first fix. Use runtime plan/cardinality evidence from Chapter 9. Pre-filter, correct statistics, use an appropriate index, reduce selected rows/columns, preaggregate, or restructure the batch when semantics allow.

9. PGA/TEMP evidence for real incidents

SELECT name,value,unitFROM v$pgastatWHERE name IN (  'aggregate PGA target parameter',  'aggregate PGA auto target',  'total PGA allocated',  'maximum PGA allocated',  'over allocation count',  'extra bytes read/written')ORDER BY name;SELECT  tablespace_name,  tablespace_size,  allocated_space,  free_spaceFROM dba_temp_free_spaceORDER BY tablespace_name;

Increasing TEMP can be necessary for capacity/safety, but if extra bytes read/written and one-pass/multipass outcomes rise, investigate workarea/PGA/query mechanisms too.

10. Parallel execution multiplies workareas

Parallel SQL can have multiple PX server sets; each worker can own sort/hash workareas. Raising Degree of Parallelism (DOP) can therefore multiply PGA/TEMP demand even when one serial execution fits comfortably. Current 26ai licensing marks parallel query/DML unavailable in Free, so no mandatory lab enables it.

SELECT  qcsid,  sid,  server_group,  server_set,  degree,  req_degreeFROM v$px_sessionORDER BY qcsid,server_group,server_set,sid;SELECT  sid,  sql_id,  operation_type,  actual_mem_used,  number_passes,  tempseg_sizeFROM v$sql_workarea_activeORDER BY sql_id,sid;

11. Deliberately wrong: add TEMP until the query is fast

TEMP size prevents allocation failure; it does not eliminate external sorting/hashing. A query can happily consume a much larger TEMP tablespace while remaining slow and harming other workloads. The safe sequence is SQL/cardinality/access path → workarea/PGA evidence → concurrency/DOP → TEMP capacity, with each change measured.

12. Cleanup

ALTER SESSION SET workarea_size_policy=AUTO;DROP TABLE sh23_workarea_a PURGE;DROP TABLE sh23_workarea_b PURGE;

13. Production judgment

Observe the exact SQL/workarea while active whenever possible. Snapshot workarea histograms across the incident, verify PGA over-allocation and TEMP headroom, then choose the smallest mechanism-level change. Never leave a diagnostic session in MANUAL workarea mode unintentionally.

The Free path is serial and uses only core views; parallel query/DML remains entitlement-gated. No pack, restart or COMPATIBLE increase is needed for the mandatory lab. Lesson 5 closes the chapter by turning all these measurements into a reproducible benchmark and regression gate instead of anecdotal “it feels faster” tuning.

Authoritative references

• V$SQL_WORKAREA_ACTIVE — active memory/TEMP workareas
• V$SQL_WORKAREA_HISTOGRAM — optimal/one-pass/multipass counts
• V$TEMPSEG_USAGE — current TEMP consumers
• WORKAREA_SIZE_POLICY — AUTO/MANUAL behavior
• Using Parallel Execution — PX work distribution/resource implications