Pression sur undo / TempDB / espace temporaire

L'espace undo/rollback, l'espace temporaire (temp) ou TempDB est sous pression. Nous déterminons où se situe la pression et remontons à la cause racine, comme un grand tri/hachage, une requête/transaction longue ou une fuite d'espace.

Où est la pression : undo, temp ou contention tempdb ?

Classez la source de pression en examinant l'utilisation de l'espace : est-ce le tablespace undo/rollback qui se remplit, l'espace temporaire (temp) général ou, dans MSSQL, une contention de latch d'allocation de pages 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%';

• L'espace undo/rollback se remplit (ORA-01555 / ORA-30036)
• L'espace temporaire (temp) se remplit (débordement de tri/hachage)
• Contention de latch d'allocation TempDB (PFS/GAM/SGAM)

Qu'est-ce qui consomme l'undo ?

La pression undo est généralement de deux types : une REQUÊTE longue a besoin d'un ancien undo pour la lecture cohérente à son heure de début (ORA-01555), ou une TRANSACTION longue/ouverte génère tant d'undo qu'elle remplit l'espace et provoque 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;

• Une requête longue a besoin d'un ancien undo (ORA-01555)
• Une transaction longue/ouverte accumule de l'undo (ORA-30036)

Qu'est-ce qui remplit l'espace temporaire ?

La pression temp provient soit d'une grande opération de tri/hachage débordant sur disque (normale mais énorme), soit d'une seule session qui retient le temp en permanence (curseur ouvert, fuite, requête sans fin).

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;

• Une grande opération de tri/hachage déborde sur disque
• Une seule session retient le temp (fuite/curseur ouvert)

La contention TempDB concerne-t-elle l'allocation ou le volume ?

Deux problèmes distincts surviennent dans TempDB de MSSQL : une contention PAGELATCH sur les pages PFS/GAM/SGAM (nombreux petits objets temporaires), ou un grand tri/version store qui remplit TempDB en 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;

• Contention de latch PFS/GAM/SGAM
• Un grand tri/version store remplit TempDB

Cause racine : requête longue + undo insuffisant (ORA-01555)

L'ancien undo dont la requête longue a besoin pour une lecture cohérente a été écrasé parce que la rétention a expiré ou que l'espace était insuffisant.

Cause racine : une transaction longue/ouverte a rempli l'undo (ORA-30036)

Un seul DML énorme ou une transaction ouverte au commit retardé a généré trop d'undo ; le tablespace UNDO n'a pas pu s'étendre et ORA-30036 a été levée.

Cause racine : un grand tri/hachage remplit l'espace temporaire

Les grandes opérations de tri/hachage/group by qui ne tiennent pas en mémoire débordent vers l'espace temporaire et peuvent le remplir.

Cause racine : une session retient de l'espace temporaire (fuite)

Une seule session ne libère pas l'espace temporaire en raison de curseurs non fermés, d'une requête sans fin ou de tables temporaires ouvertes.

Cause racine : contention de latch PFS/GAM/SGAM dans TempDB

Lorsque de nombreuses sessions créent simultanément de petits objets temporaires, une contention PAGELATCH apparaît sur les pages de cartes d'allocation de TempDB (PFS/GAM/SGAM).