oracle : version 10 or later
Oracle 10g includes a new feature "automatic optimizer statistics collection"
that eliminates the manual task of gathering optimizer statistics.
You can check if "automatic optimizer statistics collection" is enabled :
select * from dba_scheduler_jobs where job_name = 'GATHER_STATS_JOB';
A query, that has run for months , gets slow.
It is useful to check when the statistics have run for the table or tables of the query :
select last_analyzed from dba_tables where table_name='TABLE1';
select table_name, stats_update_time from dba_tab_stats_history where table_name='TABLE1';
For example, if you want to restore the old statistics
select last_analyzed from dba_tables where table_name='TABLE1';
LAST_ANALYZED
01-09-11 04:09:53
select table_name,stats_update_time from dba_tab_stats_history where table_name='TABLE1';
TABLE1 27-AUG-11 04.14.07.990109 AM +01:00
TABLE1 01-SEP-11 04.09.53.489172 AM +01:00
execute dbms_stats.restore_table_stats ('SCHEMA', 'TABLE1' , '27-AUG-11 04.14.07.990109 AM +01:00')
PL/SQL procedure successfully completed.
select last_analyzed from dba_tables where table_name='TABLE1';
LAST_ANALYZED
27-08-11 04:14:07
If you want to disable "automatic optimizer statistics" for a table :
select last_analyzed, stattype_locked from dba_tab_statistics where table_name='TABLE1';
LAST_ANAL STATT
09-SEP-11
execute dbms_stats.lock_table_stats('SCHEMA', 'TABLE1')
PL/SQL procedure successfully completed.
select last_analyzed, stattype_locked from dba_tab_statistics where table_name='TABLE1';
LAST_ANAL STATT
09-SEP-11 ALL