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; Advertisements

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 10.00.55.093000000 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,…

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

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…

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 10.13.18.954562000 PM -07:00