ITPub博客

首页 > Linux操作系统 > Linux操作系统 > (转发)一个帖子missing statistics

(转发)一个帖子missing statistics

原创 Linux操作系统 作者:阿修雷 时间:2013-08-12 11:04:47 0 删除 编辑
原帖地址http://warehouse.itpub.net/post/777/531205

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

1、missing statistics

2、stale statistics


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

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2013-06-06

  • 博文量
    2
  • 访问量
    8086