Undo / TempDB / temporary space pressure

Undo/rollback space, temporary (temp) space, or TempDB is under pressure. We determine where the pressure is and drill down to the root cause, such as a large sort/hash, a long query/transaction, or a space leak.

Where is the pressure: undo, temp, or tempdb contention?

Classify the source of pressure by looking at space usage: is the undo/rollback tablespace filling up, the general temporary (temp) space, or, in MSSQL, is it TempDB page allocation latch contention?

SELECT tablespace_name, ROUND(used_percent,1) AS used_pct
FROM dba_tablespace_usage_metrics
WHERE tablespace_name LIKE '%UNDO%' OR tablespace_name LIKE 'TEMP%';

• Undo/rollback space is filling up (ORA-01555 / ORA-30036)
• Temporary (temp) space is filling up (sort/hash spill)
• TempDB allocation latch contention (PFS/GAM/SGAM)

What is consuming undo?

Undo pressure is typically one of two kinds: a long QUERY needs old undo for the consistent read as of its start time (ORA-01555), or a long/open TRANSACTION generates so much undo that it fills the space and leads to ORA-30036.

SELECT s.sid, s.username, t.used_ublk*8/1024 AS undo_mb, s.status, t.start_time
FROM v$transaction t JOIN v$session s ON s.taddr=t.addr
ORDER BY undo_mb DESC;
SELECT MAX(maxquerylen) AS longest_query_sec, MAX(tuned_undoretention) AS tuned_retention FROM v$undostat;

• A long query needs old undo (ORA-01555)
• A long/open transaction is accumulating undo (ORA-30036)

What is filling the temporary space?

Temp pressure comes either from a large sort/hash operation spilling to disk (normal but huge) or from a single session holding temp continuously (open cursor, leak, never-ending query).

SELECT s.sid, s.username, u.tablespace, u.blocks*8/1024 AS temp_mb, u.sqladdr
FROM v$tempseg_usage u JOIN v$session s ON s.saddr=u.session_addr
ORDER BY temp_mb DESC;

• A large sort/hash operation is spilling to disk
• A single session is holding temp (leak/open cursor)

Is the TempDB contention about allocation or volume?

Two different problems occur in MSSQL TempDB: PAGELATCH contention on PFS/GAM/SGAM pages (many small temporary objects), or a large sort/version store filling TempDB by volume.

SELECT session_id, wait_type, wait_duration_ms, resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH%' AND resource_description LIKE '2:%';
SELECT COUNT(*) AS tempdb_data_files FROM sys.master_files WHERE database_id=2 AND type=0;

• PFS/GAM/SGAM latch contention
• A large sort/version store is filling TempDB

Root cause: Long query + insufficient undo (ORA-01555)

The old undo that the long query needs for a consistent read was overwritten because retention expired or space was insufficient.

Root cause: A long/open transaction filled undo (ORA-30036)

A single huge DML or an open transaction with a delayed commit generated too much undo; the UNDO tablespace could not extend and ORA-30036 was raised.

Root cause: A large sort/hash is filling temporary space

Large sort/hash/group by operations that do not fit in memory spill to temporary space and can fill it.

Root cause: A session is holding temp space (leak)

A single session does not release temporary space because of unclosed cursors, a never-ending query, or open temporary tables.

Root cause: TempDB PFS/GAM/SGAM latch contention

When many sessions create small temporary objects at the same time, PAGELATCH contention arises on TempDB's allocation map pages (PFS/GAM/SGAM).