오라클 튜닝 기초 06강 — 옵티마이저와 통계정보(CBO)

🎯 학습 목표

비용기반 옵티마이저(CBO)가 계획을 고르는 원리를 이해한다.
통계정보가 부정확할 때 생기는 문제를 안다.
DBMS_STATS로 통계를 수집·확인한다.

📖 옵티마이저는 “통계로 계획을 고르는 설계자”

오라클의 비용기반 옵티마이저(CBO, Cost-Based Optimizer)는 하나의 SQL을 실행하는 여러 방법(인덱스 vs 전체 스캔, NL vs Hash, 조인 순서…)을 두고, 각각의 예상 비용을 계산해 가장 싼 계획을 고릅니다. 이 비용 계산의 재료가 바로 통계정보(Statistics)입니다.

📖 통계정보란 무엇인가

테이블 통계 : 행 수, 블록 수, 평균 행 길이
컬럼 통계   : 서로 다른 값의 개수(NDV), NULL 비율, 최소/최대값,
             값의 분포(히스토그램)
인덱스 통계 : 인덱스 높이, 리프 블록 수, 클러스터링 팩터

→ 옵티마이저는 이 숫자로 "이 조건이면 몇 행이 나올까(카디널리티)"를 추정하고,
  그 추정에 따라 인덱스/조인/순서를 결정한다.

📖 카디널리티 추정이 핵심

카디널리티(Cardinality) = 어떤 단계에서 나올 것으로 예상되는 행 수입니다. 옵티마이저의 모든 판단이 여기서 출발합니다.

예: WHERE deptno = 10
  - 컬럼 통계상 deptno의 서로 다른 값이 5개, 전체 1만 행이라면
  - 균등 분포 가정 → 10인 행 ≈ 1만 / 5 = 2000행으로 추정
  - "2000행이면 인덱스로 2000번 테이블 들락거리느니 Full Scan이 낫겠다" 판단

문제: 실제로는 deptno=10이 10건뿐인데 통계가 옛날 거라 2000건으로 추정하면?
  → 인덱스를 써야 할 상황에 Full Scan을 골라 느려진다 (또는 그 반대)

📖 통계가 틀리면 생기는 일

실행계획의 E-Rows(예상)와 A-Rows(실제)가 크게 어긋남 (2강의 DISPLAY_CURSOR로 확인).
인덱스를 타야 하는데 Full Scan, 또는 그 반대.
조인 순서/방식이 어긋나 대량×대량을 NL로 푸는 등 비효율 발생.

즉, “어제까지 빠르던 SQL이 갑자기 느려졌다”의 흔한 범인이 통계입니다. 데이터는 늘었는데 통계는 옛날 그대로면 옵티마이저가 헛다리를 짚습니다.

💻 통계 수집하기 — DBMS_STATS

-- 특정 테이블 통계 수집 (가장 자주 씀)
EXEC DBMS_STATS.GATHER_TABLE_STATS(
       ownname    => 'HR',
       tabname    => 'EMP',
       cascade    => TRUE,                    -- 인덱스 통계도 함께
       estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);  -- 표본 크기 자동

-- 스키마 전체
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'HR');

참고로 최신 오라클은 야간 자동 통계 수집(Auto Optimizer Stats) 작업이 기본 활성화돼 있습니다. 하지만 대량 적재 직후처럼 즉시 최신 통계가 필요하면 위 명령으로 수동 수집합니다.

💻 통계 확인하기

-- 테이블이 마지막으로 통계 수집된 시점/행수
SELECT table_name, num_rows, last_analyzed
  FROM user_tables WHERE table_name = 'EMP';

-- 컬럼별 서로 다른 값 수(NDV), NULL 수
SELECT column_name, num_distinct, num_nulls, last_analyzed
  FROM user_tab_col_statistics WHERE table_name = 'EMP';

📖 히스토그램 — 값이 한쪽으로 쏠릴 때

균등 분포 가정이 깨지는 경우(예: status 컬럼 값이 99%가 ‘DONE’, 1%가 ‘ERROR’)에는 히스토그램이 값별 실제 분포를 알려줘, “ERROR는 희소하니 인덱스가 유리”처럼 더 정확한 판단을 돕습니다. 보통 DBMS_STATS가 필요 시 자동 생성합니다.

⚠️ 주의사항

대량 INSERT/DELETE 후에는 통계를 갱신하세요. 안 하면 옵티마이저가 옛 분포로 판단합니다.
통계를 너무 작은 표본으로 수집하면 부정확해집니다. 특별한 이유가 없으면 AUTO_SAMPLE_SIZE를 쓰세요.
운영 중 함부로 통계를 지우거나(DELETE_TABLE_STATS) 잠그면(LOCK_TABLE_STATS) 계획이 급변할 수 있습니다. 변경 전 영향 검토 필수.

💡 팁

“갑자기 느려진 SQL”을 만나면, 먼저 last_analyzed와 num_rows를 실제 건수와 비교하세요. 통계가 낡았으면 그것부터 갱신하고 재측정합니다.
힌트로 계획을 억지로 고정하기 전에, 통계를 바로잡아 옵티마이저가 스스로 옳은 계획을 고르게 하는 것이 더 건강한 튜닝입니다.