통계를 수동으로 잠그면(LOCK_TABLE_STATS) 실행계획이 항상 고정되나?

아니다. 통계 잠금은 통계값이 바뀌지 않도록 막을 뿐, 실행계획 자체를 고정하지는 않는다. 바인드 값, 초기화 파라미터, 다른 테이블의 통계 변화에 따라 계획은 여전히 바뀔 수 있다. 계획 자체를 고정하려면 SQL Plan Baseline 같은 별도 기능을 검토해야 한다.

estimate_percent를 낮게 잡아 빠르게 수집하는 게 좋은가?

권하지 않는다. 기본값 AUTO_SAMPLE_SIZE는 정확도와 속도를 함께 고려해 표본을 정하고, HYBRID나 Top-Frequency 같은 정밀한 히스토그램은 이 자동 표본에서만 생성된다. 수동으로 낮게 잡으면 이런 히스토그램이 안 만들어지고 추정 정확도가 떨어질 수 있다.

STALE_PERCENT 기본값은 얼마이고 바꿀 수 있나?

기본값은 10%다. 마지막 수집 이후 전체 행의 10% 이상이 변경되면 통계가 오래된 것으로 표시된다. DBMS_STATS.SET_TABLE_PREFS나 SET_GLOBAL_PREFS로 테이블별 또는 전역으로 조정할 수 있다.

SET_TABLE_STATS로 넣은 값이 자동 잡에 다시 덮여쓰이지 않게 하려면?

LOCK_TABLE_STATS로 통계를 잠그면 된다. 잠긴 상태에서는 자동 통계 수집 잡이나 일반 GATHER 호출이 통계를 바꾸지 못한다. 잠긴 통계를 의도적으로 덮어쓰려면 해당 프로시저에 force => TRUE를 넘겨야 한다.

재수집이 오히려 계획을 나쁘게 만들면 어떻게 되돌리나?

Oracle은 통계 갱신 시 이전 버전을 자동 보관한다. USER_TAB_STATS_HISTORY에서 시점을 확인한 뒤 DBMS_STATS.RESTORE_TABLE_STATS에 as_of_timestamp로 원하는 시점을 지정해 복원하면 된다.

Oracle 통계정보가 오래돼 쿼리가 느려질 때

운영하다 보면 코드도 그대로고 데이터량도 크게 안 늘었는데 특정 쿼리만 어느 날부터 느려지는 상황을 마주친다. 인덱스도 멀쩡하고 바인드 값도 평소와 같은데 실행계획만 바뀌어 있다. 이런 신고를 받으면 나는 제일 먼저 옵티마이저 통계부터 의심한다. 통계가 실제 데이터와 어긋나면 옵티마이저가 잘못된 판단을 하고, 그 판단이 실행계획을 바꾸기 때문이다.

결론부터 말하자면, 이런 증상의 상당수는 통계정보가 오래돼(stale) 옵티마이저의 행 수 추정이 실제와 크게 벌어지면서 생긴다. 진단은 DBMS_XPLAN으로 추정 행 수(E-Rows)와 실제 행 수(A-Rows)를 비교하는 것으로 시작하고, 해결은 DBMS_STATS로 통계를 다시 수집하거나, 재발이 잦은 테이블은 통계를 잠그고 대표값으로 고정하는 순서로 접근한다.

1. 옵티마이저 통계와 카디널리티

Oracle의 비용 기반 옵티마이저(CBO)는 테이블의 행 수, 블록 수, 컬럼별 값 분포 같은 통계를 근거로 실행계획을 고른다. 이 통계가 옵티마이저 통계다. 옵티마이저는 이 값으로 각 단계에서 몇 건이 나올지를 미리 어림잡는데, 이 어림 건수를 카디널리티(cardinality)라고 부른다.

카디널리티 추정이 맞으면 조인 순서, 조인 방식(nested loop 대 hash join), 인덱스 사용 여부가 대체로 합리적으로 정해진다. 반대로 추정이 어긋나면 문제가 커진다. 실제로는 수십만 건이 나오는 조인을 옵티마이저가 몇 건으로 잘못 보면, 대량 데이터에 nested loop를 선택해 인덱스를 한 건씩 수백만 번 타는 계획을 만든다. 통계가 오래돼 실제 데이터와 벌어질수록 이 추정 오차가 커진다.

2. 통계가 실제와 얼마나 어긋났는지 확인한다

느려진 쿼리를 실제로 실행해 추정과 실제를 나란히 비교하는 것이 가장 확실하다. GATHER_PLAN_STATISTICS 힌트를 넣어 한 번 돌린 뒤, DBMS_XPLAN.DISPLAY_CURSOR를 ALLSTATS LAST 형식으로 본다.

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */ o.order_id, c.name
  2  FROM   orders o JOIN customers c ON c.cust_id = o.cust_id
  3  WHERE  o.order_date >= DATE '2026-09-01';

... 행 반환 ...

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST'));

출력에서 E-Rows(옵티마이저 추정)와 A-Rows(실제 반환)를 비교한다. 아래처럼 추정은 12건인데 실제는 48만 건이면 카디널리티 오류다.

---------------------------------------------------------------------------------
| Id | Operation                     | Name       | Starts | E-Rows | A-Rows |
---------------------------------------------------------------------------------
|  0 | SELECT STATEMENT              |            |      1 |        | 480K   |
|  1 |  NESTED LOOPS                 |            |      1 |     12 | 480K   |
|  2 |   TABLE ACCESS FULL           | ORDERS     |      1 |     12 | 480K   |
|  3 |   TABLE ACCESS BY INDEX ROWID | CUSTOMERS  |   480K |      1 | 480K   |
---------------------------------------------------------------------------------

E-Rows와 A-Rows가 수십 배에서 수천 배 벌어지는 단계가 있으면, 그 단계의 통계를 옵티마이저가 잘못 알고 있다는 뜻이다. 여기서는 ORDERS의 최근 날짜 조건에 대한 추정이 완전히 빗나갔다.

3. 통계가 오래됐는지(stale) 판단한다

Oracle은 테이블에 발생한 DML 건수를 내부적으로 추적한다. 마지막 통계 수집 이후 변경(입력, 수정, 삭제)이 일정 비율을 넘으면 통계를 오래된 것으로 표시한다. 이 기준이 STALE_PERCENT이고 기본값은 10%다. 즉 행의 10% 이상이 바뀌면 stale로 본다.

마지막 수집 시각과 stale 여부는 딕셔너리 뷰로 확인한다.

SQL> SELECT table_name, num_rows, last_analyzed, stale_stats
  2  FROM   user_tab_statistics
  3  WHERE  table_name IN ('ORDERS','CUSTOMERS');

TABLE_NAME   NUM_ROWS  LAST_ANALYZED        STALE_STATS
-----------  --------  -------------------  -----------
ORDERS          12000  2026-06-30 22:14:03  YES
CUSTOMERS      250000  2026-09-14 03:02:11  NO

ORDERS의 NUM_ROWS는 12000인데 실제로는 48만 건 넘게 조회됐다. 통계상 행 수와 실제 행 수 차이가 이렇게 크면 진단은 명확하다. 아직 반영되지 않은 변경량은 USER_TAB_MODIFICATIONS에서 볼 수 있다. 다만 이 뷰는 변경 정보가 딕셔너리에 반영되기까지 지연이 있어, 최신 값을 보려면 먼저 플러시한다.

SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

SQL> SELECT table_name, inserts, updates, deletes
  2  FROM   user_tab_modifications
  3  WHERE  table_name = 'ORDERS';

TABLE_NAME   INSERTS  UPDATES  DELETES
-----------  -------  -------  -------
ORDERS        470000        0        0

4. DBMS_STATS로 통계를 다시 수집한다

가장 먼저 할 일은 해당 테이블 통계를 최신으로 다시 수집하는 것이다. 대부분의 경우 이것만으로 실행계획이 정상으로 돌아온다.

SQL> BEGIN
  2    DBMS_STATS.GATHER_TABLE_STATS(
  3      ownname          => USER,
  4      tabname          => 'ORDERS',
  5      estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  6      method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
  7      cascade          => TRUE);
  8  END;
  9  /

PL/SQL procedure successfully completed.

기본값을 그대로 쓰는 것을 권한다. estimate_percent의 기본값 AUTO_SAMPLE_SIZE는 Oracle이 표본 크기를 알아서 정하며, 정확한 히스토그램 유형(HYBRID, Top-Frequency)은 이 자동 표본에서만 생성된다. 예전에 estimate_percent를 수동으로 낮게 잡던 스크립트가 아직 돌아다니는데, 지금은 굳이 그럴 이유가 없다.

cascade => TRUE는 인덱스 통계까지 함께 수집한다. method_opt의 기본값 'FOR ALL COLUMNS SIZE AUTO'는 컬럼 사용 이력과 데이터 분포를 근거로 히스토그램이 필요한 컬럼에만 만든다.

수집 후 다시 DISPLAY_CURSOR로 E-Rows와 A-Rows가 가까워졌는지 확인한다. 추정이 실제에 근접하면 옵티마이저가 hash join과 full scan 조합으로 계획을 바꾸는 것을 볼 수 있다.

5. 자동 통계 수집 잡을 확인한다

Oracle 10g부터 유지관리 윈도(주로 야간과 주말)에 통계를 자동 수집하는 잡이 돌아간다. 잡 이름은 "auto optimizer stats collection"이다. 이 잡은 stale로 표시된 객체를 대상으로 통계를 갱신한다. 통계가 갑자기 오래된 상태로 방치됐다면, 이 잡이 꺼져 있거나 윈도가 짧아 대상 객체를 다 못 돌았을 수 있다.

SQL> SELECT client_name, status
  2  FROM   dba_autotask_client
  3  WHERE  client_name = 'auto optimizer stats collection';

CLIENT_NAME                          STATUS
-----------------------------------  --------
auto optimizer stats collection      ENABLED

꺼져 있으면 활성화한다.

SQL> BEGIN
  2    DBMS_AUTO_TASK_ADMIN.ENABLE(
  3      client_name => 'auto optimizer stats collection',
  4      operation   => NULL,
  5      window_name => NULL);
  6  END;
  7  /

야간 배치로 대량 적재가 끝나는 테이블이라면, 자동 잡의 윈도를 기다리지 말고 적재 직후 그 테이블만 명시적으로 수집하도록 배치 끝에 GATHER_TABLE_STATS를 붙이는 편을 권한다. 데이터가 크게 바뀐 직후 그 통계로 첫 쿼리가 들어오는 시간대가 가장 위험하기 때문이다.

6. 히스토그램과 편중된 데이터

히스토그램은 한 컬럼 안에서 값이 고르게 분포하지 않을 때 그 편중을 옵티마이저에 알려주는 통계다. 예를 들어 status 컬럼에 값이 'DONE' 98%, 'PENDING' 2%처럼 쏠려 있으면, 히스토그램이 없는 옵티마이저는 두 값이 반반이라고 가정해 'PENDING' 조회 건수를 크게 부풀린다.

이런 편중 컬럼을 조건에 자주 쓴다면 히스토그램이 카디널리티 추정을 크게 개선한다. SIZE AUTO가 자동으로 판단하지만, 특정 컬럼에 반드시 필요하다면 명시할 수 있다. 히스토그램의 버킷 수는 AUTO와 SKEWONLY에서 최대 254개다.

SQL> BEGIN
  2    DBMS_STATS.GATHER_TABLE_STATS(
  3      ownname    => USER,
  4      tabname    => 'ORDERS',
  5      method_opt => 'FOR COLUMNS SIZE 254 STATUS');
  6  END;
  7  /

SQL> SELECT column_name, histogram, num_buckets
  2  FROM   user_tab_col_statistics
  3  WHERE  table_name = 'ORDERS' AND column_name = 'STATUS';

COLUMN_NAME  HISTOGRAM        NUM_BUCKETS
-----------  ---------------  -----------
STATUS       FREQUENCY                  3

주의할 점이 하나 있다. 히스토그램이 있으면 바인드 변수 값에 따라 실행계획이 달라질 수 있다(bind peeking). 값이 편중된 컬럼에서 이 동작이 오히려 계획을 흔들 수 있으니, 히스토그램을 넣기 전후로 대표 값 몇 개에 대한 실행계획을 반드시 비교한다.

7. 재발이 잦으면 통계를 잠그고 고정한다

데이터가 하루에도 크게 출렁이는 테이블은 자동 수집이 오히려 계획을 계속 바꿔 불안정을 만든다. 이럴 때는 검증된 대표 통계로 고정하고 잠그는 방법이 실무에서 유효하다. 순서는 원하는 상태로 통계를 맞춘 뒤 LOCK_TABLE_STATS로 잠그는 것이다.

LOCK_TABLE_STATS로 잠그면 테이블 통계, 컬럼 통계, 히스토그램, 의존 인덱스 통계가 모두 잠기고, 이후 자동 잡이나 일반 수집이 이 통계를 바꾸지 못한다.

SQL> EXEC DBMS_STATS.LOCK_TABLE_STATS(USER, 'ORDERS');

SQL> SELECT table_name, stattype_locked
  2  FROM   user_tab_statistics
  3  WHERE  table_name = 'ORDERS';

TABLE_NAME   STATTYPE_LOCKED
-----------  ---------------
ORDERS       ALL

대표 행 수와 블록 수를 직접 지정하려면 SET_TABLE_STATS를 쓴다. 이 프로시저는 테스트나 안정화 목적으로 통계를 인위적으로 설정한다. 잠긴 통계를 덮어써야 하므로 force => TRUE를 함께 넘긴다.

SQL> BEGIN
  2    DBMS_STATS.SET_TABLE_STATS(
  3      ownname       => USER,
  4      tabname       => 'ORDERS',
  5      numrows       => 500000,
  6      numblks       => 9800,
  7      avgrlen       => 120,
  8      no_invalidate => FALSE,
  9      force         => TRUE);
 10  END;
 11  /

PL/SQL procedure successfully completed.

numrows는 행 수, numblks는 블록 수, avgrlen은 평균 행 길이다. no_invalidate => FALSE는 이 변경으로 관련 커서를 즉시 무효화해 다음 실행부터 새 통계로 계획을 세우게 한다. 기본 동작에 맡기면 무효화가 지연될 수 있어, 즉시 반영이 필요하면 명시한다.

다시 수집이 필요해지면 UNLOCK_TABLE_STATS로 풀고 GATHER_TABLE_STATS를 돌린다. 잠금 자체를 잊지 않도록, 잠근 테이블 목록을 운영 문서에 남기는 것을 권한다.

SQL> EXEC DBMS_STATS.UNLOCK_TABLE_STATS(USER, 'ORDERS');

8. 되돌리기가 필요할 때

새로 수집한 통계가 오히려 계획을 나쁘게 만들 수도 있다. Oracle은 통계를 갱신할 때 이전 버전을 자동으로 보관한다. 문제가 생기면 특정 시점 통계로 복원할 수 있다.

SQL> SELECT table_name, stats_update_time
  2  FROM   user_tab_stats_history
  3  WHERE  table_name = 'ORDERS'
  4  ORDER  BY stats_update_time DESC;

SQL> BEGIN
  2    DBMS_STATS.RESTORE_TABLE_STATS(
  3      ownname         => USER,
  4      tabname         => 'ORDERS',
  5      as_of_timestamp => SYSTIMESTAMP - 1);
  6  END;
  7  /

이 복원 이력이 있으니, 통계를 다시 수집하는 조치는 되돌릴 여지가 있는 안전한 첫 수라고 볼 수 있다. 통계 잠금과 고정은 그다음 단계로, 재발이 확인된 테이블에만 선택적으로 적용한다.

9. 정리

잘 돌던 쿼리가 갑자기 느려지면 실행계획 변화를 보고, 그 원인으로 오래된 옵티마이저 통계를 먼저 의심한다. 진단은 DBMS_XPLAN의 E-Rows 대 A-Rows 비교와 STALE_STATS, LAST_ANALYZED 확인으로 좁힌다. 해결의 기본은 GATHER_TABLE_STATS 재수집이고, 대량 적재 직후에는 배치 끝에서 명시적으로 수집한다. 데이터가 심하게 출렁여 계획이 계속 불안정한 테이블만 LOCK_TABLE_STATS와 SET_TABLE_STATS로 대표 통계에 고정한다. 잘못됐을 때는 RESTORE_TABLE_STATS로 되돌린다.