DBMS STATS

DBMS_STATS is a PL/SQL packages that can be used to gather opimizer statistics.

Functions and procedures

dbms_stats.restore_*_stats

Statistics gathered with the GATHER_*_STATS procedures are preserved for upto 30 days by Oracle automatically.

To restore these statistics, use the equivalent restore_*_stats procedures, like RESTORE_TABLE_STATS, RESTORE_SCHEMA_STATS, etc.

Select from DBA_OPTSTAT_OPERATIONS to see the history of statistics operations performed. Use views {USER|ALL|DBA}_TAB_STATS_HISTORY to see all statistics modifications performed.

Examples

exec dbms_stats.gather_table_stats(USER, 'T1', cascade => TRUE);

begin
  dbms_stats.gather_table_stats(
    ownname=>'SCOTT', 
    tabname=>'EMP',
    estimate_percent=>dbms_stats.auto_sample_size,
    cascade=>true);
end;
/

dbms_stats.gather_schema_stats(ownname=>user, cascade=>true, -
                               method_opt=>'FOR ALL INDEXED COLUMNS SIZE 254');