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
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,…
Shows in this scenrio there are backup of stats that exists for SCOTT.TEST table SQL> select * FROM DBA_TAB_STATS_HISTORY where owner = 'SCOTT' and table_name = 'TEST'; no rows selected Display current timestamp of last time when the table and index was analyzed SQL> select last_analyzed from dba_indexes where table_name = 'TEST' and table_owner =…
The following article explains global statistics versus histogram with DBMS_STATS http://www.akadia.com/services/ora_gather_statistics.html
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')…
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…
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
