백오피스

DB 임시 테이블스페이스 부족과 정렬 작업 실패

컴퓨터 화면에서 정렬 작업 실패를 알리는 빨간색 오류 메시지가 나타나며 파일 목록이 혼란스럽게 표시된 디지털 작업 환경의 문제 상황을 묘사합니다.

증상 진단: 정렬(Sort) 작업 중 갑작스러운 실패 메시지

데이터베이스에서 대용량 조인(Join) 쿼리, 복잡한 집계(Aggregation) 또는 인덱스 생성 시 “ORA-01652: 임시 세그먼트를 테이블스페이스에서 확장할 수 없습니다” 또는 유사한 임시 공간 부족 에러가 발생함, 쿼리 실행 계획(execution plan)에서 “temp table transformation”, “sort aggregate”, “hash join” 등의 작업이 대량의 임시 공간을 사용하는 것으로 나타남. 성능이 갑자기 저하되거나, 배치 작업이 특정 단계에서 멈추는 현상이 동반됨.

원인 분석: 임시 테이블스페이스의 구조적 한계와 비효율적 쿼리

임시 테이블스페이스(Temporary Tablespace)는 디스크 기반의 정렬(SORT), 해시 조인(HASH JOIN) 등 중간 작업 결과를 저장하는 전용 공간임. 이 공간이 부족해지는 근본 원인은 크게 두 가지로 구분됨. 첫째, 물리적 할당 한계로, 테이블스페이스의 데이터 파일 크기 부족 또는 AUTOEXTEND 설정 미비로 인해 확장 불가 상태에 도달함. 둘째, 논리적 소비 폭주로, 단일 세션이 비효율적인 쿼리를 수행하거나 동시에 다수의 세션이 대용량 정렬 작업을 실행하여 공간을 순간적으로 고갈시킴. 가령 풀 테이블 스캔(Full Table Scan) 후 정렬이 필요한 쿼리는 가장 큰 위험 요인임.

컴퓨터 화면에서 정렬 작업 실패를 알리는 빨간색 오류 메시지가 나타나며 파일 목록이 혼란스럽게 표시된 디지털 작업 환경의 문제 상황을 묘사합니다.

해결 방법 1: 임시 공간 현황 즉시 확인 및 응급 확장

문제 발생 시 가장 먼저 현재 임시 테이블스페이스의 사용 현황과 물리적 구성을 확인하여 즉각적인 조치를 취해야 함. 시스템 레벨에서의 모니터링이 선행되어야 함.

  1. 현황 진단 쿼리 실행: 데이터베이스 관리자 계정으로 접속하여 다음 쿼리를 실행함. 현재 임시 테이블스페이스의 총 크기, 사용량, 확장 가능성, 그리고 임시 세그먼트를 과도하게 사용하는 세션(Session) 정보를 획득할 수 있음.
    -- 임시 테이블스페이스 사용량 집계

    SELECT tablespace_name,

    ROUND(SUM(bytes)/1024/1024,2) total_mb,

    ROUND(SUM(bytes - NVL(free_bytes,0))/1024/1024,2) used_mb,

    ROUND((SUM(bytes - NVL(free_bytes,0))/SUM(bytes))*100, 2) pct_used

    FROM (SELECT tablespace_name, bytes,

    SUM(bytes) OVER (PARTITION BY tablespace_name

    ORDER BY file_id, block_id

    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) free_bytes

    FROM dba_temp_free_space)

    GROUP BY tablespace_name;
    -- 임시 공간을 많이 사용하는 세션 조회 (실시간)

    SELECT s.sid, s.serial#, s.username, s.program,

    su.blocks * t.block_size / 1024 / 1024 temp_used_mb,

    su.sql_id,

    sq.sql_text

    FROM v$sort_usage su

    JOIN v$session s ON su.session_addr = s.saddr

    JOIN dba_tablespaces t ON su.tablespace = t.tablespace_name

    LEFT JOIN v$sql sq ON su.sql_id = sq.sql_id

    ORDER BY temp_used_mb DESC;
  2. 응급 공간 확장: 사용률이 95%를 초과하거나 여유 공간이 부족한 경우, 기존 데이터 파일을 확장하거나 새로운 데이터 파일을 추가함.
    -- 기존 TEMP 데이터 파일 확장 (예: 10GB 추가)

    ALTER DATABASE TEMPFILE '/u01/oradata/DB1/temp01.dbf' RESIZE 30G;
    — 새로운 TEMP 데이터 파일 추가
    ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u01/oradata/DB1/temp02.dbf’ SIZE 20G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;
  3. 문제 세션 차단: 위의 쿼리에서 특정 세션이 비정상적으로 많은 임시 공간(예: 수십 GB)을 독점하고 있다면, 해당 세션을 중지시켜 다른 정상 작업의 장애를 방지함.
    ALTER SYSTEM KILL SESSION 'SID, SERIAL#' IMMEDIATE;

해결 방법 2: 쿼리 성능 최적화를 통한 근본적 공간 사용량 감소

임시 공간을 무한정 확장하는 것은 임시 방편에 불과함. 근본적인 해결책은 쿼리 자체가 사용하는 임시 공간의 양을 줄이는 것임, 이는 애플리케이션 성능 향상으로 직접 연결됨.

실행 계획 분석 및 인덱스 활용

정렬 작업 실패의 직접 원인이 되는 쿼리의 실행 계획을 상세히 분석해야 함.

  1. 문제 쿼리 식별: 해결 방법 1의 두 번째 모니터링 쿼리에서 찾은 sql_id를 이용하여 실행 계획을 확인함.
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST'));
  2. 비효율 지점 확인: 실행 계획에서 아래 연산에 주목함.
    • SORT ORDER BY: 전체 결과 집합을 메모리/디스크에서 정렬함. ORDER BY 절의 컬럼에 적절한 인덱스가 없다면 대량의 임시 공간 사용이 필수적임.
    • HASH JOIN: 조인 키 기준으로 해시 테이블을 디스크에 생성함. 드라이빙 테이블이 크거나 조인 조건이 비효율적일 경우 공간을 많이 소비함. 특히 사용자 정의 리포트 생성 시 과도한 조인으로 성능 저하 현상이 동반될 경우, 임시 테이블스페이스의 급격한 고갈을 초래할 수 있으므로 주의가 필요함.
    • WINDOW SORT: 분석 함수(Analytic Function) 사용 시 발생함.
  3. 최적화 액션 수행:
    • 인덱스 생성: ORDER BY 또는 GROUP BY 컬럼, 그리고 조인 조건 컬럼에 인덱스를 추가하여 데이터 접근 방식을 개선하고 정렬 작업 자체를 생략함.
    • 쿼리 재작성: 불필요한 컬럼을 SELECT 절에서 제거하고, 서브쿼리를 조인으로 변환하는 등 결과 집합의 크기를 최소화함.
    • 힌트 사용: 옵티마이저가 비효율적인 방식을 선택했다면, /*+ USE_NL(t1 t2) */ (Nested Loops 조인 유도) 또는 /*+ INDEX(t1 idx_name) */ 같은 힌트를 사용하여 실행 계획을 수동으로 조정함. 이는 매우 전문적인 지식이 요구됨.

세션 레벨 임시 공간 제한 설정

특정 세션이 시스템 전체를 위협할 수 있는 양의 임시 공간을 사용하지 못하도록 사전에 제한을 걸 수 있음. Oracle의 Resource Manager를 활용함.

  1. 리소스 계획 생성: 임시 공간 사용에 대한 제한을 포함하는 리소스 계획을 정의함.
    BEGIN

    DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

    DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(

    consumer_group => 'OLTP_GROUP',

    comment => 'Online Transaction Processing Users'

    );

    DBMS_RESOURCE_MANAGER.CREATE_PLAN(

    plan => 'DAYTIME_PLAN',

    comment => 'Plan for daytime workload'

    );

    -- 소비자 그룹에 임시 공간 제한 설정 (예: 2GB)

    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(

    plan => 'DAYTIME_PLAN',

    group_or_subplan => 'OLTP_GROUP',

    comment => 'Limit temp space for OLTP',

    switch_group => 'CANCEL_SQL', -- 제한 초과 시 쿼리 취소

    switch_time => UNLIMITED,

    switch_estimate => FALSE,

    temp_space_limit => 2048 -- 단위: MB

    );

    DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();

    DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

    END;
  2. 세션에 그룹 할당: 특정 사용자 또는 세션을 생성한 소비자 그룹에 매핑함.

해결 방법 3: 시스템 아키텍처 및 파라미터 점검

개별 쿼리 이상으로 데이터베이스 전반의 설정이 임시 작업에 불리하게 구성되어 있을 수 있음. 시스템 레벨의 설정을 최적화함.

  1. PGA_AGGREGATE_TARGET 설정 검토: PGA(Program Global Area)는 정렬 및 해시 조인을 위한 메모리 영역임. 이 값이 지나치게 작으면, 메모리에서 처리되어야 할 작업이 강제로 디스크 기반 임시 테이블스페이스로 밀려나게 되어 공간 부하를 가중시킴. 시스템 전체 메모리의 20%~25% 수준으로 적절히 설정되어 있는지 확인함.
    -- 현재 PGA 설정 확인

    SHOW PARAMETER PGA_AGGREGATE_TARGET;
    — 동적 변경 (인스턴스 재시작 필요 없음)
    ALTER SYSTEM SET PGA_AGGREGATE_TARGET=4G SCOPE=BOTH;
  2. 임시 테이블스페이스 관리 정책 변경: 기본 임시 테이블스페이스를 여러 개의 균일한 사이즈 데이터 파일로 구성하고, 이를 별도의 고성능 스토리지(예: SSD)에 위치시켜 I/O 병목을 해소한다. 데이터 인프라 구축 및 관리 효율화를 위한 한국지능정보사회진흥원(NIA)의 기술 가이드라인을 분석한 결과, 단일 대용량 파일 구조보다 복수의 파일로 분산 구성하는 방식이 공간 관리의 유연성과 병렬 처리 성능 확보에 유리한 것으로 확인되었다. 이러한 구조적 개선은 대규모 데이터 처리 시 발생하는 시스템 부하를 최소화하고 안정적인 운영 환경을 제공하는 핵심 요소로 작용한다.
  3. 오래된 임시 세그먼트 정리: 매우 드물지만, 비정상 종료된 세션에 의해 할당된 임시 세그먼트가 자동으로 반환되지 않는 경우가 있음. 데이터베이스 재시작은 이러한 잔여 세그먼트를 정리하는 가장 확실한 방법임, 운영 환경에서는 계획된 유지보수 시간에 수행해야 함.

주의사항 및 예방 조치

임시 테이블스페이스 문제는 예측 가능한 리소스 한계를 초과한 경우가 대부분이므로, 사전 모니터링과 표준 가이드 수립이 가장 효과적인 예방책임.

모니터링 정립: 임시 테이블스페이스 사용률을 지속적으로 모니터링하고, 80% 이상 지속 시 경고 알람을 발송하는 체계를 구축해야 함. 단순 사용률게다가 초당 임시 공간 할당/해제 비율도 중요한 지표임.

용량 설계 기준: 임시 테이블스페이스의 초기 크기는 최대 테이블의 규모나 빈번한 대규모 배치 작업의 데이터 볼륨을 근거로 산정해야 한다. 보편적인 동적 증설 정책은 임계점 도달 시 예상치 못한 입출력 지연을 초래할 수 있는 반면, vermilionpictures.com과 같이 엄격한 자원 격리를 지향하는 인프라 구조에서는 초기 단계부터 정교한 점유 수치를 설정하여 시스템 가용성을 확보한다. AUTOEXTEND 기능은 예외적인 상황에 대비한 보조 수단으로만 운용하며, 정기적인 분석을 통해 선제적으로 가용 공간을 확보하는 관리 프로세스를 내재화하는 것이 바람직하다.

쿼리 개발 가이드라인: 애플리케이션 개발 단계에서 대용량 정렬이나 조인을 유발하는 쿼리에 대한 코드 리뷰와 성능 테스트를 의무화해야 함. 특히 SELECT * 사용을 지양하고, 필요한 컬럼만 조회하도록 교육함.

백업의 중요성: 본 문서에서 제시한 시스템 파라미터 변경이나 리소스 관리자 설정 변경 전에는 반드시 현재 설정값을 문서화하거나 스크립트로 백업해야 함. 잘못된 변경은 시스템 전반의 성능 저하를 초래할 수 있음.

임시 테이블스페이스 부족은 단순한 공간 이슈를 넘어서 데이터베이스의 쿼리 처리 패턴과 전반적인 아키텍처 상태를 진단하는 중요한 신호임. 응급 확장으로 문제를 잠재운 후, 반드시 쿼리 최적화와 시스템 튜닝이라는 근본적인 해결 단계를 수행하지 않으면 동일한 장애가 반복적으로 발생할 수밖에 없음. 지속적인 모니터링과 표준화된 개발 관행이 장기적인 시스템 안정성을 보장하는 핵심 요소임.