time:2011/09/26
Oracle10g增加了一个新的任务,用来自动分析数据库,用于增加CBO执行的正确性。这个任务就是GATHER_STATS_JOB,
任务定义周一到周五的夜晚执行,和周六日全天执行。在oracle11g中job的名称有些不同。
oracle这个自动收集信息的出发点很好,但是,大部分系统晚上的负载也很高,或者执行计划改变,会引起新的性能问题。
而且oracle这个自动收集信息的job非常耗性能。
oracle10g的系统自动job
SQL> select job_name,comments from dba_scheduler_jobs;
JOB_NAME COMMENTS
------------------------------ --------------------------------------------------------------------------------
AUTO_SPACE_ADVISOR_JOB auto space advisor maintenance job
GATHER_STATS_JOB Oracle defined automatic optimizer statistics collection job
FGR$AUTOPURGE_JOB file group auto-purge job
PURGE_LOG purge log job
MGMT_STATS_CONFIG_JOB OCM Statistics collection job.
MGMT_CONFIG_JOB Configuration collection job.
RLM$SCHDNEGACTION
RLM$EVTCLEANUP
8 rows selected
SQL>
oracle11g的自动job
SQL> select job_name,comments from dba_scheduler_jobs;
JOB_NAME COMMENTS
------------------------------ --------------------------------------------------------------------------------
SM$CLEAN_AUTO_SPLIT_MERGE auto clean job for auto split merge
RSE$CLEAN_RECOVERABLE_SCRIPT auto clean job for recoverable script
FGR$AUTOPURGE_JOB file group auto-purge job
BSLN_MAINTAIN_STATS_JOB Oracle defined automatic moving window baseline stati