오래된(stale) 통계 찾기 및 테이블별 통계 선호 설정(prefs)

명령어

-- 1) Flush DML counters to the dictionary and list stale objects:
EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

SELECT owner, table_name, partition_name, num_rows,
       TO_CHAR(last_analyzed,'YYYY-MM-DD HH24:MI') AS last_analyzed,
       stale_stats, stattype_locked
FROM   dba_tab_statistics
WHERE  owner = 'CUSTOMER_APP'
  AND  (stale_stats = 'YES' OR last_analyzed IS NULL)
ORDER  BY table_name, partition_name;

SELECT table_name, partition_name, inserts, updates, deletes, truncated
FROM   dba_tab_modifications
WHERE  table_owner = 'CUSTOMER_APP';

-- 2) Per-table preferences (used by the auto stats job and manual GATHER calls):
EXEC DBMS_STATS.SET_TABLE_PREFS('CUSTOMER_APP', 'SALES', 'STALE_PERCENT', '5');
EXEC DBMS_STATS.SET_TABLE_PREFS('CUSTOMER_APP', 'SALES', 'METHOD_OPT', 'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS SIZE 254 CUSTOMER_ID');
EXEC DBMS_STATS.SET_TABLE_PREFS('CUSTOMER_APP', 'SALES', 'DEGREE', '4');

SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT', 'CUSTOMER_APP', 'SALES') AS stale_pct FROM dual;
SELECT table_name, preference_name, preference_value
FROM   dba_tab_stat_prefs WHERE owner = 'CUSTOMER_APP';

-- 3) Gather only stale objects:
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'CUSTOMER_APP', options => 'GATHER STALE');
-- Revert a preference: EXEC DBMS_STATS.DELETE_TABLE_PREFS('CUSTOMER_APP', 'SALES', 'STALE_PERCENT');

하는 일

변경 비율이 STALE_PERCENT(기본값 10%)를 넘으면 통계가 stale 상태가 된다. 선호 설정(preference)은 테이블별 샘플링, 히스토그램, 병렬도, stale 임계값을 영구 저장하여 야간 자동 통계 작업이 같은 규칙을 적용하게 한다.

사용 시점

대형 테이블에서 10% 임계값이 너무 늦게 동작할 때, 특정 컬럼에 항상 히스토그램이 필요할 때, 수집 전략을 스크립트가 아닌 딕셔너리에 정의하기 위해.

주의

라이선스가 필요 없다. 수동 GATHER 호출의 명시적 파라미터가 선호 설정보다 우선한다. METHOD_OPT를 변경하면 히스토그램이 추가/제거되어 계획이 바뀔 수 있다. DBA_TAB_MODIFICATIONS는 flush 후에만 최신이다(자동 flush는 약 15분마다).