DBMS STATS

From Oracle FAQ
Jump to: navigation, search

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

Contents

[edit] Functions and procedures

[edit] dbms_stats.gather_table_stats

[edit] dbms_stats.gather_schema_stats

[edit] dbms_stats.gather_system_stats

[edit] dbms_stats.create_stat_table

[edit] dbms_stats.export_*_stats

[edit] dbms_stats.import_*_stats

[edit] 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.

[edit] 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');