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 Continue reading How to gather stats without histograms?
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, space_usage_kbytes from v$sysaux_occupants where OCCUPANT_DESC like '%Statistics%'; OCCUPANT_DESC SPACE_USAGE_KBYTES —————————————————————- … Continue reading How to view/change statistics retention and space used to store stats history?
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 = 'SCOTT'; LAST_ANALYZED ——————– 2006-MAY-09 00:00:25 2006-MAY-09 00:00:25 2006-MAY-09 00:00:25 2006-MAY-09 … Continue reading How to restore statistics and view statistics history?
The following article explains global statistics versus histogram with DBMS_STATS http://www.akadia.com/services/ora_gather_statistics.html Continue reading Global Statistics vs. Histograms with DBMS_STATS Package
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') from user_tables where table_name = 'TEST'; TO_CHAR(LAST_ANALYZE ——————– 06-OCT-2010 00:08:05 … Continue reading How to restore table statistics (STATS)?
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 procedure successfully completed. SQL> select dbms_stats.get_stats_history_retention from dual; GET_STATS_HISTORY_RETENTION ————————— … Continue reading How to view and change STATS retention?
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 Continue reading How to find oldest STATS information?