Oracle 11gR2- STALE_PERCENT in DBMS_STATS

在11gR2版本中,统计信息还有一个选项“STALE_PERCENT”。该选项的作用是当数据量发生变化超过了这个阈值,晚上定时任务就会对该表执行自动统计信息收集。默认这个值被设置成为了10%。假设有一个数据仓库,里面有一张表,这张表收集了列上的直方图信息,每天这张表都会做ETL操作,如果这张数据表的数据发生了一些小变化(插入一些新的超出范围的数据),这可能就会给优化程序带来困难。所以我们希望每天都收集这张表的统计信息。
我们来做个小测试。

create table demo (col1 number);

declare
i number;
begin
for i in 1..1000000
loop
insert into demo values(i);
end loop;
commit;
end;
/

begin
dbms_stats.set_table_prefs('U1', 'DEMO','STALE_PERCENT', '0.1');
end;
/

SQL> select count(*) from demo;

  COUNT(*)
----------
   1000000

首先插入1百万数据,然后收集统计信息,收集完成之后查看STALE_STATS值,现在为false,也就是数据量变化没超过设置的阈值,因此不会自动收集统计信息。

exec dbms_stats.gather_table_stats('U1','DEMO');


SQL> select table_name,num_rows,blocks from user_tables where table_name='DEMO';

TABLE_NAME                                           NUM_ROWS     BLOCKS
-------------------------------------------------- ---------- ----------
DEMO                                                  1000000       1630

SQL> select TABLE_NAME,STALE_STATS from user_tab_statistics where table_name='DEMO';

TABLE_NAME                                         STA
-------------------------------------------------- ---
DEMO                                               NO

我们在做个更新操作,让数据变得陈旧,我们总共的数据量是100万,然后0.1%就是1000,为了方便起见我们更新10001条数据。当更新完成之后,我们可以发现STALE_STATS变成了yes。也就是说我们设置的0.1%这个阈值生效了。

SQL> update demo set col1=col1 where col1 < 1002; 1001 rows updated. SQL> commit;

Commit complete.

SQL> select TABLE_NAME,STALE_STATS from user_tab_statistics where table_name='DEMO';

TABLE_NAME                                         STA
-------------------------------------------------- ---
DEMO                                               YES

当然最绝的是你可以把参数设置成0,那么它就会每天都强制收集统计信息。还有一个不错的视图user_tab_modifications,它可以查到你的表最近的INSERTS、UPDATES、DELETES的数量是多少。而这个也是10g以及10g之前是否重新收集统计信息的一个重要参考依据。同时在查询DBA_TAB_MODIFICATIONS视图时,应确保在执行此操作之前运行DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO以获得准确的结果。

SQL> select * from user_tab_modifications where TABLE_NAME='DEMO';

TABLE_NAME  PARTITION_NAME   SUBPARTITION_NAME   INSERTS    UPDATES    DELETES TIMESTAMP TRU DROP_SEGMENTS
----------- ---------------- --------------------------- ---------- ---------- --------- --- -------------
DEMO                                                   0       1001          0 07-JUN-18 NO

而如果dba_tab_modifications中没有行表示,统计信息已经收集了。表中数据现在没发生改变。又或者你需要执行DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO把视图内容进行更新再查。

SQL> exec dbms_stats.gather_table_stats('U1','DEMO');

PL/SQL procedure successfully completed.

SQL> select * from user_tab_modifications where TABLE_NAME='DEMO';

no rows selected
分享到: 更多

Post a Comment

Your email is never published nor shared. Required fields are marked *