disable GATHER_STATS_JOB

前两天把自动收集统计信息那个job（GATHER_STATS_JOB）运行出错的问题找出来之后，马上开开心心手工执行了一遍：

exec dbms_stats.gather_database_stats_job_proc

结果第二天，一个平常只要几秒钟的sql跑了大半天也出不来了，马上意识到分析之后改变了执行计划。于是把sql涉及到的那几个表的统计信息删了：

analyze table TABLE_NAME delete statistics;

杀掉再重启程序，好了。

但是还要把那个job停掉。根据metalink上说，If （STATISTICS_LEVEL）set to BASIC, the GATHER_STATS_JOB will run only on empty objects.

但是，With STATISTICS_LEVEL= BASIC, the MONITORING flag is disabled automatically

所以，最好么让它MONITORING，把job给remove掉或者disable掉。dba_scheduler_jobs表中有这么几个字段：

ENABLED	VARCHAR2(5)		Indicates whether the job is enabled (TRUE) or not (FALSE)
AUTO_DROP	VARCHAR2(5)		Indicates whether the job will be dropped when it has completed (TRUE) or not (FALSE)
RESTARTABLE	VARCHAR2(5)		Indicates whether the job can be restarted (TRUE) or not (FALSE)
STATE	VARCHAR2(15)		Current state of the job: DISABLED RETRY SCHEDULED SCHEDULED RUNNING COMPLETED BROKEN FAILED REMOTE SUCCEEDED

SQL> select ENABLED,AUTO_DROP,RESTARTABLE,STATE from dba_scheduler_jobs where job_name='GATHER_STATS_JOB';

ENABL AUTO_ RESTA STATE
----- ----- ----- ---------------
TRUE FALSE TRUE SCHEDULED

但是我没找到如何把这些值改掉。

于是，

SQL> show parameter statistics_level

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL

SQL> select count(*) from dba_tables where monitoring='YES';

COUNT(*)
----------
995

SQL> alter system set statistics_level=BASIC;

System altered.

SQL> select count(*) from dba_tables where monitoring='YES';

no rows selected

今天我用“dba_tab_modifications”关键字搜到一篇文章，我很伤心地发现，这里讲了如何“将参数 STATISTIC_LEVEL 设为 TYPICAL 却不希望自动收集统计信息”的方法，而且，更保险的办法是返回到原有的统计信息（还好我们的数据库以前跑的是从来没分析过的），ft，前两天怎么没搜到。

于是，

SQL> alter system set statistics_level=TYPICAL;

System altered.

SQL> select count(*) from dba_tables where monitoring='YES';

COUNT(*)
----------
995

SQL> select ENABLED,AUTO_DROP,RESTARTABLE,STATE from dba_scheduler_jobs where job_name='GATHER_STATS_JOB';

ENABL AUTO_ RESTA STATE
----- ----- ----- ---------------
TRUE FALSE TRUE SCHEDULED

SQL> BEGIN
2 DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
3 END;
4 /

PL/SQL procedure successfully completed.

SQL> select ENABLED,AUTO_DROP,RESTARTABLE,STATE from dba_scheduler_jobs where job_name='GATHER_STATS_JOB';

ENABL AUTO_ RESTA STATE
----- ----- ----- ---------------
FALSE FALSE TRUE DISABLED

可见，用正确的关键字搜索是多么重要。贴一下这篇文章：

http://www.oracle.com/technology/global/cn/pub/articles/10gdba/week20_10gdba.html

您的统计信息是否陈旧？不要再等待了

大部分都道，Oracle 10g 版本最后不再支持（而不是反对）基于规则的优化器 (RBO)。考虑到这种期待已久的发展即将发生，很多 Oracle9i 数据库商店升级到基于开销的优化器 (CBO)，以便进入支持循环并利用查询重写和分区修剪等高级特性。但是，问题在于统计信息 — 或者说在于缺少统计信息。

因为 CBO 依靠准确的（或者说比较准确的）统计信息来产生优化的执行路径，所以 DBA 需要确保定期收集统计信息，创建另一个执行核对清单。在 10g 之前，这一过程可能由于多种原因而徒劳无功。这种困难导致产生了一种理论，即 CBO 具有“自己的思想” — 这意味着会有随意更改执行路径的行为！

在 10g 中已经消除了很多这类的顾虑，它能够自动收集统计信息。在 Oracle9i 中，您可以通过打开表监视选项 (ALTER TABLE ...MONITORING) 然后检查这些表的视图 DBA_TAB_MODIFICATIONS，检查表中的数据是否已明显更改过。

在 10g 中，已不再使用 MONITORING 语句了。而通过设置初始化参数 STATISTIC_LEVEL 为 TYPICAL 或 ALL，就可以自动收集统计信息。（默认值为 TYPICAL，因此可以随即启用自动收集统计信息的功能。）Oracle 数据库 10g 具有一个预定义的调度程序（您在第 19 周中已经学习过调度程序）作业，名称为 GATHER_STATS_JOB，它由 STATISTIC_LEVEL 参数的适当数值所激活。

统计信息的收集是资源相当密集的工作，因此您可能希望确保它不影响数据库的正常操作。在 10g 中，您可以使这一工作自动完成：可对一个名为 AUTO_TASK_CONSUMER_GROUP 的特定资源用户组进行预定义，用于自动执行一些任务，比如收集统计信息。该用户组确保这些统计信息收集作业的优先权低于默认用户组，因此减少或消除了自动化任务占用整个机器的风险。

如果希望将参数 STATISTIC_LEVEL 设为 TYPICAL 却不希望自动收集统计信息时该怎么办？很简单。只需使用以下语句来禁用调度程序作业即可：
BEGIN
DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;

那么您为什么要这样做呢？有很多原因 — 其中一个原因是，虽然表中大部分行发生了变化，但分布情况可能没有改变，这在数据仓库中很常见。在这种情况下，您不希望再次收集统计信息，而只是希望重用原来的统计信息。另一个原因可能是您正在使用分区交换功能来刷新物化视图 (MV)，并且不希望收集关于物化视图的统计信息，因为关于被交换表的统计信息也会被导入。但是，您也可以将特定的表排除在自动统计信息收集作业之外，而不需要禁止整个作业。

统计信息历史

在优化器收集统计信息时可能出现的复杂情况之一是执行计划的改变 — 也就是说，原来的优化方法在收集统计信息之前一直工作良好，但是在此之后，由于新收集的统计信息产生了不良计划，导致查询突然出错。这种问题并不少见。

为避免这种灾难，统计信息的收集作业在收集新信息之前保存当前的统计信息。如果出现问题，您总可以返回到原有的统计信息，或者至少可以检查二者之间的不同之处，以便于解决问题。

例如，假设在 5 月 31 日晚上 10:00 开始运行表 REVENUE 上的统计信息收集作业，而随后查询的性能变差。Oracle 保存了原有的统计信息，您可以通过执行以下命令重新获取这些信息：
begin
dbms_stats.restore_table_stats (
'ARUP',
'REVENUE',
'31-MAY-04 10.00.00.000000000 PM -04:00');
end;

此命令恢复到 5 月 31 日晚上 10:00 为止的统计信息,时间信息是以 TIMESTAMP 数据类型提供。这样您就立即还原了由新的统计信息收集程序所作的更改。

您能够恢复的时间长度是由保留参数所决定的。要查看当前的保留参数，可使用以下查询：
SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
31

在本示例中表示可以保存相当于 31 天的统计信息，但并不能予以保证。要了解统计信息所覆盖到的确切时间和日期，只需使用以下查询：
SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------
17-MAY-04 03.21.33.594053000 PM -04:00

该查询表明可用的最陈旧统计信息日期为 5 月 17 日凌晨 3:21。

您可以通过执行内建的函数将保留时间设为不同的值。例如，要将其设为 45 天，可使用：
execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (45)