How to gather stats without histograms?

When passing method_opt as 'FOR ALL COLUMNS SIZE 1', it means no histogram is collected for columns on the table(s). exec dbms_stats.gather_schema_stats('SCOTT', method_opt=>'FOR ALL COLUMNS SIZE 1'); One can find the default value by executing as follows: select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual;


How to view/change statistics retention and space used to store stats history?

See the oldest statistics history available SQL> SELECT DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY FROM DUAL; GET_STATS_HISTORY_AVAILABILITY --------------------------------------------------------------------------- 25-OCT-11 PM -07:00 See the stats retention SQL> select dbms_stats.get_stats_history_retention from dual; GET_STATS_HISTORY_RETENTION --------------------------- 31 Modify retention SQL> exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(60); Space currently used to store statistics in SYSAUX in KBytes, so increasing the retention would affect it's size SQL> select occupant_desc,... Continue Reading →

How to restore table statistics (STATS)?

When using DBMS_STATS to gather table statics Oracle backups up the statistics before overwriting it. The following example shows how dbms_stats.gather_*_stats works and how to restore statistics Example: -- stats was gathered on a table SQL> exec dbms_Stats.gather_table_stats('SCOTT', 'TEST'); PL/SQL procedure successfully completed. -- verified stats by checking last_analyzed date SQL> select to_char(last_analyzed, 'DD-MON-YYYY HH24:MI:SS')... Continue Reading →

How to view and change STATS retention?

Way of changing retention SQL> select dbms_stats.get_stats_history_retention from dual; GET_STATS_HISTORY_RETENTION --------------------------- 31 -- value passed is in days -- If 0 is passed - old statistics are never saved. The automatic purge will delete all statistics history -- If 1 is passed - statistics history is never purged by automatic purge. SQL> exec dbms_stats.alter_stats_history_retention(30); PL/SQL... Continue Reading →

How to find oldest STATS information?

Using dbms_stats.get_stats_history_availability one can find date of oldest STATS information. STATS older than this timestamp cannot be restored. SQL> select dbms_stats.get_stats_history_availability from dual; GET_STATS_HISTORY_AVAILABILITY --------------------------------------------------------------------------- 09-FEB-10 PM -07:00


DBA_TAB_MODIFICATIONS view contains tables that were modified since last time statistics was gathered on the table. It contains the number of inserts, deletes and updates have occurred on the table. In 10g this is turned on automatically when the statistics_level is typical/all. By calling DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO one can flush monitoring information from memory into dictionary. When... Continue Reading →

Blog at

Up ↑