Pressão em undo / TempDB / espaço temporário

O espaço de undo/rollback, o espaço temporário (temp) ou o TempDB está sob pressão. Determinamos onde está a pressão e descemos até a causa raiz, como um grande sort/hash, uma consulta/transação longa ou um vazamento de espaço.

Onde está a pressão: undo, temp ou contenção no tempdb?

Classifique a origem da pressão observando o uso de espaço: está enchendo o tablespace de undo/rollback, o espaço temporário (temp) geral, ou, no MSSQL, trata-se de contenção de latch na alocação de páginas do TempDB?

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%';

• O espaço de undo/rollback está enchendo (ORA-01555 / ORA-30036)
• O espaço temporário (temp) está enchendo (spill de sort/hash)
• Contenção de latch de alocação no TempDB (PFS/GAM/SGAM)

O que está consumindo o undo?

A pressão em undo normalmente é de dois tipos: uma QUERY longa precisa de undo antigo para a leitura consistente referente ao seu horário de início (ORA-01555), ou uma TRANSAÇÃO longa/aberta gera tanto undo que enche o espaço e leva ao 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;

• Uma consulta longa precisa de undo antigo (ORA-01555)
• Uma transação longa/aberta está acumulando undo (ORA-30036)

O que está enchendo o espaço temporário?

A pressão em temp vem de uma grande operação de sort/hash fazendo spill para o disco (normal, mas enorme) ou de uma única sessão segurando temp continuamente (cursor aberto, vazamento, consulta que nunca termina).

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;

• Uma grande operação de sort/hash está fazendo spill para o disco
• Uma única sessão está segurando temp (vazamento/cursor aberto)

A contenção no TempDB é de alocação ou de volume?

Dois problemas diferentes ocorrem no TempDB do MSSQL: contenção de PAGELATCH nas páginas PFS/GAM/SGAM (muitos objetos temporários pequenos), ou um grande sort/version store enchendo o TempDB em 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;

• Contenção de latch em PFS/GAM/SGAM
• Um grande sort/version store está enchendo o TempDB

Causa raiz: consulta longa + undo insuficiente (ORA-01555)

O undo antigo de que a consulta longa precisa para uma leitura consistente foi sobrescrito porque a retenção expirou ou o espaço era insuficiente.

Causa raiz: uma transação longa/aberta encheu o undo (ORA-30036)

Um único DML enorme ou uma transação aberta com commit atrasado gerou undo demais; o tablespace UNDO não conseguiu se estender e o ORA-30036 foi gerado.

Causa raiz: um grande sort/hash está enchendo o espaço temporário

Grandes operações de sort/hash/group by que não cabem na memória fazem spill para o espaço temporário e podem enchê-lo.

Causa raiz: uma sessão está segurando espaço temporário (vazamento)

Uma única sessão não libera o espaço temporário por causa de cursores não fechados, uma consulta que nunca termina ou tabelas temporárias abertas.

Causa raiz: contenção de latch em PFS/GAM/SGAM no TempDB

Quando muitas sessões criam pequenos objetos temporários ao mesmo tempo, surge contenção de PAGELATCH nas páginas de mapa de alocação do TempDB (PFS/GAM/SGAM).