优化器统计自动收集的一点总结

从10g开始 oracle默认会自动收集优化器统计信息，自动搜集只会为下面2种情况搜集优化器统计信息：

1、missing statistics

2、stale statistics

[@more@]

1、missing statistics

如果sql语句中访问的对象没有optimizer statistics，那么oracle会自动估算optimizer statistics，但是这样会影响sql的解析效率

2、stale statistics

statistics陈旧是指表里的数据修改超过10%，oracle从10g开始自动对表上的dml和truncate操作进行监控，自动监控的条件是：

statistics_level为typical或者all，监控的结果可以通过dba_tab_modifications来查看。数据被修改之后oracle监控的结果
是驻留在内存里面的，所以不是修改之后就可以实时的在dba_tab_modifications里体现出来。8i的时侯每隔3小时oracle会把监控的结果从内存刷新到disk，9i的时侯这个间隔调整为15分钟，如果你想手动刷新，可以使用过程：
dbms_stats.flush_database_monitoring_info.
下面是简单的测试过程：
--===============================
SQL> create table t tablespace users as select * from dba_objects where 1=2;

Table created.

SQL> exec dbms_stats.gather_table_stats('A','T');

PL/SQL procedure successfully completed.

SQL> insert into t select * from dba_objects;

18008 rows created.

SQL> commit;

Commit complete.

SQL> select * from ALL_tab_modifications where table_owner='A';

no rows selected

SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

SQL> select a.table_name,a.inserts,a.updates,a.deletes,a.timestamp,a.truncated,a.drop_segments from user_tab_modifications a;

TABLE_NAME INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
------------------------------ ---------- ---------- ---------- ------------------- --- -------------
T 18008 0 0 2013/07/27 19:37:33 NO 0

SQL> insert into t select * from dba_objects;

18008 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

SQL> select a.table_name,a.inserts,a.updates,a.deletes,a.timestamp,a.truncated,a.drop_segments from user_tab_modifications a;

TABLE_NAME INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
------------------------------ ---------- ---------- ---------- ------------------- --- -------------
T 36016 0 0 2013/07/27 19:41:52 NO 0

SQL>
--===============================
optimizer statistics是否stale，可以通过下面过程验证：
SQL> exec dbms_stats.gather_table_stats('A','T');

PL/SQL procedure successfully completed.

SQL> select stale_stats from user_tab_statistics a where table_name='T';

STA
---
NO

SQL> insert into t select * from dba_objects;

18008 rows created.

SQL> commit;

Commit complete.

SQL> select stale_stats from user_tab_statistics a where table_name='T';

STA
---
NO

SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

SQL> select stale_stats from user_tab_statistics a where table_name='T';

STA
---
YES

SQL>

--=============================
有关对表数据的修改的自动监控是从10g开始的，10g以前需要通过下面命令开启对表的monitoring：
SQL> select table_name,monitoring from user_tables where table_name='T';

TABLE_NAME MON
------------------------------ ---
T YES

SQL> alter table t nomonitoring;

Table altered.

SQL> select table_name,monitoring from user_tables where table_name='T';

TABLE_NAME MON
------------------------------ ---
T YES

SQL> alter table t monitoring;

Table altered.

SQL> select table_name,monitoring from user_tables where table_name='T';

TABLE_NAME MON
------------------------------ ---
T YES

SQL>
--=====================
所以从10g开始单独对表设置monitoring或者nomonitoring已经不起作用了。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19602/viewspace-1060786/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19602/viewspace-1060786/