Oracle optimizer statistics cheat sheet: DBMS_STATS gather, stale checks, locks, preferences and restore

#When you need this

A query that was fast yesterday is slow today, the execution plan changed, and the AWR report shows the same SQL with a new plan hash value. Very often the cause is optimizer statistics: missing, stale, locked, or freshly gathered at the wrong moment, for example right after a truncate and before the reload.

Run the queries in this sheet as a user with SELECT ANY DICTIONARY, or as a DBA. The DBMS_STATS calls need ANALYZE ANY for other schemas.

#Find the problem

#Stale or missing statistics

Monitoring counters are flushed to the dictionary only now and then. Flush them first so STALE_STATS is current:

EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

SELECT owner, table_name, partition_name, num_rows, last_analyzed, stale_stats, stattype_locked
FROM   dba_tab_statistics
WHERE  owner = 'SALES'
AND   (stale_stats = 'YES' OR last_analyzed IS NULL)
ORDER BY last_analyzed NULLS FIRST;

How much has changed since the last gather:

SELECT table_owner, table_name, partition_name, inserts, updates, deletes, truncated, timestamp
FROM   dba_tab_modifications
WHERE  table_owner = 'SALES'
ORDER BY inserts + updates + deletes DESC;

#Locked statistics

SELECT owner, table_name, stattype_locked
FROM   dba_tab_statistics
WHERE  stattype_locked IS NOT NULL
AND    owner NOT IN ('SYS', 'SYSTEM');

ALL or DATA means nobody can gather on that table, including the automatic job. Gathering explicitly then fails with ORA-20005: object statistics are locked.

#Index and column statistics for one table

SELECT index_name, num_rows, distinct_keys, clustering_factor, last_analyzed, stale_stats
FROM   dba_ind_statistics
WHERE  owner = 'SALES' AND table_name = 'ORDERS';

SELECT column_name, num_distinct, num_nulls, histogram, num_buckets, last_analyzed
FROM   dba_tab_col_statistics
WHERE  owner = 'SALES' AND table_name = 'ORDERS'
ORDER BY column_name;

#What gathered statistics recently

SELECT operation, target, start_time, end_time, status
FROM   dba_optstat_operations
WHERE  start_time > SYSTIMESTAMP - INTERVAL '2' DAY
ORDER BY start_time DESC;

This is the quickest way to prove "the stats job ran on ORDERS at 22:05 and the plan changed at 22:10".

#Symptom, cause and fix

#Gather statistics

#One table, the safe default

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => 'SALES',
    tabname          => 'ORDERS',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    cascade          => TRUE,
    degree           => 4,
    no_invalidate    => DBMS_STATS.AUTO_INVALIDATE);
END;
/

Leave estimate_percent at AUTO_SAMPLE_SIZE. It is both fast and accurate on current releases, and it is needed for incremental and approximate NDV statistics.

#Only what is stale in a schema

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SALES', options => 'GATHER STALE');
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SALES', options => 'GATHER EMPTY');  -- objects with no stats

#One partition after a load

EXEC DBMS_STATS.GATHER_TABLE_STATS('SALES', 'ORDERS', partname => 'P2026_10', granularity => 'PARTITION');

#Dictionary and fixed objects

Gather fixed object stats once under a representative workload, and again after an upgrade or a big change to memory settings:

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

Warning

A gather invalidates dependent cursors (immediately with no_invalidate => FALSE, gradually with the default). On a busy OLTP system, gathering a hot table at peak time can trigger a wave of hard parses and plan changes. Gather in a quiet window or use pending statistics.

#Test before you publish: pending statistics

-- 1. New stats for this table go to pending, not live
EXEC DBMS_STATS.SET_TABLE_PREFS('SALES', 'ORDERS', 'PUBLISH', 'FALSE');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SALES', 'ORDERS');

-- 2. Try them in your session only
ALTER SESSION SET optimizer_use_pending_statistics = TRUE;
-- run the problem query, check the plan with DBMS_XPLAN.DISPLAY_CURSOR

-- 3a. Happy: publish
EXEC DBMS_STATS.PUBLISH_PENDING_STATS('SALES', 'ORDERS');
-- 3b. Not happy: throw them away
EXEC DBMS_STATS.DELETE_PENDING_STATS('SALES', 'ORDERS');

-- 4. Back to normal publishing
EXEC DBMS_STATS.SET_TABLE_PREFS('SALES', 'ORDERS', 'PUBLISH', 'TRUE');

#Restore previous statistics

Oracle keeps a history of statistics, 31 days by default. Check the retention and what is available:

SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION    AS retention_days,
       DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY AS oldest_available
FROM   dual;

SELECT table_name, stats_update_time
FROM   dba_tab_stats_history
WHERE  owner = 'SALES' AND table_name = 'ORDERS'
ORDER BY stats_update_time DESC;

Put back the statistics as they were before the bad gather:

BEGIN
  DBMS_STATS.RESTORE_TABLE_STATS(
    ownname         => 'SALES',
    tabname         => 'ORDERS',
    as_of_timestamp => TIMESTAMP '2026-11-01 21:00:00',
    no_invalidate   => FALSE);
END;
/

no_invalidate => FALSE forces dependent cursors to re-parse now, so the old plan comes back straight away. Lock the stats afterwards if you need the auto job to leave the table alone while you find the root cause.

Tip

RESTORE_SCHEMA_STATS does the same for a whole schema. If the table's statistics are locked, the restore fails unless you add force => TRUE. History is purged after the retention period, so act within the window.

#Lock and unlock

EXEC DBMS_STATS.LOCK_TABLE_STATS('SALES', 'ORDERS_STAGE');
EXEC DBMS_STATS.UNLOCK_TABLE_STATS('SALES', 'ORDERS_STAGE');
EXEC DBMS_STATS.LOCK_SCHEMA_STATS('STAGING');

Locking suits staging and volatile tables that are empty when the job runs and full when queries run. Gather once while loaded, then lock.

#Table and global preferences

-- Read the effective value for a table
SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT', 'SALES', 'ORDERS') FROM dual;

-- Gather when 2% of rows change instead of 10%
EXEC DBMS_STATS.SET_TABLE_PREFS('SALES', 'ORDERS', 'STALE_PERCENT', '2');

-- Incremental stats for a large partitioned table
EXEC DBMS_STATS.SET_TABLE_PREFS('SALES', 'ORDERS', 'INCREMENTAL', 'TRUE');

-- Everything set at table level
SELECT preference_name, preference_value
FROM   dba_tab_stat_prefs
WHERE  owner = 'SALES' AND table_name = 'ORDERS';

SET_GLOBAL_PREFS changes the default for every object without a table-level preference. Change global values only with a reason you have written down.

#Copy statistics between databases

Useful to reproduce a production plan in test:

EXEC DBMS_STATS.CREATE_STAT_TABLE('DBA_ADMIN', 'STATS_EXPORT');
EXEC DBMS_STATS.EXPORT_TABLE_STATS('SALES', 'ORDERS', stattab => 'STATS_EXPORT', statown => 'DBA_ADMIN');
-- move DBA_ADMIN.STATS_EXPORT with Data Pump, then on the target:
EXEC DBMS_STATS.IMPORT_TABLE_STATS('SALES', 'ORDERS', stattab => 'STATS_EXPORT', statown => 'DBA_ADMIN');

#The automatic statistics job

SELECT client_name, status FROM dba_autotask_client
WHERE  client_name = 'auto optimizer stats collection';

SELECT window_name, job_status, job_start_time, job_duration
FROM   dba_autotask_job_history
WHERE  client_name = 'auto optimizer stats collection'
ORDER BY job_start_time DESC FETCH FIRST 10 ROWS ONLY;

Caution

Don't disable auto optimizer stats collection to "stop plans changing". Statistics then drift for every table and plans get worse over weeks. Fix the specific tables with preferences, locks or pending stats instead. On Autonomous Database the automatic job is managed by Oracle; use table preferences rather than trying to change the job.

#Prevent it next time

• After every bulk load or truncate-and-reload, gather stats in the load job itself, not hours later.
• Lock statistics on staging and volatile tables at a representative size.
• Set INCREMENTAL on large partitioned tables so the nightly job finishes inside its window.
• Check dba_optstat_operations first whenever a plan change is reported. It tells you in seconds whether new statistics were the trigger.
• Keep the stats history retention at least as long as your change freeze or release cycle, so you can always restore.