Oracle10g automatic collection of statistics-Automatic analyze

Starting from Oracle Database 10 Gb, Oracle creates a scheduled task named gather_stats_job by default after the database is created, which is used to automatically collect CBO statistics.

This automatic task is enabled by default between-on the workday and all day on the weekend. Call dbms_stats.gather_database_stats_job_proc to collect statistics. This process first detects the missing and obsolete objects of statistics. Then, determine the priority and start the statistics.

You can query the running status of the job as follows:

Select * From dba_scheduler_jobs where job_name = 'gather _ stats_job'

 

In fact, there is also an auto_space_advisor_job in the job that runs at 10: 00:

SQL> select job_name, last_start_date from dba_scheduler_jobs;

 

Job_name last_start_date
----------------------------------------------------------------------
Auto_space_advisor_job 04-dec-07 10.00.00.692269 PM + 08:00
Gather_stats_job 04-dec-07 10.00.00.701152 PM + 08:00
FGR $ autopurge_job
Purge_log 05-dec-07 03.00.00.169059 am PRC

 

However, this automation function has affected the normal operation of many systems. At am, most production systems are not idle.
Automatic analysis may lead to extremely serious competition for latches, which may lead to database hang or crash.

We recommend that you disable the automatic statistics collection function:

Exec dbms_scheduler.disable ('gather _ stats_job ');

 

Automation is always accompanied by serious risks!

The above is the reprinted gaiguoqiang's opinion ---- http://www.eygle.com/archives/2007/12/dbms_scherduler_disable.html
My dB has not disabled this function, and it is used very well. I am not planning to disable it for the time being. If it does affect usage that day, I will decide whether to disable it or not.
To disable and enable the automatic collection function, you can use either of the following methods:
Method 1:
Exec dbms_scheduler.disable ('sys. gather_stats_job ');
Exec dbms_scheduler.enable ('sys. gather_stats_job ');
Method 2:
Alter system set "_ optimizer_autostats_job" = false scope = spfile;
Alter system set "_ optimizer_autostats_job" = true scope = spfile;
Pfile can directly modify the initialization parameter file and restart the database.