測(cè)試過程如下(測(cè)試環(huán)境11.2.0.3 RAC):
1 創(chuàng)建一個(gè)200w行左右的測(cè)試表,數(shù)據(jù)源為dba_objects。
2 查看視圖中統(tǒng)計(jì)信息: select a.OWNER,a.INDEX_NAME,a.TABLE_NAME,a.LAST_ANALYZED from dba_ind_statistics a where table_name='STATEST1' and table_owner='YCR' select b.TABLE_NAME,b.OWNER,b.LAST_ANALYZED from dba_tab_statistics b where table_name='STATEST1' and owner in ('YCR')
通過觀察得到,索引的統(tǒng)計(jì)信息為索引創(chuàng)建時(shí)自動(dòng)收集,表的統(tǒng)計(jì)信息為空。
3 手工收集統(tǒng)計(jì)信息,在執(zhí)行約38秒時(shí)手工中斷(已通過測(cè)試,此表收集統(tǒng)計(jì)信息約需要60秒)
begin
dbms_stats.gather_table_stats(ownname => 'YCR',tabname => 'STATEST1' ,estimate_percent => 100 ,cascade => true);
end;
/ 4 查看視圖中統(tǒng)計(jì)信息: select a.OWNER,a.INDEX_NAME,a.TABLE_NAME,a.LAST_ANALYZED from dba_ind_statistics a where table_name='STATEST1' and table_owner='YCR' select b.TABLE_NAME,b.OWNER,b.LAST_ANALYZED from dba_tab_statistics b where table_name='STATEST1' and owner in ('YCR')
因?yàn)榻y(tǒng)計(jì)信息并未收集完全,所以此二表中的數(shù)據(jù)并無變化,再次收集統(tǒng)計(jì)信息完成后,表中信息才更新。