Oracle10g 自动统计信息

http://blog.ixpub.net/html/62/8026162-404103.html

 2008-11-28 17:39

Oracle 10g 版本最后不再支持（而不是反对）基于规则的优化器 (RBO)。因为 CBO 依靠准确的（或者说比较准确的）统计信息来产生优化的执行路径，所以 DBA 需要确保定期收集统计信息，创建另一个执行核对清单。在 10g 之前，这一过程可能由于多种原因而徒劳无功。在 10g 中已经消除了很多这类的顾虑，它能够自动收集统计信息。在 Oracle9i 中，您可以通过打开表监视选项 (ALTER TABLE ...MONITORING) 然后检查这些表的视图 DBA_TAB_MODIFICATIONS，检查表中的数据是否已明显更改过。

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

SQL>show parameter statistics_

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

statistics_level 默认是typical，在10g中表监控是激活的，强烈建议在10g中此参数的值是typical。如果STATISTICS_LEVEL设置为basic，不仅不能监控表，而且将禁掉如下一些10g的新功能：
ASH(Active Session History)
ASSM(Automatic Shared Memory Management)
AWR(Automatic Workload Repository)
ADDM(Automatic Database Diagnostic Monitor)

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

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

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

SQL>set lines 256
SQL>col last_start_date format a40
SQL>col last_run_duration format a30
SQL>SELECT owner, enabled, auto_drop, restartable, state, run_count, failure_count, last_start_date, last_run_duration FROM dba_scheduler_jobs where job_name = 'GATHER_STATS_JOB';

OWNER                          ENABL AUTO_ RESTA STATE            RUN_COUNT FAILURE_COUNT LAST_START_DATE                          LAST_RUN_DURATION
------------------------------ ----- ----- ----- --------------- ---------- ------------- ---------------------------------------- ------------------------------
SYS                            TRUE FALSE TRUE SCHEDULED               57             0 27-NOV-08 10.00.02.228110 PM +08:00      +000000000 00:00:25.870394

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

SQL>exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

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

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

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

例如，假设在 5 月 31 日晚上 10:00 开始运行表 REVENUE 上的统计信息收集作业，而随后查询的性能变差。Oracle 保存了原有的统计信息，您可以通过执行以下命令重新获取这些信息：

SQL>exec dbms_stats.restore_table_stats ( 'ARUP', 'REVENUE', '31-MAY-04 10.00.00.000000000 PM -04:00');

此命令恢复到 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
----------------------------------------------------------------
27-10月-08 11.14.20.357622000 下午 +08:00

该查询表明可用的最陈旧统计信息日期为 10 月 27 日下午 11:14。

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

SQL>exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (45);

另外为什么有部分表没有被分析呢, 原因跟表DBA_TAB_MODIFICATIONS有关.

Oracle® Database Performance Tuning Guide
10gRelease 2 (10.2)
Part Number B14211-01

14.3.1.5Determining Stale Statistics

Statistics must be regularly gathered on database objects as those database objects are modified over time. In order to determine whether or not a given database object needs new database statistics, Oracle provides a table monitoring facility. This monitoring is enabled by default whenSTATISTICS_LEVELis set toTYPICALorALL. Monitoring tracks the approximate number ofINSERTs,UPDATEs, andDELETEsfor that table, as well as whether the table has been truncated, since the last time statistics were gathered. The information about changes of tables can be viewed in theUSER_TAB_MODIFICATIONSview. Following a data-modification, there may be a few minutes delay while Oracle propagates the information to this view. Use theDBMS_STATS.FLUSH_DATABASE_MONITORING_INFOprocedure to immediately reflect the outstanding monitored information kept in the memory.

TheGATHER_DATABASE_STATSorGATHER_SCHEMA_STATSprocedures gather new statistics for tables with stale statistics when theOPTIONSparameter is set toGATHERSTALEorGATHERAUTO. If a monitored table has been modified more than10%, then these statistics are considered stale and gathered again.

默认情况下Oracle会记录数据库表的变化, 只有变化超过10%的表,才会做自动分析.

--End--