[ORACLE] USER_HISTOGRAMS 완벽 분석 : 옵티마이저의 판단을 지배하는 통계 구조

[ORACLE] USER_HISTOGRAMS

1. USER_HISTOGRAMS란 무엇인가?

USER_HISTOGRAMS는 Oracle 데이터베이스에서 사용자가 소유한 테이블의 컬럼에 대한 히스토그램 정보를 제공하는 데이터 딕셔너리 뷰입니다. 이 뷰는 Oracle 옵티마이저가 SQL 실행 계획을 최적화할 때 컬럼 값의 분포도를 파악하는 데 핵심적으로 사용됩니다. 히스토그램은 단순한 평균값이나 최소/최대 값만으로는 파악할 수 없는 데이터의 실제 분포를 반영하여, 특정 조건에 대한 필터링 효과(Cardinality Estimation)를 정밀하게 계산하는 데 결정적인 역할을 합니다.

2. 히스토그램이 필요한 이유

예를 들어, GENDER 컬럼에 'M'과 'F'만 존재한다고 해도, 전체 90%가 'M'이라면 이 분포를 인지한 옵티마이저는 더 효율적인 인덱스 스캔 전략을 선택할 수 있습니다. 이러한 분석 정보를 저장하는 것이 바로 USER_HISTOGRAMS 뷰입니다.

비교 항목	히스토그램 없음	히스토그램 존재
데이터 분포 인식	평균 기반	실제 분포 기반
옵티마이저 판단 정확도	낮음	높음
인덱스 활용률	낮을 수 있음	적절하게 사용
복잡 쿼리 성능	비효율 가능	정교한 실행 계획

3. USER_HISTOGRAMS 주요 컬럼 설명

USER_HISTOGRAMS 뷰는 다음과 같은 중요한 컬럼을 포함하고 있습니다:

컬럼명	설명
TABLE_NAME	히스토그램이 적용된 테이블명
COLUMN_NAME	히스토그램이 적용된 컬럼명
ENDPOINT_NUMBER	히스토그램 버킷 번호 (범위 구간)
ENDPOINT_VALUE	해당 구간의 값 (숫자 또는 문자열)
ENDPOINT_ACTUAL_VALUE	실제 표현된 값 (CHAR/NVARCHAR 등)
ENDPOINT_REPEAT_COUNT	해당 값이 얼마나 반복되었는지 (FREQ 히스토그램에서)

4. 히스토그램 유형

Oracle은 컬럼 데이터 분포에 따라 다양한 유형의 히스토그램을 생성합니다. 대표적으로는 다음과 같은 3가지가 있습니다:

유형	설명	적용 조건
Height-Balanced	모든 버킷에 동일한 수의 로우가 들어가는 방식	값 분포가 불균형한 경우
Frequency	각 값의 등장 빈도를 정확히 기록	고정된 소수 값의 반복
Top-Frequency	가장 자주 나오는 값들만 별도 기록	많은 고유값 중 상위 N개

5. 히스토그램 생성 방법

히스토그램은 DBMS_STATS 패키지를 사용해 수동 생성할 수 있으며, Oracle은 기본적으로 자동 통계 수집 시 필요한 컬럼에만 생성합니다.

-- FREQ 히스토그램 생성 예시
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => 'HR',
    tabname          => 'EMPLOYEES',
    method_opt       => 'FOR COLUMNS SIZE 254 JOB_ID'
  );
END;

SIZE 254: 최대 254개의 버킷으로 히스토그램 생성 (Oracle 한계)

6. 실무 활용 전략

• WHERE 절 조건에 자주 등장하는 컬럼만 히스토그램 생성
• 사용자 정의 통계 정책에 히스토그램 대상 컬럼 명시
• 쿼리 성능 저하 시 실행계획(EXPLAIN PLAN)과 함께 히스토그램 확인
• 히스토그램 과다 생성 시 옵티마이저 판단 과적응(overfitting) 주의

7. 히스토그램 조회 쿼리 예시

-- 특정 테이블의 히스토그램 분포 확인
SELECT column_name, endpoint_value, endpoint_repeat_count
FROM user_histograms
WHERE table_name = 'EMPLOYEES'
  AND column_name = 'JOB_ID'
ORDER BY endpoint_number;

8. 히스토그램 분석 도구와 진단 팁

• AWR 리포트 분석 시 히스토그램 적용 여부 확인
• SQL MONITOR에서 실제 필터링 비율이 기대값과 다를 경우 재분석
• DBMS_STATS.REPORT_COL_USAGE 사용하여 컬럼 활용 빈도 파악

-- 컬럼 사용 내역 확인
SELECT * FROM TABLE(DBMS_STATS.REPORT_COL_USAGE('HR', 'EMPLOYEES'));

출처

• Oracle Database SQL Language Reference 21c – Oracle Corporation
• Oracle Optimizer Blog – Maria Colgan, Oracle Corp.
• Expert Oracle SQL Tuning – Tony Hasler