Oracle 19c 히스토그램: 치우친 값 분포에서 실행계획이 바뀌는 조건

Oracle 19c 히스토그램은 값이 치우친 컬럼을 조건으로 검색할 때, 값마다 예상 행 수가 크게 달라져 실행 경로도 달라져야 하는 경우에 유용해요. 분포가 균등하거나 그 컬럼이 조건절에 거의 쓰이지 않는다면 히스토그램을 모든 컬럼에 강제로 만드는 것보다 SIZE AUTO로 워크로드와 분포를 함께 판단하게 두는 편이 안전합니다.

히스토그램을 추가하거나 제거하면 기존 커서가 다시 최적화되면서 계획이 바뀔 수 있습니다. 운영에서는 먼저 DBA_TAB_COL_STATISTICS의 유형과 버킷 수, 실제 값 분포, 같은 SQL의 E-Rows와 A-Rows를 기록한 뒤 한 테이블의 한 컬럼부터 검증하세요.

히스토그램이 필요한 조건부터 좁힌다

히스토그램이 없으면 옵티마이저는 기본적으로 컬럼 값이 고르게 분포한다고 가정합니다. 예를 들어 주문 100만 건 중 DONE이 99만 건이고 HOLD가 1만 건이라면, 두 값의 실제 선택도는 크게 달라요. 같은 인덱스가 HOLD 조회에는 유리해도 DONE 조회에는 대량 테이블 접근을 더해 오히려 불리할 수 있습니다. Oracle 19c 히스토그램 문서도 비균등 분포의 필터와 조인 조건에서 더 정확한 카디널리티 추정이 목적이라고 설명합니다.

적용 조건·영향 범위

작은 테스트 테이블에서 치우침을 확인한다

아래 예제는 전용 테스트 스키마용입니다. 이 글을 작성하며 실제 Oracle 인스턴스에서 실행하지 않았으며, 표시한 결과는 Oracle 19c 문서에 따른 예상 동작과 확인 방법입니다. 운영 테이블명이나 데이터 분포를 그대로 흉내 내지 말고 별도 객체로 재현하세요.

CREATE TABLE histogram_demo_orders (
  order_id    NUMBER NOT NULL,
  status_code VARCHAR2(10) NOT NULL,
  created_at  DATE NOT NULL,
  amount      NUMBER(12,2) NOT NULL,
  CONSTRAINT histogram_demo_orders_pk PRIMARY KEY (order_id)
);

INSERT INTO histogram_demo_orders (order_id, status_code, created_at, amount)
SELECT LEVEL,
       CASE WHEN LEVEL <= 9900 THEN 'DONE' ELSE 'HOLD' END,
       DATE '2026-01-01' + MOD(LEVEL, 90),
       MOD(LEVEL * 137, 100000) / 100
FROM dual
CONNECT BY LEVEL <= 10000;

CREATE INDEX histogram_demo_orders_i1
    ON histogram_demo_orders (status_code);

COMMIT;

분포부터 SQL로 확인하면 히스토그램을 만들기 전에 “정말 치우쳤는가”를 숫자로 볼 수 있어요. 전체 행 수가 큰 운영 테이블에서는 GROUP BY 자체도 많은 I/O를 낼 수 있으므로 통계 뷰, 표본 조회, 업무 집계 결과를 함께 활용하세요.

SELECT status_code,
       COUNT(*) AS row_count,
       ROUND(100 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS pct
FROM histogram_demo_orders
GROUP BY status_code
ORDER BY status_code;

현재 히스토그램과 사용 흔적을 먼저 기록한다

히스토그램 수집 전에는 유형, 버킷 수, distinct 값 수, 마지막 수집 시각을 저장하세요. 자기 스키마는 USER_TAB_COL_STATISTICS, 다른 스키마까지 보는 DBA는 필요한 사전 조회 권한으로 DBA_TAB_COL_STATISTICS를 사용합니다. 테이블 통계의 stale 여부와 수집·복원 기준부터 정해야 한다면 Oracle 19c 통계정보를 안전하게 수집하고 복원하는 순서를 먼저 확인할 수 있습니다.

SELECT owner,
       table_name,
       column_name,
       num_distinct,
       density,
       histogram,
       num_buckets,
       last_analyzed
FROM dba_tab_col_statistics
WHERE owner = 'APP'
  AND table_name = 'ORDERS'
  AND column_name = 'STATUS_CODE';

SIZE AUTO는 데이터 분포만 보지 않습니다. 이전 SQL이 어떤 컬럼을 필터·조인·그룹 조건으로 사용했는지도 반영해 히스토그램 대상을 정할 수 있어요. Oracle은 이 사용 정보를 내부의 SYS.COL_USAGE$에 기록하며, 직접 조회 대신 DBMS_STATS.REPORT_COL_USAGE를 제공합니다.

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

한 컬럼만 수집하고 예상 행 수를 비교한다

운영 기본값은 FOR ALL COLUMNS SIZE AUTO지만 원인 분석 단계에서 모든 컬럼을 강제로 바꾸면 변화 범위가 너무 넓어집니다. 테스트에서는 상태 컬럼 한 개에 최대 254개 버킷을 허용해 분포가 어떻게 기록되는지 확인할 수 있어요. distinct 값이 버킷 수 이하인 이 예제에서는 frequency histogram이 예상됩니다.

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => USER,
    tabname          => 'HISTOGRAM_DEMO_ORDERS',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR COLUMNS SIZE 254 STATUS_CODE',
    cascade          => DBMS_STATS.AUTO_CASCADE,
    degree           => 1,
    no_invalidate    => DBMS_STATS.AUTO_INVALIDATE
  );
END;
/

DBMS_STATS 19c 문서에서 METHOD_OPT는 SIZE 정수·REPEAT·AUTO·SKEWONLY를 지원합니다. 일반 운영에서는 자동 작업과 기존 테이블 설정을 먼저 확인하세요. SIZE REPEAT는 이미 히스토그램이 있는 컬럼만 다시 수집하므로 새로 필요한 컬럼을 놓칠 수 있고, SKEWONLY는 워크로드보다 분포에 초점을 둡니다.

SELECT column_name, histogram, num_buckets, num_distinct
FROM user_tab_col_statistics
WHERE table_name = 'HISTOGRAM_DEMO_ORDERS'
  AND column_name = 'STATUS_CODE';

SELECT endpoint_value, endpoint_number
FROM user_histograms
WHERE table_name = 'HISTOGRAM_DEMO_ORDERS'
  AND column_name = 'STATUS_CODE'
ORDER BY endpoint_number;

수집 전후 검증표

바인드 변수에서는 한 번의 빠른 결과로 판단하지 않는다

리터럴 SQL은 값이 보이므로 히스토그램의 선택도를 계획에 직접 반영하기 쉽습니다. 바인드 SQL은 첫 하드 파스 때 값이 관찰될 수 있고, 값별 선택도가 크게 다르면 Adaptive Cursor Sharing이 여러 자식 커서를 관리할 수 있어요. 그렇다고 히스토그램이 값마다 알맞은 계획을 항상 보장하는 것은 아닙니다. 실행 순서, 커서 공유 상태, 통계 변경과 애플리케이션의 바인드 방식이 함께 영향을 줍니다.

희소 값 하나만 실행해 인덱스 계획이 나왔다고 끝내지 마세요. 같은 SQL ID에서 흔한 값과 희소 값을 모두 관찰하고, V$SQL의 child number·plan hash·실행 횟수와 V$SQL_CS_SELECTIVITY 같은 ACS 관련 뷰를 필요한 권한으로 점검합니다. 예상 행 수와 실제 행 수를 보는 방법은 DBMS_XPLAN의 E-Rows와 A-Rows 비교 절차를 함께 참고할 수 있습니다.

문제가 생기면 통계 이력으로 되돌린다

히스토그램만 떼어 과거 상태로 되돌리려 하기보다, 정상 계획이 사용되던 테이블 통계 전체 시점을 확인하는 편이 일관성을 지키기 쉽습니다. RESTORE_TABLE_STATS는 보존된 시점의 테이블·컬럼·관련 인덱스 통계를 함께 복원하며, 업무 데이터는 복원하지 않습니다. 복원 제한은 Oracle 19c 통계 이력 관리 문서에서 확인할 수 있습니다.

SELECT table_name, stats_update_time
FROM dba_tab_stats_history
WHERE owner = 'APP'
  AND table_name = 'ORDERS'
ORDER BY stats_update_time DESC;

BEGIN
  DBMS_STATS.RESTORE_TABLE_STATS(
    ownname         => 'APP',
    tabname         => 'ORDERS',
    as_of_timestamp => TO_TIMESTAMP_TZ(
      '2026-09-05 01:20:00 +09:00',
      'YYYY-MM-DD HH24:MI:SS TZH:TZM'
    ),
    no_invalidate   => DBMS_STATS.AUTO_INVALIDATE
  );
END;
/

히스토그램 운영에서 자주 생기는 실수

• 모든 인덱스 컬럼에 강제 생성: 인덱스 존재와 값 분포, 조건절 사용은 서로 다른 판단입니다. 필요하지 않은 통계 수집과 계획 변화를 늘릴 수 있어요.
• NUM_DISTINCT만 보고 결정: 값 개수가 같아도 실제 빈도와 SQL 사용 방식이 다르면 필요한 통계도 달라집니다.
• 희소 값 하나만 검증: 흔한 값의 응답 시간과 논리 읽기량이 나빠질 수 있습니다. 대표 값 묶음으로 비교하세요.
• SIZE AUTO 결과를 영구 상태로 가정: 데이터가 같아도 컬럼 사용 워크로드가 달라지면 자동 수집의 히스토그램 선택이 바뀔 수 있습니다.
• 히스토그램 삭제를 즉시 해결책으로 사용: 계획 불안정의 원인이 바인드, 상관관계, 오래된 통계, 인덱스 설계인지 먼저 분리해야 합니다.
• 복원 시점 없이 적용: 회귀가 난 뒤 정상 통계와 계획의 시점을 찾느라 대응이 늦어집니다.

히스토그램의 성공 기준은 HISTOGRAM 열이 NONE에서 다른 값으로 바뀌는 것이 아닙니다. 중요한 값들의 예상 행 수가 실제에 가까워지고, 그 차이가 더 적절한 접근 경로와 안정적인 업무 응답으로 이어져야 해요. 효과가 확인되지 않으면 컬럼 그룹 통계나 표현식 통계, 인덱스 설계, 바인드별 실행 특성처럼 다른 원인을 좁히는 편이 낫습니다.

공식 출처

• Oracle Database 19c SQL Tuning Guide — Histograms
• Oracle Database 19c SQL Tuning Guide — Optimizer Statistics Concepts
• Oracle Database 19c PL/SQL Packages and Types Reference — DBMS_STATS
• Oracle Database 19c SQL Tuning Guide — Managing Historical Optimizer Statistics