Oracle 10g Statistic数据统计

Oracle 10g statistic数据统计,Oracle会根据这些统计信息来决定是走RBO(Rule-BasedOptimization)，还是走CBO(Cost-BasedOptimization)，会去选择哪种执行计划更划算,影响是否走相关的索引等.如果是CBO的话，它依靠准确的（或者说比较准确的）统计信息来产生优化的执行路径,如果没有做过统计，CBO也就没有做cost评估的依据，所以虽然是CBO，但是实际上还是用RBO了，而且如果不常做统计的话，由于CBO是以统计为依据的，所以这时CBO的依据信息有问题，CBO也会不准。 所以 DBA 需要确保定期收集统计信息，创建另一个执行核对清单。

举个例子：
一次测试，一个sql语句执行要20分钟，有时候还出不了结果，发现查看执行计划，发现居然走了全表扫描（表中大约300w条记录），为啥不用索引呢，查看索引状态，一切正常。分析了相关的表，然后重新执行3分钟搞定！

世事无绝对，analyze表会增加CBO执行的性能？不一定的。
我就碰到一个语句分析后要执行30多分钟，删除分析后，只要30秒。
很多情况下不一定的，最好是自己从执行计划判断。

analyze table tablename compute statistics for all indexes;
analyze table tablename delete statistics

顺便补充一点，表只有分析了之后，num_rows才会有值。
select * from user_all_tables a where a.num_rows <10;

analyze table tablename compute statistics;

SELECT 'ANALYZE  TABLE  ' || TABLE_NAME || '  COMPUTE  STATISTICS;'
  FROM (SELECT DISTINCT TABLE_NAME FROM ALL_COL_COMMENTS);

SQL语句的执行计划走不走索引除了与Statistic有关系，还有pfile的一个参数有关：optimizer_index_cost_adj. 该参数影响优化器选择索引还是全表扫描的倾向,建议将其设为40.

在 10g 中，通过设置初始化参数 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
10g Release 2 (10.2)
Part Number B14211-01

14.3.1.5 Determining 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 when STATISTICS_LEVEL is set to TYPICAL or ALL. Monitoring tracks the approximate number of INSERTs, UPDATEs, and DELETEs for 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 the USER_TAB_MODIFICATIONS view. Following a data-modification, there may be a few minutes delay while Oracle propagates the information to this view. Use the DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure to immediately reflect the outstanding monitored information kept in the memory.

The GATHER_DATABASE_STATS or GATHER_SCHEMA_STATS procedures gather new statistics for tables with stale statistics when the OPTIONS parameter is set to GATHER STALE or GATHER AUTO. If a monitored table has been modified more than 10%, then these statistics are considered stale and gathered again.

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

当然我们可以用job来定时的执行Statistic.

1. 创建存储过程：
CREATE OR REPLACE PROCEDURE USER."ANALYZEDB"
IS
   CURSOR get_ownertable
   IS
      SELECT table_name
        FROM user_tables;

   ownertable   get_ownertable%ROWTYPE;
BEGIN
   OPEN get_ownertable;

   LOOP
      FETCH get_ownertable
       INTO ownertable;

      EXIT WHEN get_ownertable%NOTFOUND;

      EXECUTE IMMEDIATE    'analyze table '
                        || ownertable.table_name
                        || ' compute statistics for table for all indexes for all indexed columns ';
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      RAISE;
END;
/

2. 将存储过程写进JOB

BEGIN
  SYS.DBMS_JOB.REMOVE(3);
COMMIT;
END;
/

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
  ( job       => X
   ,what      => 'ANALYZEDB;'
   ,next_date => to_date('14-10-2009 06:00:00','dd/mm/yyyy hh24:mi:ss')
   ,interval  => 'trunc(sysdate + 7) + 6/24'
   ,no_parse  => FALSE
  );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/