[ORACLE] USER_SUBPART_HISTOGRAMS 뷰로 히스토그램 기반 서브파티션 최적화하기

[ORACLE] USER_SUBPART_HISTOGRAMS

1. USER_SUBPART_HISTOGRAMS란?

USER_SUBPART_HISTOGRAMS는 Oracle DB에서 사용자 소유 서브파티션 컬럼에 대한 히스토그램 통계 정보를 저장하는 데이터 딕셔너리 뷰입니다. 이 정보는 SQL 옵티마이저가 데이터 분포 특성을 기반으로 정확한 실행 계획을 수립할 수 있도록 지원합니다.

2. 히스토그램의 중요성

히스토그램은 컬럼 값의 분포 상태를 세분화하여 저장함으로써, 옵티마이저가 일반 통계만으로는 판단할 수 없는 편향된 데이터 분포를 고려할 수 있게 해줍니다.

• 빈도 기반 히스토그램 (Frequency)
• 높이 균등 히스토그램 (Height-balanced)
• Top-N 히스토그램 (12c 이상)
• Hybrid 히스토그램 (18c 이상)

3. 주요 컬럼 설명

컬럼명	설명
TABLE_NAME	통계가 적용된 테이블 이름
SUBPARTITION_NAME	서브파티션 이름
COLUMN_NAME	통계 대상 컬럼 이름
ENDPOINT_NUMBER	히스토그램 구간 번호
ENDPOINT_VALUE	각 구간의 마지막 값
ENDPOINT_ACTUAL_VALUE	실제 문자열/날짜 값 (숫자 외 컬럼에 사용)

4. 실전 활용 예제

SELECT SUBPARTITION_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE
FROM USER_SUBPART_HISTOGRAMS
WHERE TABLE_NAME = 'SALES_DATA'
AND COLUMN_NAME = 'REGION_CODE'
ORDER BY SUBPARTITION_NAME, ENDPOINT_NUMBER;
      

이 쿼리는 각 서브파티션의 REGION_CODE 컬럼에 설정된 히스토그램 구간과 값을 조회하는 데 유용합니다.

5. 관련 뷰 비교

뷰 이름	설명	범위
USER_HISTOGRAMS	전체 테이블 컬럼의 히스토그램	테이블 전체
USER_PART_HISTOGRAMS	파티션 단위 히스토그램	파티션
USER_SUBPART_HISTOGRAMS	서브파티션 단위 히스토그램	서브파티션

6. 고급 활용 전략

• 옵티마이저 강제 유도: 히스토그램 설정을 통해 옵티마이저가 특정 인덱스를 우선 고려하게 유도 가능
• 서브파티션 튜닝: 각 파티션 별로 데이터 분포가 상이할 때, 파티션 단위 히스토그램 설정으로 병목 구간 최적화
• DBMS_STATS 자동 수집 시 히스토그램 포함 여부 제어: METHOD_OPT 파라미터 설정

7. 결론

USER_SUBPART_HISTOGRAMS 뷰는 데이터가 파티션 및 서브파티션 기반으로 설계된 시스템에서 정교한 SQL 튜닝을 가능하게 하는 강력한 도구입니다. 수많은 데이터가 비대칭적으로 분포되어 있을 경우, 히스토그램 기반 통계를 통해 쿼리의 정확한 실행 계획 수립이 가능해집니다.

참고 자료

• Oracle® Database Performance Tuning Guide 21c
• Oracle® Database SQL Tuning Guide
• Oracle Help Center: https://docs.oracle.com/en/