Andrew Fraser DBA

Purge and regather all optimizer stats

Purge and regather all optimizer stats with:

exec dbms_stats.delete_database_stats
exec dbms_stats.delete_system_stats
exec dbms_scheduler.run_job('GATHER_STATS_JOB')

That works provided statistics level has not been changed to none

show parameter statistics_level

You can gather more detailed system stats with:

exec dbms_stats.gather_system_stats('START')
exec dbms_stats.gather_system_stats('STOP')

You can see the system stats with:

set pages 9999 lines 112
col pval2 form a20
select * from sys.aux_stats$;

You can check automatic stats gathering job is scheduled with:

select state, last_start_date from dba_scheduler_jobs where job_name = 'GATHER_STATS_JOB';

This has changed for 11g.
To check if it is scheduled in 11g:

select status, max_duration_last_7_days from dba_autotask_client where client_name = 'auto optimizer stats collection' ;

And to run it in 11g:

exec dbms_stats.gather_database_stats_job_proc