DBAtlas · 데이터베이스 운영 아틀라스

Undo / TempDB / 임시 공간 압박

undo/rollback 공간, 임시(temp) 공간 또는 TempDB가 압박을 받고 있다. 압박이 어디에 있는지 파악하고 대규모 정렬/해시, 긴 쿼리/트랜잭션, 공간 누수 같은 근본 원인으로 좁혀 간다.

압박은 어디인가: undo, temp, 아니면 tempdb 경합?

공간 사용량을 보고 압박의 원인을 분류한다: undo/rollback 테이블스페이스가 차고 있는가, 일반 임시(temp) 공간인가, 아니면 MSSQL에서 TempDB 페이지 할당 latch 경합인가?

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 공간이 차고 있음(ORA-01555 / ORA-30036)
  • 임시(temp) 공간이 차고 있음(sort/hash spill)
  • TempDB 할당 latch 경합(PFS/GAM/SGAM)

무엇이 undo를 소비하는가?

Undo 압박은 대개 두 가지 중 하나다: 긴 쿼리가 시작 시점 기준의 일관된 읽기를 위해 오래된 undo를 필요로 하거나(ORA-01555), 길거나 열린 트랜잭션이 undo를 너무 많이 생성해 공간을 채우고 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;
  • 긴 쿼리가 오래된 undo를 필요로 함(ORA-01555)
  • 길거나 열린 트랜잭션이 undo를 축적 중(ORA-30036)

무엇이 임시 공간을 채우고 있는가?

Temp 압박은 대규모 정렬/해시 작업이 디스크로 spill되는 경우(정상이지만 거대함)이거나 단일 세션이 temp를 계속 보유하는 경우(열린 커서, 누수, 끝나지 않는 쿼리)에서 온다.

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;
  • 대규모 정렬/해시 작업이 디스크로 spill됨
  • 단일 세션이 temp를 보유 중(누수/열린 커서)

TempDB 경합은 할당 문제인가, 용량 문제인가?

MSSQL TempDB에서는 두 가지 다른 문제가 발생한다: PFS/GAM/SGAM 페이지의 PAGELATCH 경합(다수의 작은 임시 객체), 또는 대규모 정렬/version store가 용량으로 TempDB를 채우는 경우.

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 경합
  • 대규모 정렬/version store가 TempDB를 채움

근본 원인: 긴 쿼리 + undo 부족(ORA-01555)

긴 쿼리가 일관된 읽기에 필요로 하는 오래된 undo가 보존 기간 만료 또는 공간 부족으로 덮어써졌다.

근본 원인: 길거나 열린 트랜잭션이 undo를 채움(ORA-30036)

단일 거대 DML이나 커밋이 지연된 열린 트랜잭션이 undo를 너무 많이 생성했다. UNDO 테이블스페이스가 확장되지 못해 ORA-30036이 발생했다.

근본 원인: 대규모 정렬/해시가 임시 공간을 채움

메모리에 들어가지 않는 대규모 sort/hash/group by 작업이 임시 공간으로 spill되어 공간을 채울 수 있다.

근본 원인: 세션이 임시 공간을 보유 중(누수)

닫히지 않은 커서, 끝나지 않는 쿼리, 열린 임시 테이블 때문에 단일 세션이 임시 공간을 해제하지 않는다.

근본 원인: TempDB PFS/GAM/SGAM latch 경합

다수의 세션이 동시에 작은 임시 객체를 생성하면 TempDB의 할당 맵 페이지(PFS/GAM/SGAM)에서 PAGELATCH 경합이 발생한다.

DBAtlas에서 열기 →