Automatic Collection of Oracle10g statistics

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. Theoretically, more than 10% of data changes in a table are collected by this job.

 

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 ');

1. query a job
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

----------------------------------------------------------------------------

There are two methods:
Method 1:
Sysdba Logon
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
Then restart the database.