How to lock or unlock table statistics in Oracle

Lock a table statistics so gathering skips it (useful for volatile or hand-tuned tables), or unlock to resume normal gathering.

While stats are locked, DBMS_STATS will not update them unless forced, so the optimizer keeps using whatever is stored. Unlock when you want normal gathering back.

-- Lock
BEGIN
  DBMS_STATS.LOCK_TABLE_STATS(ownname => '&&owner', tabname => '&&table_name');
END;
/

-- Unlock
BEGIN
  DBMS_STATS.UNLOCK_TABLE_STATS(ownname => '&owner', tabname => '&table_name');
END;
/

-- Verify (read-only): LOCKED or NULL
SELECT stattype_locked
FROM   dba_tab_statistics
WHERE  owner = UPPER('&owner')
  AND  table_name = UPPER('&table_name')
  AND  object_type = 'TABLE';