How to restore table statistics to an earlier time in Oracle

Roll a table statistics back to how they were at an earlier time, the quickest fix when freshly gathered stats caused a bad plan.

This overwrites the current statistics with the older version and invalidates dependent cursors. Restore only within the stats history retention window (default 31 days).

-- See when this table stats changed (pick a time before the change)
SELECT table_name,
       TO_CHAR(stats_update_time,'YYYY-MM-DD HH24:MI:SS TZH:TZM') AS stats_update_time
FROM   dba_tab_stats_history
WHERE  owner = UPPER('&&owner')
  AND  table_name = UPPER('&&table_name')
ORDER  BY stats_update_time DESC;

BEGIN
  DBMS_STATS.RESTORE_TABLE_STATS(
    ownname         => '&owner',
    tabname         => '&table_name',
    as_of_timestamp => TO_TIMESTAMP_TZ('&restore_time','YYYY-MM-DD HH24:MI:SS TZH:TZM'));
END;
/

-- Verify (read-only)
SELECT num_rows,
       TO_CHAR(last_analyzed,'YYYY-MM-DD HH24:MI') AS last_analyzed
FROM   dba_tables
WHERE  owner = UPPER('&owner')
  AND  table_name = UPPER('&table_name');