Stale statistics are one of the most common causes of a sudden bad execution plan, for example after a large data load or a purge. These are the two calls we use most.

1. Gather statistics for a table

BEGIN
 DBMS_STATS.GATHER_TABLE_STATS (ownname => '<OWNER>' , tabname => '<TABLE_NAME>',
  cascade => true, estimate_percent => 15,method_opt=>'for all indexed columns size 1', granularity => 'ALL', degree => 1);
END;
  • cascade => true also gathers statistics on the table’s indexes.
  • estimate_percent => 15 samples 15 % of the rows.
  • granularity => 'ALL' covers the table, its partitions and sub-partitions.

2. Gather statistics for a schema

exec dbms_stats.gather_schema_stats( -
    ownname          => '<SCHEMA_NAME>', -
    options          => 'GATHER AUTO', -
    estimate_percent => dbms_stats.auto_sample_size, -
    method_opt       => 'for all columns size repeat', -
    degree           => 15 -
)
  • options => 'GATHER AUTO' lets Oracle decide which objects need new statistics (missing or stale).
  • method_opt => 'for all columns size repeat' keeps the histograms that already exist.
  • degree => 15 runs the collection in parallel — adjust it to the CPUs available.