Skip to main content

Statistics Management

· One min read
Franck Blettner
Franck Blettner
Creator

Practical PL/SQL recipes for gathering, restoring and managing Oracle optimizer statistics.

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;

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 -
)