DBAtlas · Атлас эксплуатации баз данных

Найти устаревшую статистику и задать параметры сбора статистики для таблиц (prefs)

OracleСтатистика

Команда

-- 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%). Preferences сохраняют для таблицы параметры выборки, гистограмм, параллелизма и порогов устаревания, чтобы ночная автоматическая задача сбора статистики применяла те же правила.

Когда использовать

Когда порог 10% на больших таблицах срабатывает слишком поздно, когда для определённых столбцов всегда нужна гистограмма, и чтобы задать стратегию сбора в словаре, а не в скриптах.

Внимание

Лицензия не требуется. Явные параметры в ручном вызове GATHER переопределяют preferences. Изменение METHOD_OPT добавляет или удаляет гистограммы и может изменить планы. DBA_TAB_MODIFICATIONS актуально только после flush (автоматический flush — примерно раз в 15 минут).

Открыть в DBAtlas →

Связанные материалы