oracle10g如何关闭和打开自动收集统计信息

oracle 10g如何关闭和打开自动收集统计信息

 

一、ORACLE10g自动收集统计信息--自动analyze 

 

从Oracle Database 10g开始，Oracle在建库后就默认创建了一个名为GATHER_STATS_JOB的定时任务，用于自动收集CBO的统计信息。 

 

这个自动任务默认情况下在工作日晚上10：00-6：00和周末全天开启。调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集统计信息。该过程首先检测统计信息缺失和陈旧的对象。然后确定优先级，再开始进行统计信息。 

  www.2cto.com  

可以通过以下查询这个JOB的运行情况： 

 

    select * from Dba_Scheduler_Jobs where JOB_NAME =&＃39;GATHER_STATS_JOB&＃39; 

 

其实同在10点运行的Job还有一个AUTO_SPACE_ADVISOR_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             www.2cto.com  

 

    PURGE_LOG                      05-DEC-07 03.00.00.169059 AM PRC 

 

然而这个自动化功能已经影响了很多系统的正常运行，晚上10点对于大部分生产系统也并非空闲时段。 

 

而自动分析可能导致极为严重的闩锁竞争，进而可能导致数据库Hang或者Crash。 

 

所以建议最好关闭这个自动统计信息收集功能： 

 

    exec DBMS_SCHEDULER.DISABLE(&＃39;GATHER_STATS_JOB&＃39;); 

 

自动化永远而严重的隐患相伴随！ 

 

关闭及开启自动搜集功能，有两种方法，分别如下： 

 

方法一： 

 

exec dbms_scheduler.disable(&＃39;SYS.GATHER_STATS_JOB&＃39;); 

  www.2cto.com  

exec dbms_scheduler.enable(&＃39;SYS.GATHER_STATS_JOB&＃39;); 

 

方法二： 

 

alter system set "_optimizer_autostats_job"=false scope=spfile; 

 

alter system set "_optimizer_autostats_job"=true scope=spfile; 

 

Pfile可以直接修改初始化参数文件，重新启动数据库。 

 

二、AWR默认通过MMON及MMNL进程来每小自动运行一次，为了节省空间，采集的数据在 7 天后自动清除。 

 

快照频率和保留时间都可以由用户修改。要查看当前的设置，您可以使用下面的语句： 

  www.2cto.com  

select snap_interval, retention 

 

from dba_hist_wr_control; 

 

SNAP_INTERVAL       RETENTION 

 

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

  +00000 01:00:00.0   +00007 00:00:00.0 

 

这些 SQL 语句显示快照每小时采集一次，采集的数据保留 7 天。要修改设置—例如，快照时间间隔为 20 分钟，保留时间为两天—您可以发出以下命令。参数以分钟为单位。 

 

begin 

 

   dbms_workload_repository.modify_snapshot_settings ( 

 

      interval => 20, 

 

      retention => 2*24*60 

  www.2cto.com  

   );end; 

 

AWR 使用几个表来存储采集的统计数据，所有的表都存储在新的名称为 SYSAUX 的特定表空间中的 SYS 模式下，并且以 WRM$_* 和 WRH$_* 的格式命名。前一种类型存储元数据信息（如检查的数据库和采集的快照），后一种类型保存实际采集的统计数据。（您可能已经猜到，H 代表“历史数据 (historical)”而 M 代表“元数据 (metadata)”。）在这些表上构建了几种带前缀 DBA_HIST_ 的视图，这些视图可以用来编写您自己的性能诊断工具。视图的名称直接与表相关；例如，视图 DBA_HIST_SYSMETRIC_SUMMARY 是在WRH$_SYSMETRIC_SUMMARY 表上构建的。 

  www.2cto.com  

您的处理计划一般是有规律的，并且通常基于您对各种事件的了解和您处理它们的经验。现在设想相同的事情由一个引擎来完成，这个引擎采集量度并根据预先确定的逻辑来推出可能的计划。您的工作不就变得更轻松了吗？ 现在在 Oracle Database 10g中推出的这个引擎称为自动数据库诊断监控程序 (ADDM)。为了作出决策，ADDM 使用了由 AWR 采集的数据。 

 

在 AWR 进行的每一次快照采集之后，调用 ADDM 来检查量度并生成建议。因此，实际上您拥有了一个一天二十四小时工作的自动数据库管理员，它主动地分析数据并生成建议，从而把您解放出来，使您能够关注更具有战略意义的问题。   www.2cto.com  

 

快照默认是自动采集的，但您也可以按需要采集它们。所有的 AWR 功能都在程序包 DBMS_WORKLOAD_REPOSITORY 中实施。要采集一次快照，只需发出下面的命令： 

 

execute dbms_workload_repository.create_snapshot它立即采集一次快照，快照被记录在表 WRM$_SNAPSHOT 中。采集的量度是针对 TYPICAL 级别的。如果您想采集更详细的统计数据，您可以在上面的过程中将参数 FLUSH_LEVEL 设置为 ALL。统计数据自动删除，但也可以通过调用过程 drop_snapshot_range() 来手动删除。