Automatic Collection of Oracle10g statistics

Source: Internet
Author: User

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 PM + 08:00
Gather_stats_job 04-dec-07 PM + 08:00
FGR $ autopurge_job
Purge_log 05-dec-07 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.


Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.