ITPub博客

首页 > 应用开发 > IT综合 > 优化器统计自动收集的一点总结

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

原创 IT综合 作者:warehouse 时间:2013-07-27 19:55:58 0 删除 编辑

从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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-07

  • 博文量
    717
  • 访问量
    5098757