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, space_usage_kbytes from v$sysaux_occupants where OCCUPANT_DESC like '%Statistics%';

OCCUPANT_DESC SPACE_USAGE_KBYTES
—————————————————————- ——————
Server Manageability – Optimizer Statistics History 17920

How to restore statistics and view statistics 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 00:00:25
2006-MAY-09 00:00:25
2006-MAY-09 00:00:25
2006-MAY-09 00:00:25

SQL> select last_analyzed from dba_tables where table_name = 'TEST' and owner = 'SCOTT';

LAST_ANALYZED
——————–
2011-JUL-21 00:20:34

Gather/Update stats on the table
SQL> exec dbms_stats.gather_table_stats('SCOTT', 'TEST', cascade=>true);

Check stats on the table by checking last_analyzed date, shows date updated
SQL> select last_analyzed from dba_indexes where table_name = 'TEST' and table_owner = 'SCOTT';

LAST_ANALYZED
——————–
2011-NOV-26 10:38:57
2011-NOV-26 10:39:14
2011-NOV-26 10:39:18
2011-NOV-26 10:39:19
2011-NOV-26 10:39:28
2011-NOV-26 10:39:28
2011-NOV-26 10:39:28

SQL> select last_analyzed from dba_tables where table_name = 'TEST' and owner = 'SCOTT';

LAST_ANALYZED
——————–
2011-NOV-26 10:38:55

When stats are gathered dbms_stats backups the stats are backup and viewed in DB_TAB_STATS_HISTORY which can be used to restore stats
SQL> select * FROM DBA_TAB_STATS_HISTORY where owner = 'SCOTT' and table_name = 'TEST';

OWNER TABLE_NAME STATS_UPDATE_TIME
————— —————————— —————————-
SCOTT TEST 11-11-26 10:38:56,021074 -07

Restore stats as of yesterday i.e. to time before the stats were run
SQL> exec dbms_stats.restore_table_stats('SCOTT', 'TEST', TO_DATE('26-NOV-2011 11:00', 'DD-MON-YYYY HH24:MI') – 1));

Check last analyzed date it indicates the stats were restored back
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 00:00:25
2006-MAY-09 00:00:25
2006-MAY-09 00:00:25
2006-MAY-09 00:00:25

SQL> select last_analyzed from dba_tables where table_name = 'TEST' and owner = 'SCOTT';

LAST_ANALYZED
——————–
2011-JUL-21 00:20:34

dbms_stats.restore_table_stats backs up the stats again to restore the stats that was changed
SQL> select * FROM DBA_TAB_STATS_HISTORY where owner = 'SCOTT' and table_name = 'TEST';

OWNER TABLE_NAME STATS_UPDATE_TIME
————— —————————— —————————-
SCOTT TEST 11-11-26 11:53:24,517857 -07
SCOTT TEST 11-11-26 10:38:56,021074 -07

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') from user_tables where table_name = 'TEST';

TO_CHAR(LAST_ANALYZE
——————–
06-OCT-2010 00:08:05

— Some updates/inserts/deletes occurred on the table, gather table stats again
SQL> exec dbms_Stats.gather_table_stats('SCOTT', 'TEST');

— show 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:48

— Check historical stats present for the table
SQL> select table_name, to_char(stats_update_time, 'DD-MON-YYYY HH24:MI:SS') from dba_tab_stats_history where owner = 'SCOTT';

TABLE_NAME TO_CHAR(STATS_UPDATE
—————————— ——————–
TEST 06-OCT-2010 00:08:05
TEST 06-OCT-2010 00:08:48

— restore stats to time it was before
SQL> exec DBMS_STATS.RESTORE_TABLE_STATS (ownname=>'SCOTT', tabname=>'TEST', as_of_timestamp=>TO_DATE('06-OCT-2010 00:08:48', 'DD-MON-YYYY HH24:MI:SS'));

PL/SQL procedure successfully completed.

— verify stats was restored by checking 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

See the oldest STATs data available
How to change STATS retention

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 procedure successfully completed.

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
—————————
30

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