Oracle 19c 통계정보 오래됨 확인: DBMS_STATS 안전 수집과 복원 순서

Oracle 19c에서 통계정보가 오래됐는지는 날짜만 보지 말고 DBA_TAB_STATISTICS.STALE_STATS와 실제 데이터 변화, 느려진 SQL의 실행계획을 함께 확인해야 해요. STALE_STATS='YES'라면 수집 후보라는 뜻이지, 즉시 전체 스키마 통계를 다시 모으라는 명령은 아닙니다.

먼저 대상 테이블 한 개의 상태와 통계 이력을 남기고, 업무가 한산한 시간에 필요한 범위만 수집하세요. 수집 뒤 대표 SQL의 계획이 나빠지면, 보존된 통계 이력과 정상 계획을 확인해 이전 통계 복원을 검토하세요. 데이터 자체를 되돌리는 작업은 아닙니다.

통계가 오래됐다는 신호를 어떻게 판단하나

옵티마이저 통계는 행 수, 블록 수, 컬럼 값 분포처럼 실행계획 비용 계산에 쓰는 정보예요. Oracle은 테이블 변경량을 모니터링하고 DBMS_STATS의 STALE_PERCENT 설정과 비교해 통계가 낡았는지를 판단합니다. 그래서 마지막 수집일이 오래됐더라도 데이터가 거의 바뀌지 않았다면 반드시 다시 수집할 필요는 없습니다.

적용 조건·영향 범위

Oracle 12.2부터는 DBA_TAB_STATISTICS와 DBA_TAB_MODIFICATIONS가 메모리와 디스크의 정보를 함께 보여 주므로, 상태 확인만을 위해 DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO를 습관적으로 호출할 필요가 없습니다. 이 동작은 Oracle 19c의 옵티마이저 통계 수집 문서에 설명돼 있습니다.

작은 테스트 테이블로 통계 변화를 재현한다

아래 예제는 전용 테스트 스키마에서만 실행하세요. SQL은 Oracle 19c 공식 문서를 대조한 예시이며, 이 글을 작성하며 실제 Oracle 인스턴스에서 실행하지는 않았어요. 표시한 행 수와 상태는 확인할 예상 결과입니다.

CREATE TABLE stats_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 stats_demo_orders_pk PRIMARY KEY (order_id)
);

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

COMMIT;

이 예제의 값 분포는 통계가 단순한 행 수뿐 아니라 컬럼 분포와 실행계획에도 연결된다는 점을 보여 주기 위한 것입니다. 히스토그램을 무조건 만들라는 뜻은 아니며, METHOD_OPT는 기본 자동 판단을 우선합니다.

실습을 계속할 때는 뒤에 나오는 APP.ORDERS를 그대로 쓰지 마세요. 소유자는 현재 테스트 계정으로, 테이블명은 STATS_DEMO_ORDERS로 바꿉니다. 운영 점검 SQL의 APP과 ORDERS는 설명용 이름입니다.

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => USER,
    tabname => 'STATS_DEMO_ORDERS',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    degree => 1
  );
END;
/

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

UPDATE stats_demo_orders
SET status_code = 'HOLD'
WHERE order_id BETWEEN 1 AND 3000;
COMMIT;

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

UPDATE는 행 잠금과 UNDO·REDO를 만들고 COMMIT에서 확정됩니다. 실습이 끝났다고 운영 테이블까지 같은 UPDATE를 실행하면 안 됩니다. 테스트 객체 정리는 이름과 소유자를 다시 확인한 뒤 별도 작업으로 진행하세요. 통계 수집·복원은 이 UPDATE로 바뀐 업무 값을 복구하지 않습니다.

실행 전에 현재 통계와 변경량을 남긴다

자기 스키마라면 USER_TAB_STATISTICS를, 다른 스키마까지 점검한다면 필요한 데이터 사전 조회 권한을 갖고 DBA_TAB_STATISTICS를 사용하세요. 여기서는 DBA가 특정 업무 테이블을 확인하는 상황을 기준으로 씁니다.

SELECT owner,
       table_name,
       partition_name,
       num_rows,
       sample_size,
       last_analyzed,
       stale_stats,
       stattype_locked
FROM dba_tab_statistics
WHERE owner = 'APP'
  AND table_name = 'ORDERS'
ORDER BY partition_name NULLS FIRST;

SELECT table_owner,
       table_name,
       partition_name,
       inserts,
       updates,
       deletes,
       truncated,
       timestamp
FROM dba_tab_modifications
WHERE table_owner = 'APP'
  AND table_name = 'ORDERS'
ORDER BY partition_name NULLS FIRST;

기본 임계값을 10%로 외우는 것보다 대상 테이블의 실제 STALE_PERCENT 설정을 읽는 편이 안전합니다. 테이블별 설정이 없으면 전역 기본값이 적용될 수 있습니다.

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

수집 전후 검증표

필요한 테이블만 DBMS_STATS로 수집한다

자동 통계 수집이 정상적으로 관리되는 시스템이라면 수동 수집이 늘 정답은 아닙니다. 대량 적재 직후처럼 자동 작업을 기다리기 어렵고, 현재 통계가 실제 데이터를 크게 벗어나며, 대표 SQL 검증 계획이 있을 때 대상 테이블부터 좁게 실행하세요.

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,
    degree           => 1,
    no_invalidate    => DBMS_STATS.AUTO_INVALIDATE
  );
END;
/

DBMS_STATS 매개변수 문서를 기준으로 대상의 기존 설정을 먼저 확인하세요. 특히 PUBLISH가 FALSE이면 수집 통계가 pending 상태일 수 있고, PREFERENCE_OVERRIDES_PARAMETER가 TRUE이면 명시한 인수보다 설정이 우선할 수 있습니다. 이를 모르고 설정을 바꾸거나 즉시 적용을 가정하지 마세요.

파티션 테이블이라면 새로 적재된 파티션과 전역 통계의 관계를 따로 설계해야 합니다. granularity 기본값인 AUTO는 파티션 유형에 맞게 범위를 고르지만, 대형 파티션 테이블은 증분 통계 설정과 전역 통계 유지 방식을 DBA 정책으로 확인해야 해요.

수집 후에는 날짜가 아니라 대표 SQL을 검증한다

통계가 최신으로 표시돼도 쿼리가 빨라졌다는 뜻은 아닙니다. 새 통계가 데이터 분포를 더 정확히 표현하면서도, 특정 바인드 값이나 히스토그램 때문에 다른 계획을 선택할 수 있어요. 먼저 대상 통계를 다시 조회하고, 자동 작업이나 스키마 단위 작업과 시간이 겹쳤는지도 확인합니다. DBA_OPTSTAT_OPERATIONS는 스키마·데이터베이스 수준 작업 이력을 확인하는 보조 자료입니다.

SELECT operation,
       target,
       start_time,
       end_time,
       status
FROM dba_optstat_operations
WHERE start_time >= SYSTIMESTAMP - INTERVAL '1' DAY
ORDER BY start_time DESC
FETCH FIRST 10 ROWS ONLY;

예상 행 수와 실제 행 수의 차이는 DBMS_XPLAN에서 E-Rows와 A-Rows를 비교하는 순서로 확인할 수 있습니다. 새 통계 전후의 plan hash, 첫 추정 오차 지점, 논리 읽기량을 함께 보세요.

계획이 나빠졌다면 이전 통계로 되돌린다

복원 범위와 제한은 Oracle의 통계 이력 관리 문서를 기준으로 확인합니다. 사용자 정의 통계는 복원 대상이 아니고, 테이블을 삭제·재생성했다면 이전 이력이 그대로 남아 있다고 가정하면 안 됩니다.

Oracle은 데이터 사전의 통계가 바뀔 때 과거 버전을 자동으로 보관합니다. 보존 기간 안이라면 DBA_TAB_STATS_HISTORY에서 복원할 시점을 찾고 RESTORE_TABLE_STATS로 테이블·컬럼·관련 인덱스 통계를 함께 복원할 수 있어요. ANALYZE 명령으로 수집한 옛 통계는 이 복원 이력에 저장되지 않는다는 제한도 있습니다.

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-01 01:55:00 +09:00',
      'YYYY-MM-DD HH24:MI:SS TZH:TZM'
    ),
    no_invalidate   => DBMS_STATS.AUTO_INVALIDATE
  );
END;
/

흔한 실수는 수집 범위와 검증 생략에서 생긴다

SQL이 느린 이유가 행 잠금 대기라면 통계를 다시 모아도 차단 세션은 사라지지 않아요. 먼저 V$SESSION·V$LOCK으로 블로킹 세션을 확인하는 절차로 대기 원인을 분리하세요.

• LAST_ANALYZED만 보고 전부 수집: 데이터 변화가 거의 없는 큰 테이블까지 스캔합니다. stale 상태와 변경량을 함께 보세요.
• 성능 저하를 통계 탓으로 단정: 잠금 대기, I/O 병목, 바인드 값, 코드 변경도 먼저 분리해야 합니다.
• 스키마 전체를 피크 시간에 수집: CPU·I/O 사용량과 여러 SQL의 계획 변화를 동시에 키웁니다.
• 통계 잠금을 이유 없이 해제: 고정된 배치 계획이나 특수 적재 정책을 깨뜨릴 수 있습니다.
• 수집 성공만 확인: 대표 SQL의 plan hash와 E-Rows/A-Rows, 응답 시간, 논리 읽기를 비교하지 않으면 계획 회귀를 놓칩니다.
• 복원 시점을 준비하지 않음: 문제가 생긴 뒤 이력과 정상 계획 시점을 찾느라 대응이 늦어집니다.

다시 수집하기 전에 남겨 둘 기록

작업 기록에는 테이블명과 수집 시각뿐 아니라 같은 바인드 값으로 비교한 대표 SQL, 응답 시간, 복원할 통계 시점을 함께 남겨 두세요. 계획이 바뀌었다는 사실과 업무가 느려졌다는 사실을 구분할 수 있어야 다음 조치도 좁힐 수 있어요. 통계가 이미 충분하다면 수집을 멈추고 다른 병목을 찾는 것이 이번 점검의 올바른 결과일 수 있습니다.

공식 출처

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