[Oracle] 테이블 통계정보 손실로 인한 배치 성능 저하 문제 해결 사례

개요

대규모 데이터를 다루는 시스템에서 데이터베이스 최적화는 성능 유지의 핵심입니다.

 

최근 백업 시간 단축을 위해 데이터를 정제하는 과정에서,

역설적으로 배치 작업 성능이 급격히 저하되는 이슈를 겪었습니다.

 

이번 포스팅에서는 테이블 통계정보의 불일치가 시스템에 어떤 영향을 주는지, 그리고 이를 어떻게 해결했는지 공유합니다.

---

문제 상황: "백업을 최적화했더니 배치가 느려졌다?"

1. 시스템 배경

저희 시스템은 다음과 같은 데이터 처리 파이프라인으로 구성되어 있습니다.

• Source: MSSQL (실시간 CRM 데이터)
• Data Warehouse: Oracle 데이터베이스에 매일 정해진 시간에 스냅샷 적재
• ETL: MSSQL → Oracle (스키마 및 형변환 적재)
• 배치 처리: 변환된 데이터를 기반으로 다단계 집계 프로세스를 통한 통계 데이터 생성
• 데이터 백업: 데이터 무결성을 위한 정기 Full Backup

2. 발생한 문제

시스템 운영이 4년을 경과하면서 데이터 규모가 급증했고, Full Backup에 10시간이 소요되면서 일일 배치 작업과 충돌하기 시작했습니다. 이를 해결하기 위해 다음 조치를 취했습니다.

• 미사용 임시/테스트 테이블 삭제
• 중간 처리 테이블의 데이터 정제 (배치 완료 후 TRUNCATE 처리)

백업 시간은 단축되었으나, 다음 날부터 평소 1시간이면 끝나던 배치 작업이 5시간 이상 소요되는 심각한 지연 현상이 발생했습니다.

---

근본 원인: 통계정보와 옵티마이저의 엇갈림

통계정보의 역할

Oracle 옵티마이저는 테이블의 행 개수, 데이터 분포도 등 통계정보를 바탕으로 최적의 실행 경로를 결정합니다.

문제의 발생

중간 처리 테이블을 배치 완료 후 TRUNCATE 하도록 변경했으나, 작업 순서와 Oracle의 자동 통계 수집 스케줄이 엇갈리면서 치명적인 문제가 발생했습니다. 

시각	작업 내용	상태 및 문제점
15:00	Table TRUNCATE	데이터 정제 완료. 테이블은 비어 있고 통계는 '낡은 상태(Stale)'가 됨
22:00	자동 통계 수집	⚠️ [함정] Oracle이 0건인 상태를 기준으로 통계를 확정함
06:00	Data INSERT	대량(약 1억 건)의 데이터가 적재됨. 그러나 통계는 여전히 0건으로 기록됨
07:00	배치(SELECT) 실행	❌ [이슈] 옵티마이저는 데이터가 0건인 줄 알고 최악의 경로를 선택함

이로 인해 옵티마이저가 비효율적인 실행 계획을 수립하여 배치 성능이 급격히 저하된 것입니다.

---

현재 시스템 설정 확인

1. 자동 통계 수집 윈도우 설정

저희 시스템은 평일 밤은 짧게, 주말은 길게 통계를 수집하도록 설정되어 있습니다.

WINDOW_NAME	시작 시간	유지 시간 (DURATION)	상세 설명
평일(월~금)	밤 22:00	4시간	밤 10시 시작 ~ 새벽 2시 종료 (시간 제약 있음)
주말(토~일)	오전 06:00	20시간	아침 6시 시작 ~ 다음날 새벽 종료 (넉넉함)

2. 설정 확인용 쿼리

요일별 수집 시간대 및 유지 시간 확인

-- 자동 수집 작업이 활성화(ENABLED)되어 있는지 확인
SELECT CLIENT_NAME, STATUS
FROM DBA_AUTOTASK_CLIENT
WHERE CLIENT_NAME = 'auto optimizer stats collection';

-- 요일별 수집 시간대(Window) 확인
SELECT WINDOW_NAME, REPEAT_INTERVAL, DURATION
FROM DBA_SCHEDULER_WINDOWS;

특정 테이블의 마지막 통계 수집 시점 확인

SELECT OWNER, TABLE_NAME, NUM_ROWS, LAST_ANALYZED 
FROM DBA_TABLES 
WHERE TABLE_NAME = 'TARGET_TABLE_NAME';

---

해결 방법

수동 통계정보 갱신

정확한 실행 계획 유도를 위해 DBMS_STATS 패키지를 사용하여 통계정보를 수동으로 재계산했습니다.

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname          => '[SCHEMA_NAME]',
        tabname          => '[TABLE_NAME]',
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
        method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
        cascade          => TRUE,   -- 인덱스 통계까지 함께 수집
        no_invalidate    => FALSE   -- 기존 커서 무효화 (즉시 반영)
    );
END;

주요 파라미터 설명

• ownname: 스키마 이름
• tabname: 대상 테이블 이름
• estimate_percent: 통계 추정 방식 (AUTO_SAMPLE_SIZE는 Oracle이 자동 결정)
• method_opt: 컬럼별 히스토그램 생성 방식 (AUTO는 필요한 컬럼만 자동 선택)
• cascade: TRUE 설정 시 테이블의 인덱스 통계도 함께 수집하여 옵티마이저의 정확성 향상
• no_invalidate: FALSE 설정 시 기존 커서를 무효화하여 새로운 통계정보가 즉시 반영됨

통계정보 갱신 결과, 배치 작업이 정상 속도로 복귀했습니다.

---

통계정보 상태 점검 쿼리

이후 통계정보 상태를 모니터링하기 위해 다음 쿼리를 작성했습니다.

SELECT 
    owner AS "스키마명", 
    table_name AS "테이블명", 
    num_rows AS "행(Row)개수", 
    last_analyzed AS "마지막통계수집일시"
FROM 
    all_tables
WHERE 
    owner NOT IN ('SYS', 'SYSTEM')
ORDER BY 
    owner, num_rows DESC;

이 쿼리를 통해

• 각 테이블의 현재 데이터 건수 파악
• 통계정보가 언제 마지막으로 갱신되었는지 확인
• 오래된 통계정보를 가진 테이블 식별

---

장기적 개선 전략

1. 백업 최적화 방안

현재 10시간에 소요되는 Full Backup 시간을 단축하기 위해 다음과 같은 개선안을 검토하고 있습니다.

 

단기 개선사항

• 미사용 테이블/인덱스 정리 (진행 완료)
• 데이터 적재 후 통계정보 자동 갱신: 배치 프로세스의 데이터 적재 완료 단계에서 명시적으로 DBMS_STATS.GATHER_TABLE_STATS()를 호출하여 옵티마이저가 항상 최신 통계정보로 실행 계획을 수립하도록 개선 예정

장기 개선사항

• 테이블 파티셔닝: 대용량 테이블을 연도별로 파티셔닝하여 백업 대상 축소 및 선택적 백업 지원 검토

---

결론

이번 사건을 통해 "데이터 적재만큼 메타정보 관리도 중요하다"는 교훈을 얻었습니다.

 

✅ 쿼리 성능이 갑자기 저하된다면, 가장 먼저 last_analyzed 날짜를 확인하세요.

✅ TRUNCATE 등 대량 데이터 작업 이후에는 반드시 통계정보를 갱신해야 합니다.

 

백업 최적화와 성능 안정화를 동시에 달성하기 위해, 위의 개선사항들을 단계별로 추진해 나갈 계획입니다.