Histogram 이해: 데이터 편중·인기 값·Bind 실행계획

핵심 요약

기본 Column Statistics의 NUM_DISTINCT(NDV)는 값의 종류 수를 알려 주지만 각 값이 몇 Row를 차지하는지는 충분히 설명하지 못합니다.

ORDERS NUM_ROWS = 1,000,000
STATUS NDV      = 4

Histogram 없음
  → 단순 균등 가정에서 값당 약 250,000 Row

실제 분포
  COMPLETE   900,000
  READY       80,000
  ERROR       15,000
  CANCELLED    5,000

Histogram은 Column의 값 분포를 Bucket과 Endpoint로 요약해 인기값·비인기값의 Selectivity와 Cardinality를 보완하는 특수 Column Statistics입니다.

Histogram의 목적
  → 값별 결과 규모 차이를 Optimizer에 전달

Histogram의 목적이 아닌 것
  → Index 사용 강제
  → 특정 Plan 영구 고정
  → Runtime 성능 자동 보장

같은 SQL 구조라도 실제 값에 따라 최적 경로가 달라질 수 있습니다.

status='COMPLETE'
  → Table 대부분
  → Full Scan·Hash 처리 후보

status='CANCELLED'
  → 극소수 Row
  → Index Range Scan·Nested Loops 후보

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → 옵티마이저와 통계정보 → Histogram·Data Skew·Bind Plan 범위에서 Histogram 유형, 수집 정책, Literal·Bind 실행계획과 검증 방법을 다룹니다.

학습 목표

이 이론을 학습한 뒤에는 다음을 설명할 수 있어야 합니다.

• Histogram이 필요한 Data Skew와 불필요한 Column을 구분한다.
• Bucket·Endpoint·Popular·Nonpopular Value를 설명한다.
• Frequency·Top-Frequency·Hybrid·Height-Balanced Histogram의 생성 조건을 구분한다.
• Histogram 유형별 ENDPOINT_NUMBER와 ENDPOINT_REPEAT_COUNT를 해석한다.
• SIZE AUTO, SIZE n, SIZE 1의 목적과 한계를 설명한다.
• Column Usage가 SIZE AUTO 결과에 영향을 주는 이유를 설명한다.
• Literal과 Bind Predicate에서 Histogram의 사용 차이를 설명한다.
• Bind Peeking·Bind-Sensitive·Bind-Aware Cursor를 설명한다.
• Histogram 생성·제거·재수집 후 Plan 변동 위험을 설명한다.
• 값별 E-Rows·A-Rows·Buffers·Elapsed로 Histogram 효과를 검증한다.

1. Histogram이 필요한 이유

다음 분포를 가정합니다.

Histogram이 없고 NDV가 4라면 가장 단순한 균등 추정은 다음과 같습니다.

Selectivity ≈ 1 / 4 = 25%
Cardinality ≈ 1,000,000 / 4 = 250,000

Cardinality 오류는 다음으로 전파됩니다.

값별 Cardinality 오류
→ Index·Full Scan Cost 오류
→ Join Order·Join Method 오류
→ Sort·Hash Memory·Parallel 판단 오류

2. Histogram이 유용한 Column

다음 조건을 함께 만족할수록 Histogram의 가치가 큽니다.

• 실제 SQL의 Filter·Join Predicate에 자주 사용
• 값 분포가 심하게 편중
• 값별 예상 Row 수 차이가 큼
• 값에 따라 최적 Access Path·Join Plan이 달라짐
• 수집 시점 분포가 실제 운영 기간을 대표함

Histogram이 불필요하거나 효과가 작은 사례입니다.

• 거의 균등 분포
• Predicate에 사용되지 않는 Column
• 작은 Table이라 Plan 차이가 미미
• 어떤 값이든 Table 대부분을 읽음
• PK·UK 단건 조회처럼 값별 Plan이 사실상 동일
• Query가 Column을 사용해도 결과 규모 차이가 Plan을 바꾸지 않음

NDV가 작다
  ≠ Histogram이 반드시 필요

NDV가 크다
  ≠ Histogram이 불필요

NDV가 커도 소수 인기값이 대부분의 Row를 차지하면 Histogram 후보가 될 수 있습니다.

3. Bucket과 Endpoint

Histogram은 정렬된 값 분포를 제한된 Bucket으로 요약합니다.

Oracle 공식 해석의 핵심입니다.

Frequency·Hybrid
  ENDPOINT_NUMBER
  → 현재·이전 Bucket까지의 누적 빈도

Height-Balanced
  ENDPOINT_NUMBER
  → 0 또는 1부터 시작하는 순차 Bucket 번호

따라서 Histogram 유형을 확인하지 않고 ENDPOINT_NUMBER만 보고 원본 빈도를 계산하면 안 됩니다.

4. Frequency Histogram

생성 조건의 핵심입니다.

NDV <= 요청 Bucket 수 n

각 Distinct Value에 전용 Bucket을 할당할 수 있으므로 값별 빈도를 상세히 표현합니다.

STATUS NDV = 4
SIZE 254 요청

→ Frequency Histogram 후보
→ 4개 값의 빈도를 각각 저장 가능

Frequency Histogram의 ENDPOINT_NUMBER는 누적 빈도입니다.

ENDPOINT_NUMBER 차이
  → 해당 Endpoint Value의 빈도

예를 들어 Endpoint Number가 5,000 → 20,000으로 증가했다면 두 Endpoint 사이의 빈도 차이는 15,000입니다.

5. Top-Frequency Histogram

생성 조건의 핵심입니다.

NDV > Bucket 수 n
+ 상위 n개 값이 전체 Row의 내부 임계치 p 이상 차지

내부 임계치는 Oracle 문서에서 다음과 같이 설명합니다.

p = (1 - 1/n) × 100

기본 n=254라면 약 99.6%입니다.

Top-Frequency Histogram은 영향이 작은 비인기값 일부를 제외하고 상위 인기값의 Frequency를 상세히 표현합니다.

NDV는 매우 큼
상위 인기값이 거의 모든 Row 차지

→ Top-Frequency

각 포함된 Distinct Value는 자체 Bucket을 가지며 Endpoint Number는 누적 빈도입니다.

6. Hybrid Histogram

생성 조건의 핵심입니다.

NDV > n
+ Top-Frequency 조건 미충족
+ ESTIMATE_PERCENT = AUTO_SAMPLE_SIZE

Hybrid Histogram은 Height-Based Bucket 구조와 Frequency 정보를 결합합니다.

주요 특징입니다.

• 같은 Endpoint Value가 여러 Bucket 경계를 차지하지 않도록 Bucket을 통합
• Endpoint Value의 빈도를 ENDPOINT_REPEAT_COUNT로 저장
• 인기값·거의 인기값의 Equality 추정을 Height-Balanced보다 개선
• 모든 Distinct Value를 개별 Bucket으로 저장하는 것은 아님

ENDPOINT_REPEAT_COUNT가 큼
  → 해당 Endpoint Value 빈도가 높은 방향

7. Height-Balanced Histogram

Height-Balanced Histogram은 각 Bucket에 비슷한 Row 수가 들어가도록 값 범위를 나누는 Legacy 유형입니다.

NDV > n
+ ESTIMATE_PERCENT가 AUTO_SAMPLE_SIZE가 아님

→ Height-Balanced 후보

Oracle 12c 이후 기본 Auto Sampling으로 새 Histogram을 수집하면 Frequency·Top-Frequency·Hybrid가 중심이며, Height-Balanced는 다음 맥락에서 볼 수 있습니다.

• 구버전에서 생성된 Statistics가 남아 있음
• 비기본 ESTIMATE_PERCENT로 Statistics 수집
• Upgrade 후 기존 Histogram이 아직 재수집되지 않음

Height-Balanced에서는 Endpoint가 여러 Bucket에 반복되면 Popular Value로 해석할 수 있습니다.

8. Histogram 유형 선택 요약

여기서 n은 요청 Bucket 수이며 기본 최대값은 일반적으로 254입니다.

SIZE 254
  → 무조건 254개 Bucket 생성 X
  → 최대 254개 Bucket을 요청 O

실제 Bucket 수와 유형은 NDV·분포·Sample 방식에 따라 달라집니다.

9. Histogram 확인 방법

Column Statistics를 확인합니다.

SELECT table_name,
       column_name,
       num_distinct,
       density,
       histogram,
       num_buckets,
       sample_size,
       last_analyzed,
       global_stats,
       notes
FROM   user_tab_col_statistics
WHERE  table_name = 'ORDERS'
ORDER  BY column_name;

Endpoint를 확인합니다.

SELECT endpoint_number,
       endpoint_value,
       endpoint_actual_value,
       endpoint_repeat_count
FROM   user_histograms
WHERE  table_name  = 'ORDERS'
AND    column_name = 'STATUS'
ORDER  BY endpoint_number;

주의합니다.

• ENDPOINT_VALUE는 내부 Numeric 표현일 수 있음
• 문자·날짜는 ENDPOINT_ACTUAL_VALUE 제공 여부 확인
• 유형마다 Endpoint Number 의미가 다름
• 실제 원본 GROUP BY value, COUNT(*) 분포와 함께 검증
• Global·Partition Column Histogram의 범위를 구분

10. METHOD_OPT와 Bucket 제어

10.1 SIZE AUTO

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => USER,
    tabname          => 'ORDERS',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    cascade          => DBMS_STATS.AUTO_CASCADE
  );
END;
/

SIZE AUTO는 Data 분포와 Column Usage를 바탕으로 Histogram 생성 여부·유형·Bucket 수를 Oracle이 선택하게 합니다.

10.2 SIZE n

method_opt =>
  'FOR ALL COLUMNS SIZE 1 ' ||
  'FOR COLUMNS STATUS SIZE 254'

SIZE n
  → 최대 n개 Bucket 요청

10.3 SIZE 1

SIZE 1
  → Histogram 없이 기본 Column Statistics

Histogram 제거가 필요한 Column을 대상으로 사용할 수 있으나, 제거 전후 대표 SQL의 Plan·Runtime을 비교해야 합니다.

11. Column Usage와 SIZE AUTO

SIZE AUTO는 Data Skew만이 아니라 Query Workload에서 해당 Column이 Predicate·Group 등에 사용된 정보도 참고할 수 있습니다.

SELECT DBMS_STATS.REPORT_COL_USAGE(
         ownname => USER,
         tabname => 'ORDERS'
       )
FROM dual;

자동 Histogram이 기대와 다를 수 있는 경우입니다.

• Table 생성 직후 대표 SQL이 아직 실행되지 않음
• Column Usage 정보가 부족
• Histogram이 Plan 차이를 만들 필요가 없다고 판단
• Table·Schema Preference의 METHOD_OPT가 다름
• Statistics 수집 시점의 일시적 분포가 대표성을 잃음

따라서 SIZE AUTO를 사용해도 핵심 Column의 Histogram 유형과 SQL별 E-Rows를 확인합니다.

12. Literal Predicate와 Histogram

SELECT *
FROM orders
WHERE status = 'ERROR';

Literal 값은 Hard Parse 시 명확합니다.

Histogram 존재
+ Literal 값 식별
→ 해당 값의 Bucket·빈도 기반 Cardinality
→ 값별 Access Path 비교

희귀값 Literal과 인기값 Literal은 별도 SQL Text·Parent Cursor가 될 수 있어 각각 다른 Plan이 만들어질 수 있습니다.

장점:

• 값별 최적 Plan 가능성

비용:

• 값이 SQL Text에 계속 바뀌면 Hard Parse·Shared Pool 부하 증가
• Application에서는 일반적으로 Bind Variable 사용이 권장됨

13. Bind Predicate·Bind Peeking

SELECT *
FROM orders
WHERE status = :status;

Bind Variable은 Cursor Sharing을 높이지만 한 Plan이 모든 값에 최적이라는 보장은 없습니다.

Bind Peeking은 최초 Hard Parse에서 Bind 값을 보고 Literal처럼 Cardinality를 계산하는 기능입니다.

첫 Peek = CANCELLED
→ 소량 Index Plan 가능

후속 Bind = COMPLETE
→ 같은 Plan 재사용 시 대량 ROWID Access 가능

Optimizer는 매 실행마다 Bind 값을 Peek하는 것이 아니라 최초 Hard Parse에서 Peek합니다.

확인합니다.

• SQL_ID·Child Number
• Peeked Bind
• Plan Hash Value
• 값별 A-Rows·Buffers·Elapsed
• Histogram 유형·수집 시점

14. Adaptive Cursor Sharing

Histogram이 있는 Skew Column의 Bind Predicate는 값에 따라 최적 Plan이 달라질 수 있습니다.

Oracle은 Cursor를 다음 단계로 관리할 수 있습니다.

Bind-Sensitive
  → Bind 값별 실행 Row·Buffer 차이를 관찰

Bind-Aware
  → Selectivity 범위에 따라 여러 Child Cursor Plan 사용 가능

확인 View·Column입니다.

SELECT child_number,
       plan_hash_value,
       executions,
       buffer_gets,
       is_bind_sensitive,
       is_bind_aware,
       is_shareable
FROM   v$sql
WHERE  sql_id = :sql_id
ORDER  BY child_number;

추가 진단 View입니다.

• V$SQL_CS_SELECTIVITY
• V$SQL_CS_STATISTICS
• V$SQL_CS_HISTOGRAM

주의합니다.

• 값마다 Child Cursor를 무한히 하나씩 만드는 구조는 아님
• Cardinality 범위와 실행 패턴을 분류
• 적응 과정에서 추가 Hard Parse가 발생할 수 있음
• Histogram이 있다고 항상 Bind-Aware가 되는 것은 아님

15. Histogram이 문제를 만들 수 있는 상황

Histogram 자체가 잘못된 것은 아니지만 운영 조건과 맞지 않으면 Plan 변동이 커질 수 있습니다.

• 값 분포가 시간대·배치마다 빠르게 변경
• Sample 변화로 Histogram 유형·Endpoint가 변경
• 일시적인 배치 직후 분포를 정상 분포로 수집
• 모든 Column에 불필요한 Histogram 생성
• Bind Skew가 크지만 Cursor가 아직 적응하지 못함
• 새 값이 기존 HIGH_VALUE 밖으로 지속 증가
• Partition별 Histogram과 Global Histogram이 불일치
• Statistics 재수집 후 Cursor Invalidations와 Plan 변경

Histogram 정확도 향상
  + Plan 안정성 비용
  + Parse·Child Cursor 비용
  → 전체 Workload로 판단

16. 모든 Column에 SIZE 254를 적용하면 안 되는 이유

가능한 부작용입니다.

• Statistics 수집 시간 증가
• Data Dictionary 저장량 증가
• Histogram Endpoint·유형 변화에 따른 Plan 변동
• Cursor Invalidations·Hard Parse 증가 가능성
• Bind-Sensitive·Bind-Aware Cursor 수 증가 가능성
• 사용하지 않는 Column 통계 관리 비용
• 일시적 Data Skew를 과도하게 반영

Histogram 대상
  = SQL에 실제 사용
  + Data Skew 존재
  + 값별 Plan 차이가 성능에 중요

17. Runtime 검증

SELECT /*+ GATHER_PLAN_STATISTICS */
       order_id,
       customer_id,
       amount
FROM   orders
WHERE  status = :status;

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    :sql_id,
    :child_no,
    'ALLSTATS LAST +PREDICATE +PEEKED_BINDS +NOTE'
  )
);

값별로 기록합니다.

반복 Operation은 E-Rows×Starts와 총 A-Rows 또는 Per-Start 단위로 비교합니다.

18. 적용 판단 절차

1. Histogram 후보 Column이 실제 Predicate에 사용되는지 확인한다.
2. GROUP BY 값별 COUNT로 Skew와 시간대 변화를 확인한다.
3. 현재 NDV·Histogram·Bucket·Sample·LAST_ANALYZED를 확인한다.
4. Literal·Bind·Peeked Bind·Child Cursor를 구분한다.
5. 값별 E-Rows·A-Rows와 현재 Plan을 수집한다.
6. SIZE AUTO 또는 필요한 Column에만 SIZE n을 적용한다.
7. 희귀값·인기값·중간값의 Buffers·Reads·Elapsed를 비교한다.
8. 통계 재수집 후 Histogram 유형·Endpoint·Plan 변화를 확인한다.
9. 다른 SQL·Bind 범위·Partition의 Plan Regression을 확인한다.
10. Pending Statistics·Canary·Rollback 절차를 포함해 적용한다.

자주 혼동하는 판단