Why statistics matter

Oracle's Cost-Based Optimizer (CBO) relies on statistics to generate efficient execution plans. Outdated, missing, or incorrect statistics are one of the top causes of poor query performance.

Recipe #1 - Gather statistics for a table

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => USER,
    tabname => 'EMP',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    cascade => TRUE  -- also gathers index stats
  );
END;

Recipe #2 - Gather statistics for an entire schema

BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS(
    ownname => USER,
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    cascade => TRUE,
    degree => 4  -- use parallel execution
  );
END;

Recipe #3 - Check when statistics were last gathered

Stale statistics are a common cause of bad plans. Find tables with old or missing stats:

SELECT table_name,
       num_rows,
       TO_CHAR(last_analyzed, 'YYYY-MM-DD HH24:MI') AS last_analyzed,
       stale_stats
FROM user_tab_statistics
ORDER BY last_analyzed NULLS FIRST;

Recipe #4 - Find tables with stale statistics

Oracle marks statistics as stale when more than 10% of the rows have changed:

SELECT owner, table_name, object_type, stale_stats,
       TO_CHAR(last_analyzed, 'YYYY-MM-DD HH24:MI') AS last_analyzed
FROM dba_tab_statistics
WHERE stale_stats = 'YES'
  AND owner = USER
ORDER BY last_analyzed;

Recipe #5 - Lock statistics to prevent auto-gathering

Sometimes you want to keep specific statistics frozen, especially for temporary or volatile tables:

-- Lock stats so auto-gather job won't change them
BEGIN
  DBMS_STATS.LOCK_TABLE_STATS(USER, 'MY_STAGING_TABLE');
END;

-- Unlock when you're ready
BEGIN
  DBMS_STATS.UNLOCK_TABLE_STATS(USER, 'MY_STAGING_TABLE');
END;

Recipe #6 - Gather stats on a specific column with histogram

For columns with skewed data distribution, histograms help the optimizer make better decisions:

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname     => USER,
    tabname     => 'ORDERS',
    method_opt  => 'FOR COLUMNS SIZE 254 status',
    cascade     => TRUE
  );
END;

When in doubt, let Oracle's auto-stats job handle it — it runs nightly by default. But after bulk loads, major deletes, or other big changes, always gather statistics manually to avoid bad plans.