Purge and regather all optimizer stats with:
1 | exec dbms_stats.delete_database_stats |
2 | exec dbms_stats.delete_system_stats |
3 | exec dbms_scheduler.run_job( 'GATHER_STATS_JOB' ) |
That works provided statistics level has not been changed to none
1 | show parameter statistics_level |
You can gather more detailed system stats with:
1 | exec dbms_stats.gather_system_stats( 'START' ) |
2 | exec dbms_stats.gather_system_stats( 'STOP' ) |
You can see the system stats with:
1 | set pages 9999 lines 112 |
3 | select * from sys.aux_stats$; |
You can check automatic stats gathering job is scheduled with:
1 | 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:
1 | select status, max_duration_last_7_days from dba_autotask_client where client_name = 'auto optimizer stats collection' ; |
And to run it in 11g:
1 | exec dbms_stats.gather_database_stats_job_proc |
Leave a Reply