在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表。

SQL> select * from user_tab_modifications where table_name='EMP';
no rows selected
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> update emp set sal=sal+100;
14 rows updated.
SQL> select * from user_tab_modifications where table_name='EMP';
no rows selected
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
PL/SQL procedure successfully completed.
SQL> select inserts,updates,deletes from user_tab_modifications where table_name
='EMP';
INSERTS UPDATES DELETES
---------- ---------- ----------
0 14 0
SQL> rollback;
Rollback complete.
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
PL/SQL procedure successfully completed.
SQL> select inserts,updates,deletes from user_tab_modifications where table_name
='EMP';
INSERTS UPDATES DELETES
---------- ---------- ----------
0 14 0
SQL>

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

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

--仅限于Oracle11g版本
BEGIN
DBMS_STATS.SET_TABLE_PREFS ( ownname =>'KAMUS', tabname =>'T1', pname =>'STALE_PERCENT', pvalue =>'5');
END;
/
SQL> select * from dba_tab_stat_prefs;
OWNER TABLE_NAME PREFERENCE_NAME PREFE
---------- ---------- -------------------- -----
KAMUS T1 STALE_PERCENT 5

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

BEGIN
DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;
/

ITPub博客

Automatic Statistics Gathering

最新文章