Oracle 성능/특성 정리: 실행 계획/통계/UNDO와 ORA-01555(snapshot too old)

Oracle 성능/특성 정리: 실행 계획/통계/UNDO와 ORA-01555(snapshot too old)

이 글은 Oracle Database를 성능/운영 관점에서 길게 정리한 개인 노트입니다. 목표는 "지금 느린 이유"를 증거로 좁히고, 재발을 줄이는 체크리스트를 갖추는 것입니다.

전제: 성능은 DB만으로 결정되지 않습니다. 쿼리 패턴, 데이터 분포, 인덱스/스키마, 애플리케이션 트랜잭션 경계, 인프라(IO/네트워크)가 함께 결정합니다. 그래서 이 글은 '성능 모델(어디서 시간이 쓰이나) -> 설계/쿼리 -> 설정 -> 운영' 순서로 정리합니다.

Oracle은 'Undo/Redo + 옵티마이저/통계 + AWR/ASH'로 강력한 관측과 튜닝 체계를 제공합니다. 대신 개념이 많아 용어를 잡고 순서대로 접근해야 합니다.

이 DB를 언제 선택하나

정답은 항상 케이스 바이 케이스지만, 선택을 빠르게 만들기 위해 기준을 먼저 둡니다. 아래에 해당이 많을수록 이 DB를 고려할 만합니다.

- 미션 크리티컬 OLTP에서 강력한 기능/운영 도구가 필요할 때
- 복잡한 워크로드를 안정적으로 운영하고 전문 튜닝 프로세스를 갖출 수 있을 때
- 엔터프라이즈 HA(예: RAC/데이터가드 등) 구성이 필요한 경우

성능 모델: 어디서 시간이 쓰이나

Oracle 성능의 핵심 축은 Undo/Redo와 옵티마이저(통계)입니다. 읽기 일관성을 위해 Undo가 필요하고, 쓰기는 Redo로 내구성을 확보합니다. Undo가 부족하거나 관리가 잘못되면 snapshot too old 같은 문제가 발생할 수 있습니다.

AWR/ASH 같은 도구로 병목을 wait event 기준으로 분해할 수 있어, 감으로 튜닝하기보다 근거 기반으로 접근하기 좋습니다. 다만 개념이 많아서 어디를 먼저 봐야 하는지 순서를 정하는 게 중요합니다.

핵심 용어 빠른 사전

성능/장애 분석에서 자주 쓰는 단어는 팀마다 다르게 이해되기 쉽습니다. 용어를 짧게 정의하고, 어떤 지표/증상과 연결되는지 같이 적어둡니다.

용어	설명	실무에서 연결되는 것
UNDO	읽기 일관성을 위한 변경 이력	장시간 쿼리에서 snapshot too old와 연결
REDO	내구성/복구 로그	쓰기 부하/로그 스위치/IO와 직결
AWR	워크로드 리포트	상위 SQL/대기 이벤트로 병목 분리
ASH	세션 샘플링	어떤 세션이 무엇을 기다리는지 분석
SGA/PGA	공유/작업 메모리 영역	캐시/정렬/해시 spill과 연결
Hard Parse	실행 계획 생성 비용	파싱 폭주 시 CPU 상승/지연 증가

옵션/핵심 요소(3~6)

자주 부딪히는 핵심 요소를 표로 먼저 고정해 두면, 장애/튜닝 때 팀 커뮤니케이션이 빨라집니다.

항목	의미	언제 쓰는지(실무 상황)
EXPLAIN PLAN	실행 계획 확인	인덱스/조인 전략을 검증할 때
Statistics	옵티마이저 판단 기준	계획이 틀어질 때
UNDO	읽기 일관성을 위한 변경 이력	장시간 쿼리/리포팅에서 오류/지연이 생길 때
Redo/Log	내구성/복구 경로	쓰기 부하/로그 스위치 이슈를 볼 때
AWR/ASH	성능 리포트/세션 샘플링	병목을 wait event로 분리할 때

실무 튜닝 포인트(설정/옵션별)

설정에는 정답이 없습니다. 워크로드(읽기/쓰기/동시성/데이터 크기) 기준으로 관측하고 조정합니다. 아래는 실무에서 가장 자주 만지는 레버를 '항목별'로 정리한 것입니다.

UNDO_TABLESPACE
의미: Undo 저장 공간
언제 만지나: undo 부족이 의심될 때
주의/트레이드오프: 디스크/백업/운영 정책 고려

UNDO_RETENTION
의미: Undo 보존 시간 힌트
언제 만지나: 장시간 읽기 일관성 요구가 있을 때
주의/트레이드오프: 실제 보존은 공간/부하에 의해 제한될 수 있음

SGA_TARGET
의미: SGA 메모리 목표
언제 만지나: 캐시/공유 풀을 균형 잡을 때
주의/트레이드오프: 무작정 늘리면 OS 압박

PGA_AGGREGATE_TARGET
의미: 작업 메모리
언제 만지나: 정렬/해시가 디스크로 spill 될 때
주의/트레이드오프: 메모리 압박/동시성 고려

optimizer statistics
의미: 통계 수집 정책
언제 만지나: 계획이 흔들릴 때
주의/트레이드오프: 수집 타이밍/샘플링이 중요

plan baseline
의미: 계획 고정
언제 만지나: 회귀를 줄이고 싶을 때
주의/트레이드오프: 고정은 회피가 아니라 관리 대상

log file size/switch
의미: redo log 스위치
언제 만지나: 스위치가 너무 잦을 때
주의/트레이드오프: 로그 정책/IO와 연결

쿼리/스키마 설계에서 성능이 갈리는 지점

Oracle에서도 인덱스/조인 순서/통계가 튜닝의 중심입니다. 특히 대형 테이블 조인에서 통계가 틀리면 해시 조인/중첩 루프 선택이 달라지고 성능이 크게 흔들릴 수 있습니다.

장시간 리포팅/배치에서는 Undo가 핵심 리소스가 됩니다. 쓰기 트래픽이 크면 Undo가 빨리 덮어써져 snapshot too old가 발생할 수 있어, 워크로드 분리/스케줄 조정이 필요합니다.

EXPLAIN PLAN FOR SELECT /*+ gather_plan_statistics */ * FROM orders o JOIN users u ON o.user_id = u.id WHERE o.created_at >= SYSDATE - 7;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname => 'APP', tabname => 'ORDERS');
END;
/

진단 명령/쿼리 모음(바로 실행용)

장애 때는 문서 찾을 시간이 없습니다. 아래는 현장에서 자주 쓰는 '첫 5분' 진단 명령/쿼리만 모은 것입니다. 환경/권한에 따라 일부는 제한될 수 있습니다.

-- 세션/대기 확인(권한 필요)
SELECT sid, serial#, status, event, wait_class, seconds_in_wait FROM v$session WHERE status = 'ACTIVE';

-- Undo 상태(권한 필요)
SELECT begin_time, undoblks, txncount, maxquerylen FROM v$undostat ORDER BY begin_time DESC;

-- 실행 계획 확인(환경/권한에 따라)
EXPLAIN PLAN FOR SELECT 1 FROM dual;

자주 하는 실수(운영/튜닝)

실수 체크리스트:
- AWR/ASH 없이 감으로 튜닝을 시작한다
- 장시간 리포트/배치를 OLTP 피크 시간에 돌려 Undo/Redo 병목을 만든다
- Undo 정책(공간/retention)을 워크로드 성장에 맞춰 재평가하지 않는다
- 통계 수집 정책이 불명확해 계획이 흔들리는데도 방치한다
- 플랜 고정(plan baseline)을 '영구 해결'로 착각하고 관리하지 않는다
- 로그 스위치가 과도한데도 redo/log 정책을 점검하지 않는다

운영/모니터링 체크리스트

운영에서는 AWR/ASH로 상위 대기 이벤트, top SQL, Undo 사용량, redo log 스위치, 공유 풀 이슈를 봅니다. 튜닝은 SQL 하나가 아니라 워크로드를 보는 게 일반적입니다.

공통 체크리스트:
- 느린 쿼리 top N을 지속 수집(시간/호출수/총시간)
- 지연(p95/p99)과 오류율을 애플리케이션 지표와 함께 본다
- 캐시/메모리/디스크 IO 중 무엇이 병목인지 먼저 분리한다
- 복제/HA를 쓰면 일관성 요구가 높은 기능을 분리한다
- 튜닝/변경 후에는 동일 조건으로 재측정해 회귀를 막는다

트러블슈팅 루틴(순서 고정)

장애 때는 선택지가 너무 많아서 흔히 길을 잃습니다. 아래 순서를 팀 표준으로 정해두면, 원인 추적이 빨라지고 '감으로 설정만 만지는' 일을 줄일 수 있습니다.

1) AWR/ASH로 병목 타입(wait event)을 분리한다
2) top SQL의 실행 계획/통계를 확인한다
3) 통계/인덱스/조인 전략을 우선 조정한다
4) 장시간 쿼리면 Undo 사용량/retention/공간을 점검한다
5) redo/log 스위치/IO 병목을 확인한다
6) 변경 후 AWR로 효과를 재확인한다

문제 상황(정확히 1개)

상황 -> 배치 리포트가 가끔 ORA-01555(snapshot too old)로 실패하거나 같은 시간대에 매우 느려진다.
원인 -> 장시간 읽기 쿼리가 필요한 Undo를 유지해야 하는데, 쓰기 트래픽으로 Undo가 빨리 재사용돼 필요한 버전이 사라졌다.
해결 -> Undo tablespace/retention을 재평가하고, 리포트 쿼리 범위를 줄이거나 배치 시간을 조정하며, 쓰기 폭주 구간을 피하도록 운영을 조정한다.
예방 팁 -> 리포팅 워크로드의 최대 실행 시간을 기준으로 Undo 정책을 잡고, 동시간대 쓰기 부하와의 상관을 지표로 운영한다.

참고/출처

정확한 동작/버전별 차이는 공식 문서를 기준으로 확인합니다. 실무에서는 버전 차이가 곧 성능/장애 차이입니다.

• Oracle Database Documentation
• DBMS_XPLAN