DBMS_STATS 통계 운영: 수집·Stale·Incremental·Pending·복구

핵심 요약

Optimizer Statistics 운영의 목적은 수집 횟수를 늘리는 것이 아니라 다음 흐름을 안정적으로 관리하는 것입니다.

업무·Data 변화 파악
→ 수집 대상과 Preference 결정
→ Statistics 수집
→ Pending·Test 환경 검증
→ Publish
→ 새 Child Cursor·Plan·Runtime 관찰
→ 문제 발생 시 Restore·Import·Pending 삭제

Oracle은 기본적으로 자동 Optimizer Statistics 수집을 제공하지만, 모든 Object와 Workload를 동일 정책으로 처리하면 안 됩니다.

정적 Code Table
  → 낮은 변경률·Lock 후보

대량 적재 Partition
  → Load 직후 Partition Statistics·Synopsis 관리

Histogram 민감 Bind SQL
  → Pending Statistics·대표 Bind 검증

Volatile Stage Table
  → Persistent Statistics보다 Dynamic Statistics 후보

핵심 기능입니다.

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → 옵티마이저와 통계정보 → DBMS_STATS 운영 범위에서 수집·Stale·Incremental·Pending·복구 정책을 다룹니다.

학습 목표

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

• DBMS_STATS와 ANALYZE의 역할을 구분한다.
• AUTO_SAMPLE_SIZE, METHOD_OPT, AUTO_CASCADE, AUTO_INVALIDATE의 의미를 설명한다.
• GATHER AUTO와 자동 Maintenance Task를 구분한다.
• STALE_STATS, STALE_PERCENT, Monitoring DML의 한계를 설명한다.
• Partition NDV를 단순 합산할 수 없는 이유를 설명한다.
• Synopsis를 이용한 Incremental Global Statistics 유지 흐름을 설명한다.
• Pending Statistics를 Session에서 검증하고 Publish·Delete하는 절차를 설명한다.
• Statistics History와 Export·Import의 보존 범위를 구분한다.
• Statistics Lock이 막는 것과 막지 않는 것을 설명한다.
• Statistics 변경 후 새 Hard Parse·대표 Bind·전체 Workload 회귀를 검증한다.

1. DBMS_STATS를 표준으로 사용한다

Oracle Optimizer Statistics는 DBMS_STATS Package로 관리합니다.

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

DBMS_STATS가 제공하는 대표 기능입니다.

• Table·Column·Index·System Statistics 수집
• Statistics Preference 설정
• Statistics 삭제·잠금·해제
• Pending Statistics Publish·Delete
• Statistics History Restore
• 사용자 Statistics Table Export·Import
• Reporting Mode·Advisor 지원

ANALYZE는 Structure Validation, Chained Row 조사 등 관리·진단 용도로 사용할 수 있지만 Optimizer Statistics 운영은 DBMS_STATS를 기준으로 통일합니다.

2. 주요 수집 Parameter

2.1 ESTIMATE_PERCENT

AUTO_SAMPLE_SIZE
  → Oracle이 적절한 Sample 크기를 결정
  → 기본값

고정 Sample 또는 100% 수집이 필요한지는 실제 문제 SQL로 검증합니다.

100% Sample
  ≠ 모든 Cardinality 문제 해결

Column 상관·Expression·Bind Skew
  → Extended Statistics·Histogram·SQL 구조가 필요할 수 있음

2.2 METHOD_OPT

Column Statistics와 Histogram 정책입니다.

FOR ALL COLUMNS SIZE AUTO
  → Data 분포와 Column Usage를 참고해 Histogram 자동 결정

FOR COLUMNS STATUS SIZE 254
  → STATUS에 최대 254 Bucket 요청

SIZE 1
  → Histogram 제거·미생성

Histogram은 값 편중과 값별 Plan 차이가 중요한 Column에만 사용합니다.

2.3 CASCADE

AUTO_CASCADE
  → 관련 Index Statistics 수집 필요 여부를 Oracle이 결정

CASCADE=>TRUE는 Table·Column Statistics와 함께 Index Statistics도 수집합니다.

2.4 GRANULARITY

Partitioned Table Statistics 범위를 지정합니다.

AUTO
ALL
GLOBAL
GLOBAL AND PARTITION
PARTITION
SUBPARTITION

Query의 실제 Pruning 범위와 Statistics 수집 범위를 일치시킵니다.

2.5 NO_INVALIDATE

Dependent Cursor의 무효화 시점을 제어합니다.

TRUE
  → Cursor를 즉시 무효화하지 않음

FALSE
  → 즉시 무효화 대상으로 표시

AUTO_INVALIDATE
  → 기본값
  → Rolling Invalidation으로 Hard Parse 집중 완화

NO_INVALIDATE는 새 Statistics의 품질을 검증하는 기능이 아닙니다. Plan 변경의 배포 시점과 Hard Parse 부하를 조절하는 기능입니다.

3. Preferences와 Parameter 우선순위

Table·Schema·Database·Global 수준의 Statistics Preference를 설정할 수 있습니다.

BEGIN
  DBMS_STATS.SET_TABLE_PREFS(
    ownname => 'APP',
    tabname => 'ORDERS',
    pname   => 'STALE_PERCENT',
    pvalue  => '5'
  );
END;
/

대표 Preference입니다.

• ESTIMATE_PERCENT
• METHOD_OPT
• CASCADE
• DEGREE
• GRANULARITY
• NO_INVALIDATE
• STALE_PERCENT
• PUBLISH
• INCREMENTAL
• INCREMENTAL_STALENESS
• AUTO_STAT_EXTENSIONS

운영에서는 실제 적용 Preference를 먼저 조회해야 합니다.

SELECT DBMS_STATS.GET_PREFS(
         pname   => 'STALE_PERCENT',
         ownname => 'APP',
         tabname => 'ORDERS'
       )
FROM dual;

Call Parameter와 저장 Preference의 우선순위 정책에 따라 기대와 다른 수집이 실행될 수 있으므로 수집 Log·결과 Statistics를 확인합니다.

4. 자동 Statistics 수집과 GATHER AUTO

4.1 자동 Maintenance Task

Oracle은 Maintenance Window의 자동 Task를 통해 Missing·Stale Object Statistics를 수집할 수 있습니다.

Maintenance Window
→ Missing·Stale Object 식별
→ 자동 Statistics 수집
→ Published Statistics 사용

자동 Task가 해결하지 못할 수 있는 상황입니다.

• Load 직후 Maintenance Window 전에 즉시 Query
• 수집 Window 안에 끝나지 않는 대형 Object
• Histogram 변동에 민감한 핵심 SQL
• 낮 동안 분포가 급변하는 Volatile Object
• Locked Statistics
• Partition Exchange 직후 Global·Partition 불일치

4.2 GATHER AUTO

DBMS_STATS의 OPTIONS=>'GATHER AUTO'는 필요한 Object를 자동 판단해 수집하는 수동 실행 옵션입니다.

자동 Maintenance Task
  → Scheduler·Maintenance Window의 자동 작업

GATHER AUTO
  → 사용자가 DBMS_STATS 호출 시 실행하는 수집 옵션

둘을 같은 기능으로 혼동하지 않습니다.

5. Stale Statistics

Oracle은 Table Monitoring의 근사 DML 변경량과 STALE_PERCENT Preference를 이용해 Stale 여부를 판단합니다.

SELECT table_name,
       num_rows,
       last_analyzed,
       stale_stats
FROM   user_tab_statistics
WHERE  table_name='ORDERS';

SELECT table_name,
       inserts,
       updates,
       deletes,
       timestamp
FROM   user_tab_modifications
WHERE  table_name='ORDERS';

5.1 상태 해석

STALE_STATS='YES'
  → 변경량 임계치를 넘은 재수집 후보

STALE_STATS='NO'
  → Stale 임계치를 넘지 않음

STALE_STATS IS NULL
  → Statistics 미수집 Object일 수 있음

기본 STALE_PERCENT는 일반적으로 10입니다.

5.2 Stale이 아니어도 문제가 되는 경우

• 전체 변경률은 낮지만 인기값 하나에 변경 집중
• HIGH_VALUE 밖 최신 날짜가 급증
• 특정 Tenant·Status 분포가 변경
• Column 상관관계 변화
• 작은 Table에서 적은 Row 변화가 Plan 경계 변경
• 신규 Partition Statistics 누락
• Histogram Endpoint가 현재 분포를 대표하지 않음

Stale 판단
  = 변경량 기반 재수집 후보 신호

Cardinality 정확성
  = 실제 Predicate·분포·Bind별 E-Rows 검증

5.3 Object별 임계치

Table 크기와 Plan 민감도에 맞게 설정합니다.

작은 핵심 Table
  → 낮은 임계치가 유리할 수 있음

대형 Stage Table
  → 변경률이 높아도 분포가 안정적일 수 있음

대형 Transaction Table
  → 특정 Partition 중심 정책 필요

6. Partition NDV와 Global Statistics

Partition별 NDV를 단순히 더하면 Global NDV가 되지 않습니다.

P1 customer_id NDV = 100,000
P2 customer_id NDV = 120,000

같은 customer_id가 P1·P2 모두 존재
→ Global NDV < 220,000 가능

Global Statistics는 다음을 표현해야 합니다.

• 전체 Table Row·Block 수
• Global Column NDV·Histogram
• 전체 Partition의 중복값 관계
• Global Index Statistics

Partition Statistics는 실제 Pruning 대상의 지역 분포를 표현합니다.

7. Incremental Statistics와 Synopsis

Incremental Statistics는 Partition별 Synopsis를 저장하고 이를 결합해 Global Statistics를 갱신합니다.

변경 Partition Statistics 수집
→ 해당 Partition Synopsis 생성·갱신
→ 기존 Partition Synopsis와 결합
→ Global NUM_ROWS·NDV 등 갱신

설정합니다.

BEGIN
  DBMS_STATS.SET_TABLE_PREFS(
    ownname => 'APP',
    tabname => 'SALES',
    pname   => 'INCREMENTAL',
    pvalue  => 'TRUE'
  );
END;
/

수집합니다.

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname     => 'APP',
    tabname     => 'SALES',
    granularity => 'AUTO',
    cascade     => DBMS_STATS.AUTO_CASCADE
  );
END;
/

7.1 이점

• 변경 Partition만 읽어 Global Statistics 유지
• 대형 Range Partition Table의 Full Scan 감소
• Partition Exchange·Rolling Load 운영 효율
• Partition 중복값을 고려한 Global NDV 계산

7.2 조건과 비용

• INCREMENTAL=TRUE가 필요
• 기존 Partition Synopsis 준비가 필요할 수 있음
• Synopsis 저장 공간과 수집 비용이 발생
• INCREMENTAL_LEVEL, INCREMENTAL_STALENESS 정책 영향
• Locked·Stale Partition 처리 규칙 확인
• 모든 Partition이 자주 변하면 이득이 제한적

Partition Exchange로 Synopsis를 유지하려면 Exchange 대상 Table의 Incremental Preference와 수집 방식도 맞아야 합니다.

8. Pending Statistics

Pending Statistics는 새 Statistics를 일반 Session에 공개하기 전에 Test Session에서 검증하는 기능입니다.

8.1 수집

BEGIN
  DBMS_STATS.SET_TABLE_PREFS(
    ownname => 'APP',
    tabname => 'ORDERS',
    pname   => 'PUBLISH',
    pvalue  => 'FALSE'
  );

  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'APP',
    tabname => 'ORDERS'
  );
END;
/

일반 Session은 기존 Published Statistics를 계속 사용합니다.

8.2 Test Session

ALTER SESSION SET optimizer_use_pending_statistics=TRUE;

반드시 새 Hard Parse를 유도해 다음을 확인합니다.

• Child Number·Plan Hash
• E-Rows·A-Rows
• Access Path·Join Order·Join Method
• 대표 희귀값·인기값 Bind
• Buffers·Reads·CPU·Elapsed·P95
• 다른 핵심 SQL Plan Regression

8.3 Publish·Delete

BEGIN
  DBMS_STATS.PUBLISH_PENDING_STATS('APP','ORDERS');
END;
/

BEGIN
  DBMS_STATS.DELETE_PENDING_STATS('APP','ORDERS');
END;
/

OPTIMIZER_USE_PENDING_STATISTICS는 Test Session 수준에서 사용하는 것이 안전합니다.

9. Statistics History

DBMS_STATS가 Dictionary Statistics를 변경하면 이전 Version이 History에 저장될 수 있습니다.

SELECT table_name,
       stats_update_time
FROM   user_tab_stats_history
WHERE  table_name='ORDERS'
ORDER  BY stats_update_time DESC;

복원합니다.

BEGIN
  DBMS_STATS.RESTORE_TABLE_STATS(
    ownname         => 'APP',
    tabname         => 'ORDERS',
    as_of_timestamp => TO_TIMESTAMP(
      '2026-07-28 22:00:00',
      'YYYY-MM-DD HH24:MI:SS'
    )
  );
END;
/

기본 Retention은 일반적으로 31일입니다.

확인합니다.

SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION
FROM dual;

Statistics History
  → 최근 Dictionary 변경 Version 복원

장기 보관·환경 이동
  → Export·Import 사용

10. Export·Import Statistics

사용자 Statistics Table에 Known-Good Statistics Set을 저장합니다.

BEGIN
  DBMS_STATS.CREATE_STAT_TABLE(
    ownname => 'APP',
    stattab => 'OPTSTAT_BACKUP'
  );

  DBMS_STATS.EXPORT_TABLE_STATS(
    ownname => 'APP',
    tabname => 'ORDERS',
    stattab => 'OPTSTAT_BACKUP',
    statid  => 'BEFORE_CHANGE'
  );
END;
/

복구·시험합니다.

BEGIN
  DBMS_STATS.IMPORT_TABLE_STATS(
    ownname => 'APP',
    tabname => 'ORDERS',
    stattab => 'OPTSTAT_BACKUP',
    statid  => 'BEFORE_CHANGE'
  );
END;
/

용도입니다.

• History Retention보다 장기 보관
• 운영→Test Statistics 이동
• 여러 Statistics Set 반복 비교
• 배포 전·후 Known-Good Version 보관

Statistics Table에 Export된 값은 Import해야 Optimizer Dictionary Statistics로 사용됩니다.

11. Statistics Lock

BEGIN
  DBMS_STATS.LOCK_TABLE_STATS('APP','CODE_TABLE');
END;
/

Lock 대상입니다.

• Table Statistics
• Column Statistics
• Histogram
• 관련 Index Statistics

Lock이 막지 않는 것:

• INSERT·UPDATE·DELETE
• SQL Text·Bind 변화
• System Statistics 변화
• Optimizer Parameter 변화
• Data 분포 변화

Lock 사용 후보입니다.

• 거의 변하지 않는 Code Table
• 의도적으로 대표 Statistics를 고정한 Object
• Dynamic Statistics 전략을 쓰는 Volatile Table
• 통제된 Statistics 배포가 필요한 민감 Object

새 Index·대량 Load가 발생하면 Unlock→수집→검증→재Lock 절차를 사용합니다.

12. NO_INVALIDATE와 Cursor 검증

Statistics 수집 후 기존 Cursor 처리 방식은 NO_INVALIDATE에 영향을 받습니다.

TRUE
  → 기존 Cursor를 즉시 무효화하지 않음

FALSE
  → 즉시 무효화 대상으로 표시

AUTO_INVALIDATE
  → Rolling Invalidation

주의합니다.

Statistics가 Published됨
  ≠ 모든 기존 Session이 즉시 새 Plan 사용

검증할 때는 다음을 구분합니다.

• 기존 Child Cursor
• Statistics 변경 후 새 Hard Parse된 Child
• Invalidation 시점
• Bind Peeking 값
• Plan Hash·E-Rows·Runtime

13. 안전한 Statistics 변경 절차

1. 문제 SQL·업무 시간대·Bind 분포를 확정한다.
2. 기존 Statistics·Preferences·History·Plan을 저장한다.
3. 변경 Object·Column·Partition을 최소화한다.
4. Pending 또는 Test 환경에서 Statistics를 수집한다.
5. 새 Hard Parse된 Child Cursor를 확인한다.
6. E-Rows·A-Rows뿐 아니라 Buffers·Reads·CPU·Elapsed·P95를 비교한다.
7. 희귀값·인기값·중간값과 OLTP·Batch SQL을 함께 검증한다.
8. Publish 후 Rolling Invalidation과 Plan 변화를 관찰한다.
9. 자동 Task가 다음 Window에 정책을 바꾸지 않는지 확인한다.
10. 실패 시 Pending 삭제·History Restore·Import로 복구한다.

14. 자주 혼동하는 판단