在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博客 ” ,链接:http://blog.itpub.net/16158219/viewspace-548852/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16158219/viewspace-548852/