After created oracle 10g database,there is an existing job named gather_stats_job which is used to collect the statistics in database level automatically. In default, this job will be actived and executed during the working time from 10:00PM to 6:00AM every Monday to Friday also included the full daytime in weekend. This job fulfil the task by invoked the store procedure named dbms_stats.gather_database_stats_job_proc to collect the statistics. Collecting statistics will bring on some of the contention of internal latch, and collecting statistics inadequately will cause many unexpected problem. So we'd better disable this function in Oracle 10g after database installation.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
SQL> select job_name,state,last_start_date from dba_scheduler_jobs;
JOB_NAME STATE
------------------------------ ---------------
LAST_START_DATE
---------------------------------------------------------------------------
AUTO_SPACE_ADVISOR_JOB SCHEDULED
22-MAY-08 10.00.02.500000 PM -04:00
GATHER_STATS_JOB SCHEDULED
22-MAY-08 10.00.02.500000 PM -04:00
FGR$AUTOPURGE_JOB DISABLED
PURGE_LOG SCHEDULED
22-MAY-08 03.00.00.109000 AM US/EASTERN
SQL> exec dbms_scheduler.disable('GATHER_STATS_JOB');
PL/SQL procedure successfully completed.
SQL> select job_name,state,last_start_date from dba_scheduler_jobs;
JOB_NAME STATE
------------------------------ ---------------
LAST_START_DATE
---------------------------------------------------------------------------
AUTO_SPACE_ADVISOR_JOB SCHEDULED
22-MAY-08 10.00.02.500000 PM -04:00
GATHER_STATS_JOB DISABLED
22-MAY-08 10.00.02.500000 PM -04:00
FGR$AUTOPURGE_JOB DISABLED
PURGE_LOG SCHEDULED
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
SQL> select job_name,state,last_start_date from dba_scheduler_jobs;
JOB_NAME STATE
------------------------------ ---------------
LAST_START_DATE
---------------------------------------------------------------------------
AUTO_SPACE_ADVISOR_JOB SCHEDULED
22-MAY-08 10.00.02.500000 PM -04:00
GATHER_STATS_JOB SCHEDULED
22-MAY-08 10.00.02.500000 PM -04:00
FGR$AUTOPURGE_JOB DISABLED
PURGE_LOG SCHEDULED
22-MAY-08 03.00.00.109000 AM US/EASTERN
SQL> exec dbms_scheduler.disable('GATHER_STATS_JOB');
PL/SQL procedure successfully completed.
SQL> select job_name,state,last_start_date from dba_scheduler_jobs;
JOB_NAME STATE
------------------------------ ---------------
LAST_START_DATE
---------------------------------------------------------------------------
AUTO_SPACE_ADVISOR_JOB SCHEDULED
22-MAY-08 10.00.02.500000 PM -04:00
GATHER_STATS_JOB DISABLED
22-MAY-08 10.00.02.500000 PM -04:00
FGR$AUTOPURGE_JOB DISABLED
PURGE_LOG SCHEDULED
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12361284/viewspace-293581/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12361284/viewspace-293581/