dbms_stats.gather_schema_stats的GATHER STALE选项

    dbms_stats.gather_schema_stats可以使用options => 'GATHER STALE'来只分析有过时的统计信息的表。那么Oracle是如何知道表的统计信息是过时(STALE)的呢？这就是本文探讨的问题。

    首先Oracle是根据dba_tab_statistics的stale_stats字段来找出统计信息STALE的表，默认情况下STALE的定义是自从上次分析之后变动超过10%的表。那么Oracle是如何知道哪些表有多少变动呢？

    答案就是在10g之前，通过alter table monitoring来制定，在10g之后，只要开启了AWR，也就是statistics_level=typical或all之后，所有表都自动被monitoring。这些收集的信息就放在dba_tab_modifications视图里。所以，dba_tab_statistics的stale_stats字段是通过分析dba_tab_modifications来得到。

    但是最近我们遇到的一个问题是，dba_tab_statistics.stale_stats显示没有任何表有stale stats，但是通过dbms_stats.gather_schema_stats的options => 'GATHER STALE'进行收集，却有不少表被分析。

    原来问题出在dba_tab_modifications视图的刷新频率不是实时上，在文档里可以看到：

“For performance reasons, the Oracle Database does not populate this view immediately when the actual modifications occur. Run the FLUSH_DATABASE_MONITORING_INFO procedure in the DIMS_STATS PL/SQL package to populate this view with the latest information. ”

    而dbms_stats.gather_schema_stats的options => 'GATHER STALE'执行之前相当于会自动的FLUSH_DATABASE_MONITORING_INFO一次然后再开始分析。

    所以解决方法是在脚本里添加dbms_stats.FLUSH_DATABASE_MONITORING_INFO();，再查看dba_tab_statistics.stale_stats。

   

    综上，依赖关系如下：

dbms_stats.gather_schema_stats's options 'GATHER STALE'

=======>   dba_tab_statistics.stale_stats = 'YES'   

=======>   dba_tab_modifications has 10% change

=======>   9i(alter table monitoring),>=10G(AWR enabled)

=======>   dbms_stats.FLUSH_DATABASE_MONITORING_INFO(); can help get the most current info, otherwize not accurate.

 

具体实验如下：

1. user_tab_statistics.stale_stats is based on user_tab_modifications
SQL> insert into AAA select * from AAA;

65536 rows created.

SQL> commit;

Commit complete.

SQL> select TABLE_NAME,INSERTS,UPDATES,DELETES from user_tab_modifications;

no rows selected

SQL> select TABLE_NAME from user_tab_statistics where stale_stats = 'YES';

no rows selected

SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO();

PL/SQL procedure successfully completed.

SQL> select TABLE_NAME,INSERTS,UPDATES,DELETES from user_tab_modifications;

TABLE_NAME                        INSERTS    UPDATES    DELETES
------------------------------ ---------- ---------- ----------
AAA                                 65536          0          0

SQL> select TABLE_NAME from user_tab_statistics where stale_stats = 'YES';

TABLE_NAME
------------------------------
AAA

2.gather_schema_stats will analyze the actuall 'STALE' tables even if they didn't show up in the user_tab_statistics.

SQL> insert into AAA select * from AAA;

262144 rows created.

SQL> commit;

Commit complete.

SQL> select TABLE_NAME,INSERTS,UPDATES,DELETES from user_tab_modifications;

no rows selected

SQL> select TABLE_NAME from user_tab_statistics where stale_stats = 'YES';

no rows selected

SQL> select to_char(LAST_ANALYZED,'yyyymmdd hh24:mi:ss') from user_Tables where table_name='AAA';

TO_CHAR(LAST_ANAL
-----------------
20100921 20:29:12
 
SQL> BEGIN 
  2  dbms_stats.gather_schema_stats(ownname => 'HAOZHU',options => 'GATHER STALE',estimate_percent => 100,method_opt=> 'for all columns size 1',  cascade=>true,no_invalidate => false, degree=>1);
END;  3 
  4  /
 
PL/SQL procedure successfully completed.

SQL> select to_char(LAST_ANALYZED,'yyyymmdd hh24:mi:ss') from user_Tables where table_name='AAA';

TO_CHAR(LAST_ANAL
-----------------
20100921 20:30:43

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

转载于:http://blog.itpub.net/15415488/viewspace-674679/