[ORACLE] USER_PART_HISTOGRAMS : 파티션 히스토그램 분석과 실전 활용 전략

[ORACLE] USER_PART_HISTOGRAMS

Oracle 데이터베이스는 정교한 쿼리 옵티마이저를 통해 실행 계획을 수립하고, 이 과정에서 통계 정보는 핵심적인 역할을 합니다. 특히 대용량 테이블이 파티션되어 있는 경우, 파티션별 컬럼 분포를 분석하는 히스토그램 정보는 매우 중요한 요소가 됩니다. 본 글에서는 Oracle의 USER_PART_HISTOGRAMS 뷰를 활용하여 파티션 단위의 히스토그램 데이터를 어떻게 해석하고 실무에서 성능 최적화에 활용할 수 있는지를 상세히 소개합니다.

1. USER_PART_HISTOGRAMS란 무엇인가?

USER_PART_HISTOGRAMS는 사용자 소유의 파티션 테이블에 대해 수집된 히스토그램 통계 정보를 제공하는 Oracle 데이터 딕셔너리 뷰입니다. 각 파티션 별로 히스토그램 정보를 구분하여 보여주기 때문에, 특정 파티션에 국한된 쿼리 성능 문제를 정확히 진단할 수 있습니다.

-- 히스토그램 정보 조회 예시
SELECT table_name, partition_name, column_name, endpoint_number, endpoint_value
FROM user_part_histograms
WHERE table_name = 'SALES_PART';
  

이 뷰는 Oracle 옵티마이저가 컬럼 값의 분포(빈도나 범위 기반)를 이해할 수 있게 하며, 파티션 테이블의 통계 기반 실행 계획을 정교하게 제어할 수 있도록 돕습니다.

2. 주요 컬럼 설명

컬럼명	설명
TABLE_NAME	대상 테이블 이름
PARTITION_NAME	파티션 이름
COLUMN_NAME	히스토그램이 적용된 컬럼 이름
ENDPOINT_NUMBER	히스토그램 구간 번호
ENDPOINT_VALUE	히스토그램의 기준값

3. 히스토그램 유형

Oracle은 히스토그램 유형을 크게 Frequency, Height Balanced, Top-Frequency, Hybrid로 구분합니다. 각 유형은 컬럼의 데이터 분포를 어떻게 표현할지에 따라 옵티마이저가 선택합니다.

유형	설명	주요 활용 상황
Frequency	동일 값이 많이 존재하는 경우, 각 값의 빈도 수 기록	불균형 분포 (예: status='ACTIVE' 95%)
Height Balanced	값의 범위를 고르게 나누어 분포 파악	숫자형/날짜형 등 범위 기반 검색 컬럼
Top-Frequency	자주 등장하는 Top N 값만 기록	값이 많고 집중 분포된 컬럼
Hybrid	Frequency와 Height Balanced 혼합	다양한 유형이 혼재한 분포

4. 실무 활용 전략

• 파티션 단위의 쿼리 성능 이슈 진단: 특정 파티션에서 성능이 저하될 경우 해당 파티션의 히스토그램을 확인해 편중 여부 분석
• 파티션별 히스토그램 수집 설정: DBMS_STATS의 method_opt 옵션 사용
• 히스토그램 삭제: 불필요한 히스토그램은 옵티마이저 판단을 흐릴 수 있어, 삭제 또는 수집 방식 변경 필요
• Adaptive Plans 확인: 히스토그램이 실행 계획에 미치는 영향 확인 및 실행 계획 고정화 전략 수립

5. 수집 및 관리 예제

-- 특정 파티션만 통계 수집
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'MYSCHEMA',
    tabname => 'SALES_PART',
    partname => 'P202407',
    method_opt => 'FOR COLUMNS SIZE 254 PRODUCT_ID'
  );
END;
  

위 예제는 2024년 7월에 해당하는 파티션(P202407)에 대해 PRODUCT_ID 컬럼에 대한 최대 254 버킷 크기의 히스토그램을 생성하는 명령입니다.

6. USER_HISTOGRAMS vs USER_PART_HISTOGRAMS

두 뷰는 모두 히스토그램 정보를 제공하지만, 사용 범위에 차이가 있습니다.

구분	USER_HISTOGRAMS	USER_PART_HISTOGRAMS
적용 대상	비파티션 또는 전체 테이블 컬럼	파티션별 컬럼
세분화	전체 기준	파티션 기준
활용도	일반적인 통계 분석	파티션 튜닝 및 상세 옵티마이징

7. 결론

Oracle의 USER_PART_HISTOGRAMS 뷰는 단순한 데이터 통계를 넘어, 옵티마이저의 결정 과정에 직결되는 핵심 정보를 제공합니다. 특히 대용량 파티션 테이블에서 컬럼 값 분포가 파티션마다 다를 경우, 이 정보를 정밀하게 분석하고 튜닝하는 것이 전체 시스템 성능에 큰 영향을 미칠 수 있습니다. 실무에서는 파티션별 히스토그램을 주기적으로 수집하고 검토함으로써, 쿼리 성능 이슈를 선제적으로 대응할 수 있습니다.

---

출처:

• Oracle Database 21c PL/SQL Packages and Types Reference
• Oracle Optimizer Statistics Concepts and Usage Guide
• DBMS_STATS 공식 문서: https://docs.oracle.com/en/database/
• 실제 성능 튜닝 사례 및 경험 기반 분석