[ORACLE] ALL_HISTOGRAMS 완전 정복 : 옵티마이저를 움직이는 히스토그램 이해와 실전 튜닝

[ORACLE] ALL_HISTOGRAMS

ALL_HISTOGRAMS는 컬럼 값의 분포를 버킷(bucket) 단위로 저장해 옵티마이저(Optimizer)의 선택도(Selectivity) 추정에 직접적인 근거를 제공하는 핵심 딕셔너리 뷰입니다. 스큐(Skew) 여부, 상·하위 빈도값, 경계값 변화폭을 정밀하게 파악해 조인 순서, 접근 경로, 인덱스 사용 여부 등 실행계획의 품질을 가르는 지점에서 결정적인 인사이트를 줍니다.

왜 ALL_HISTOGRAMS를 봐야 하는가?

• 실제 분포 파악: 평균치 기반 카디널리티 착시를 방지(특히 편향 데이터).
• 계획 재현·비교: 통계 스냅샷 간 버킷 경계 변화로 계획 변화 원인 추적.
• 힌트 최소화: 통계만 바로잡아도 힌트 없는 안정적 실행계획 유도.

핵심 개념 요약

1. 버킷(bucket): 값 구간을 뜻합니다. 각 버킷의 끝점(Endpoint)이 ENDPOINT_NUMBER로 누적 카운트를 표현합니다.
2. 엔드포인트 값: ENDPOINT_VALUE는 내부 표현, 버전에 따라 ENDPOINT_ACTUAL_VALUE가 실제 값으로 제공됩니다.
3. 반복 카운트: ENDPOINT_REPEAT_COUNT는 해당 엔드포인트 값의 반복 발생 수(특히 상위 빈도값 식별)에 유용합니다.
4. 히스토그램 유형: FREQUENCY, TOP-FREQUENCY, HYBRID, NONE를 주로 사용합니다(유형은 *_TAB_COL_STATISTICS.HISTOGRAM 컬럼에서 확인).

ALL_HISTOGRAMS 주요 컬럼 빠르게 이해하기

• OWNER, TABLE_NAME, COLUMN_NAME
• ENDPOINT_NUMBER – 누적 행수의 경계(이전 엔드포인트와의 차가 버킷 빈도)
• ENDPOINT_VALUE – 내부 표현의 값(숫자/RAW 기반)
• ENDPOINT_ACTUAL_VALUE – 실제 표현값(버전별 제공)
• ENDPOINT_REPEAT_COUNT – 해당 엔드포인트 값의 반복 수

참고: 실제 제공 컬럼은 데이터베이스 버전에 따라 차이가 있을 수 있습니다. 운영 환경의 딕셔너리 정의를 확인하세요.

히스토그램 유형 비교(요약)

유형	특징	장점	주의점	권장 사례
FREQUENCY	각 서로 다른 값별 버킷(카디널리티가 버킷 수 이내)	상위/하위 빈도값을 정확히 반영	값 종류가 많으면 비효율	값 종류가 제한적인 코드성 컬럼
TOP-FREQUENCY	상위 빈도값만 빈도 기반, 나머지는 균등 처리	상위 N개 편향값을 잘 반영	꼬리 분포 정밀도는 낮음	상위 몇 개 값이 압도적인 편향
HYBRID	연속구간+빈도 정보를 혼합(12c+)	연속값 분포와 편향을 동시에 포착	해석이 상대적으로 복잡	범위 조건이 많은 날짜/수치형
NONE	히스토그램 미생성(균등 가정)	관리 단순, 비용 낮음	강한 스큐에서 오판 가능	균질 분포 컬럼, 낮은 민감도

현장에서 바로 쓰는 분석 쿼리

1) 특정 컬럼의 버킷 분포 재구성

-- 버킷별 행수(누적 경계 차) 계산
WITH h AS (
  SELECT
    owner, table_name, column_name,
    endpoint_number,
    endpoint_number - LAG(endpoint_number,1,0)
      OVER (ORDER BY endpoint_number) AS bucket_rows,
    endpoint_value,
    /* 실제 값 제공 시 우선 사용 */
    COALESCE(TO_CHAR(endpoint_actual_value), TO_CHAR(endpoint_value)) AS endpoint_val_disp
  FROM all_histograms
  WHERE owner = :owner
    AND table_name = :table
    AND column_name = :column
)
SELECT endpoint_number, bucket_rows, endpoint_val_disp
FROM h
ORDER BY endpoint_number;

2) 상위 빈도값(Top N) 추출

-- endpoint_repeat_count가 큰 값은 상위 빈도 신호
SELECT
  COALESCE(TO_CHAR(endpoint_actual_value), TO_CHAR(endpoint_value)) AS value_key,
  SUM(endpoint_repeat_count) AS repeat_cnt,
  MAX(endpoint_number) AS last_endpoint
FROM all_histograms
WHERE owner = :owner AND table_name = :table AND column_name = :column
GROUP BY COALESCE(TO_CHAR(endpoint_actual_value), TO_CHAR(endpoint_value))
ORDER BY repeat_cnt DESC
FETCH FIRST 10 ROWS ONLY;

3) 히스토그램 유형·버킷 수와 함께 보기

SELECT s.owner, s.table_name, s.column_name,
       s.histogram, s.num_buckets, s.last_analyzed
FROM   all_tab_col_statistics s
WHERE  s.owner = :owner AND s.table_name = :table AND s.column_name = :column;

4) 조건값의 선택도 예측 감

-- 특정 상수값 :val에 대해 가까운 엔드포인트를 이용해 대략적 선택도 감 잡기
WITH h AS (
  SELECT endpoint_number, endpoint_value,
         LAG(endpoint_number,1,0) OVER (ORDER BY endpoint_number) AS prev_epn
  FROM all_histograms
  WHERE owner = :owner AND table_name = :table AND column_name = :column
),
pos AS (
  SELECT MIN(endpoint_number) AS epn
  FROM h
  WHERE endpoint_value >= :val
)
SELECT (epn - NVL((SELECT prev_epn FROM h WHERE endpoint_number = epn),0))
       / (SELECT MAX(endpoint_number) FROM h) AS approx_selectivity
FROM pos;

정밀 추정은 옵티마이저 내부 로직과 버전/유형별 해석이 필요합니다. 위 식은 분포 감지용 개략치입니다.

운영에서 통하는 실전 운용 팁

• 분포가 바뀌면 통계도 바뀌어야 합니다. DBMS_STATS의 METHOD_OPT로 컬럼별 히스토그램을 전략적으로 생성하세요.

  BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
      ownname => :owner,
      tabname => :table,
      method_opt => 'FOR COLUMNS SIZE AUTO col1, col2 SIZE 254',
      cascade  => TRUE
    );
  END;
  /
  

• SIZE AUTO에만 의존하지 말기: 고스큐 컬럼은 SIZE <N>로 명시해 계획 변동성을 줄입니다.
• 불필요한 히스토그램 제거: 균질 분포 컬럼의 히스토그램은 오히려 노이즈일 수 있습니다(SIZE 1 유도 또는 NONE 전환).
• 계획 회귀 점검 루틴화: 배포 전후 ALL_HISTOGRAMS 스냅샷 비교로 엔드포인트 급변을 감시하세요.
• 상수 값 바인딩 정책: 상위 빈도 상수는 바인드 변수 스니핑 영향이 큼. CURSOR_SHARING, SQL Plan Baseline 등과 함께 검토.

자주 겪는 문제 & 해결 가이드(요약)

증상	가능 원인	점검 포인트	해결 방향
인덱스 미사용, Full Scan 증가	스큐 무시(히스토그램 NONE)	히스토그램 유형/버킷, 상위 빈도값 존재	FREQUENCY/TOP-FREQUENCY 생성, 버킷 증대
배포 후 계획 급변	통계 갱신으로 엔드포인트 급변	이전/이후 ENDPOINT_NUMBER 분포 비교	버킷 수 안정화, 컬럼별 SIZE 고정
바인드 값에 따라 일관성 저하	바인드 스니핑 + 편향 데이터	상위 빈도 상수 여부	SPM 도입, SQL 프로파일, OPTIMIZER_FEATURES_ENABLE 검토
문자형 컬럼 값 해석 어려움	내부 표현값 노출	ENDPOINT_ACTUAL_VALUE 지원 여부	지원 시 해당 컬럼 사용, 미지원이면 변환 함수 활용

분석 워크플로우(권장)

1. 현 실행계획 확인 – 카디널리티 추정이 실제와 어긋나는 지점 파악
2. *_TAB_COL_STATISTICS 확인 – HISTOGRAM 유형·버킷·최종 분석시점
3. ALL_HISTOGRAMS 드릴다운 – 버킷별 행수, 상위 빈도 탐지
4. 통계 재수집/정교화 – METHOD_OPT 조정, 필요 시 히스토그램 제거
5. 회귀 테스트 – 주요 SQL에 대한 전후 카디널리티/실행시간 비교

FAQ

Q1. HYBRID와 TOP-FREQUENCY 중 무엇을 쓸까?
A. 상위 몇 개 값이 압도적으로 많은 편향이면 TOP-FREQUENCY, 연속형 수치/날짜 컬럼의 범위 조건이 잦다면 HYBRID가 유리합니다.

Q2. 버킷 수는 얼마나?
A. 기본은 자동(SIZE AUTO)로 시작하되, 변동성이 큰 핵심 컬럼은 75~254 사이에서 실측 벤치마크로 고정하는 방식을 권합니다.

출처

• Oracle Database SQL Language Reference – Data Dictionary Views (ALL_HISTOGRAMS, *_TAB_COL_STATISTICS)
• Oracle Database Performance Tuning Guide – Optimizer Statistics Concepts
• DBMS_STATS 패키지 문서 – METHOD_OPT, SIZE, 히스토그램 생성 정책
• Oracle Optimizer 팀 기술 문서 및 블로그(히스토그램 유형: FREQUENCY, TOP-FREQUENCY, HYBRID 개념)

문서 세부 내용은 사용하는 Oracle 버전에 따라 상이할 수 있습니다. 운영 버전의 공식 매뉴얼을 우선 확인하세요.