DBAtlas · Database operations atlas

Find stale statistics and set per-table statistics preferences (prefs)

OracleStatistics

Command

-- 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');

What it does

Statistics become stale when the change ratio exceeds STALE_PERCENT (default 10%). Preferences persist per-table sampling, histogram, parallelism and staleness thresholds so the nightly automatic statistics task applies the same rules.

When to use it

When the 10% threshold triggers too late on large tables, when specific columns must always get a histogram, and to define the gathering strategy in the dictionary instead of in scripts.

Caution

No license required. Preferences are overridden by explicit parameters in a manual GATHER call. Changing METHOD_OPT adds or removes histograms and can change plans. DBA_TAB_MODIFICATIONS is current only after a flush (automatic flush roughly every 15 minutes).

Open in DBAtlas →

Related