Automatic Statistics Gathering

原贴地址：http://www.dbform.com/html/2008/538.html

在Oracle10g中引入的优化器统计信息（Optimizer Statistics）自动收集，是一个看上去很不错的功能，但是在实际应用中却往往没有起到相应的效果，甚至在某些系统中我们会建议禁用这个功能。

阐述一些该功能的相关知识点。

1. Automatic Statistics Gathering是由Scheduler调度GATHER_STATS_JOB作业来完成的，在GATHER_STATS_JOB作业中则调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC存储过程。

2. 该作业在创建数据库的自动创建，并且设置为每天晚上10点到第二天早上6点和周六周日的全天为运行窗口期。在运行窗口期内，该作业都会运行，根据stop_on_window_close属性来决定，如在窗口期结束以后，该作业如果还没有运行完毕，是继续运行还是结束运行。

3. GATHER_DATABASE_STATS_JOB_PROC是内部的存储过程，基本上跟DBMS_STATS.GATHER_DATABASE_STATS的功能一样，但是有内部的优先顺序考虑，更新越多的表将会越优先收集统计信息。

4. 收集统计信息的表对象是，之前从来没有收集过的或者是更新的（包括insert,update,delete,truncate）记录数超过当前总记录数10%的表。记录数的更改量由Oracle数据库自动监控，在初始化参数statistics_level设置为TYPICAL或者ALL时，自动监控即会生效。

5. 在USER_TAB_MODIFICATIONS表中记录了所有被监控的表的数据量更改信息。该信息的更新将会稍微滞后于真实的修改，可以通过DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO存储过程来立刻将更改的信息更新到USER_TAB_MODIFICATIONS表中。对于更新之后再rollback的记录，仍然算为已经受影响的记录，Oracle不会在rollback之后再去更新USER_TAB_MODIFICATIONS表。

1. SQL> select * from user_tab_modifications where table_name='EMP';
2.  
3. no rows selected
4.  
5. SQL> select count(*) from emp;
6.  
7.   COUNT(*)
8. ----------
9.         14
10.  
11. SQL> update emp set sal=sal+100;
12.  
13. 14 rows updated.
14.  
15. SQL> select * from user_tab_modifications where table_name='EMP';
16.  
17. no rows selected
18.  
19. SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
20.  
21. PL/SQL procedure successfully completed.
22.  
23. SQL> select inserts,updates,deletes from user_tab_modifications wheretable_name
24. ='EMP';
25.  
26.    INSERTS    UPDATES    DELETES
27. ---------- ---------- ----------
28.          0         14          0
29.  
30. SQL> rollback;
31.  
32. Rollback complete.
33.  
34. SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
35.  
36. PL/SQL procedure successfully completed.
37.  
38. SQL> select inserts,updates,deletes from user_tab_modifications wheretable_name
39. ='EMP';
40.  
41.    INSERTS    UPDATES    DELETES
42. ---------- ---------- ----------
43.          0         14          0
44.  
45. SQL>

6. 在Oracle10g版本（包括最新的10.2.0.4）中没有已知的修改10%这个阀值的方法。但是在Oracle11g中则提供了SET_TABLE_PREFS等函数。

以下命令将指定表的STALE默认值从10%改为5%，该值可以从新的dba_tab_stat_prefs数据字典中查询获得。

1. --仅限于Oracle11g版本
2. BEGIN
3.   DBMS_STATS.SET_TABLE_PREFS ( ownname =>'KAMUS', tabname =>'T1', pname=>'STALE_PERCENT', pvalue =>'5');
4. END;
5. /
6.  
7. SQL> select * from dba_tab_stat_prefs;
8.  
9. OWNER      TABLE_NAME PREFERENCE_NAME      PREFE
10. ---------- ---------- -------------------- -----
11. KAMUS      T1         STALE_PERCENT        5

7. 在Oracle10g中运行以下命令，可以禁用统计信息自动收集功能。

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