Disable the automatic funciton of collecting statistics in 10G

oracle_ace發表於2008-05-23
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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12361284/viewspace-293581/，如需轉載，請註明出處，否則將追究法律責任。

相關文章