오라클 19c stale percent 변경 및 자동통계수집

OS 환경 : Oracle Linux 9.6 (64bit)

 

DB 환경 : Oracle Database 19.31.0.0

 

방법 : 오라클 19c stale percent 변경 및 자동통계수집

오라클 옵티마이저가 최적의 실행계획을 세우려면 정확한 통계 정보가 필수적임
데이터 변경(dml)이 발생할 때마다 통계를 수집할 수는 없기 때문에 오라클은 stale_percent 파라미터를 사용함
이 파라미터의 기본값은 10%임
테이블의 전체 로우 수 대비 10% 이상의 데이터가 변경(INSERT, UPDATE, DELETE)되면 해당 통계 정보를 만료(Stale) 상태로 간주함
통계가 만료되어 STALE_STATS 값이 YES로 변경된 테이블은 오라클의 자동 통계 수집(Auto Optimizer Stats Collection) 작업이 실행될 때 재수집 대상이 됨
본문에서는 STALE_PERCENT 설정값을 확인하고 대량 DML 발생 시 통계가 만료되는 과정과 자동 통계 수집 작업의 스케줄 뷰를 확인해봄

 

 

테스트
현재 STALE_PERCENT 값 확인

1 2 3 4 5 6 7 8 9

SQL>  set lines 200 pages 1000 col stale_pct for a20 select dbms_stats.get_prefs('STALE_PERCENT') as stale_pct  from dual;   STALE_PCT -------------------- 10

시스템 전체 STALE_PERCENT 기본값이 10%로 설정되어 있음

 

 

테스트 테이블 생성 및 데이터 삽입

1 2 3 4 5 6 7 8 9 10 11 12 13

SQL>  drop table stale_test purge; create table stale_test (     id  number,     val varchar2(100) );   insert into stale_test select level, 'DATA_'||level  from dual  connect by level <= 1000;   commit;

 

 

초기 통계 정보 수집 및 상태 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SQL> exec dbms_stats.gather_table_stats('IMSI', 'STALE_TEST');   PL/SQL procedure successfully completed.   SQL>  set lines 200 pages 1000 col table_name for a15 col stale_stats for a11 col last_anal for a20 select table_name, num_rows, stale_stats, to_char(last_analyzed, 'yyyy/mm/dd hh24:mi:ss') as last_anal  from dba_tab_statistics where table_name = 'STALE_TEST';   TABLE_NAME        NUM_ROWS STALE_STATS LAST_ANAL --------------- ---------- ----------- -------------------- STALE_TEST            1000 NO          2026/06/18 19:51:28

통계를 방금 수집했기 때문에 STALE_STATS 값은 NO 상태임

 

 

10%(100건) 이상의 데이터 추가 삽입

1 2 3 4 5 6 7 8 9 10 11

SQL>  insert into stale_test select level, 'NEW_DATA_'||level  from dual  connect by level <= 150;   150 rows created.   SQL> commit;   Commit complete.

 

 

데이터 변경 후 통계 만료 상태 재확인

1 2 3 4 5 6 7 8 9 10 11 12

SQL>  set lines 200 pages 1000 col table_name for a15 col stale_stats for a11 col last_anal for a20 select table_name, num_rows, stale_stats, to_char(last_analyzed, 'yyyy/mm/dd hh24:mi:ss') as last_anal  from dba_tab_statistics where table_name = 'STALE_TEST';   TABLE_NAME        NUM_ROWS STALE_STATS LAST_ANAL --------------- ---------- ----------- -------------------- STALE_TEST            1000 YES         2026/06/18 19:51:28

전체 1000건 중 150건(15%)이 변경되어 설정값(10%)을 초과했기 때문에 STALE_STATS 상태가 YES로 변경됨
이제 이 테이블은 다음번 유지보수 시간(Maintenance Window)에 자동으로 통계가 수집됨

 

 

STALE_STATS 상태가 변경되지 않았으면 아래 명령 사용 후 재확인
오라클은 DML 발생 건수를 메모리(SGA)에 가지고 있다가 주기적으로 딕셔너리에 반영하기 때문에 위 값이 변경되지 않았다면 강제 플러시(Flush) 해주면 됨

1 2 3

SQL> exec dbms_stats.flush_database_monitoring_info;   PL/SQL procedure successfully completed.

 

 

자동 통계 수집 작업 활성화 여부 확인

1 2 3 4 5 6 7 8 9 10 11

SQL>  set lines 200 pages 1000 col client_name for a35 col status for a10 select client_name, status  from dba_autotask_client where client_name = 'auto optimizer stats collection';   CLIENT_NAME                         STATUS ----------------------------------- ---------- auto optimizer stats collection     ENABLED

활성화 되어있음

 

 

자동 통계 윈도우 시간 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41

SQL> set lines 200 pages 1000 col window_name for a30 select window_name, window_active, autotask_status,  optimizer_stats, to_char(window_next_time, 'yyyy/mm/dd hh24:mi:ss') as next_run from dba_autotask_window_clients order by window_name;   WINDOW_NAME                    WINDO AUTOTASK OPTIMIZE NEXT_RUN ------------------------------ ----- -------- -------- ------------------- MONDAY_WINDOW                  FALSE ENABLED  ENABLED  2026/06/22 22:00:00 TUESDAY_WINDOW                 FALSE ENABLED  ENABLED  2026/06/23 22:00:00 WEDNESDAY_WINDOW               FALSE ENABLED  ENABLED  2026/06/24 22:00:00 THURSDAY_WINDOW                FALSE ENABLED  ENABLED  2026/06/18 22:00:00 FRIDAY_WINDOW                  FALSE ENABLED  ENABLED  2026/06/19 22:00:00 SATURDAY_WINDOW                FALSE ENABLED  ENABLED  2026/06/20 06:00:00 SUNDAY_WINDOW                  FALSE ENABLED  ENABLED  2026/06/21 06:00:00   7 rows selected.   SQL>  set lines 200 pages 1000 col window_name for a20 col repeat_interval for a70 col duration for a15 select window_name, repeat_interval, duration  from dba_scheduler_windows;   WINDOW_NAME          REPEAT_INTERVAL                                                        DURATION -------------------- ---------------------------------------------------------------------- --------------- MONDAY_WINDOW        freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0                  +000 04:00:00  <<-- !! TUESDAY_WINDOW       freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0                  +000 04:00:00  <<-- !! WEDNESDAY_WINDOW     freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0                  +000 04:00:00  <<-- !! THURSDAY_WINDOW      freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0                  +000 04:00:00  <<-- !! FRIDAY_WINDOW        freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0                  +000 04:00:00  <<-- !! SATURDAY_WINDOW      freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0                   +000 20:00:00  <<-- !! SUNDAY_WINDOW        freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0                   +000 20:00:00  <<-- !! WEEKNIGHT_WINDOW     freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0  +000 08:00:00 WEEKEND_WINDOW       freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0                    +002 00:00:00   9 rows selected.

결과를 보면 평일은 밤 10시에 시작해서 4시간 동안 수행되고 주말은 아침 6시에 시작해 20시간 동안 수행됨

 

 

현재 시간 확인

1 2	SQL> !date Thu Jun 18 20:01:09 KST 2026

6월 18일 목요일 20시임, 2시간 뒤에 이 테이블의 통계정보가 자동으로 수집될 예정임

 

 

22시 이후 테스트 테이블 마지막 통계 수집 일자 확인

1 2 3 4 5 6 7 8 9 10 11 12

SQL>  set lines 200 pages 1000 col table_name for a15 col stale_stats for a11 col last_anal for a20 select table_name, num_rows, stale_stats, to_char(last_analyzed, 'yyyy/mm/dd hh24:mi:ss') as last_anal  from dba_tab_statistics where table_name = 'STALE_TEST';   TABLE_NAME        NUM_ROWS STALE_STATS LAST_ANAL --------------- ---------- ----------- -------------------- STALE_TEST            1000 NO          2026/06/18 22:00:10

STALE_STATS가 NO로 변하고 LAST_ANAL도 22시로 업데이트됨

 

 

참고. STALE_PERCENT 값 변경 방법

1 2 3 4 5 6 7 8

#DB 전체의 STALE_PERCENT 값 변경 SQL> exec dbms_stats.set_database_prefs('stale_percent', 변경할_수치);   #특정 유저의 STALE_PERCENT 값 변경 SQL> exec dbms_stats.set_schema_prefs('스키마명', 'stale_percent', 변경할_수치);   #특정 테이블의 STALE_PERCENT 값 변경 SQL> exec dbms_stats.set_table_prefs('스키마명', '테이블명', 'stale_percent', 변경할_수치);

 

 

결론 :
STALE_PERCENT는 데이터베이스 환경에서 통계 수집 부하를 제어하는 핵심 파라미터임
전체 시스템의 자동 통계 수집은 STALE_STATS가 YES인 객체들만 선별해서 작업을 진행함
만약 1억 건이 넘는 대용량 테이블의 경우 10% 변경이 발생하기까지 오랜 시간이 걸려 통계가 부정확해지는 성능 문제가 발생할 수 있음
이런 큰 테이블들은 dbms_stats.set_table_prefs로 테이블 레벨의 STALE_PERCENT 값을 1~5% 정도로 낮춰서 관리할수도 있음
자동통계수집을 꺼놓는다면 STALE_PERCENT가 YES로 되어있어도 통계정보는 수집되지 않음, 다만 데이터가 일정량 이상 변경됬다는 것만 알수있음

 

 

참조 : 

https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_STATS.html#GUID-2C00FE80-1553-404C-85B6-220895561FE8
오라클 19c autotask, 자동통계수집 잡 확인 및 비활성화 ( https://positivemh.tistory.com/1276 )
https://tuna.tistory.com/48