利用dbms_stats.gather_database_stats包快速確定統計資訊為空或陳舊的物件

set line 150
set serveroutput on
declare
  obj_empty dbms_stats.ObjectTab;
  obj_stale dbms_stats.ObjectTab;
begin
  dbms_stats.gather_database_stats(options => 'LIST EMPTY',objlist => obj_empty);
  for i in 1..obj_empty.count
    loop
      dbms_output.put_line('Empty statistics---> Owner:  '||obj_empty(i).ownname||'   Object name:  '||obj_empty(i).objName||'- -Object type:  '||obj_empty(i).objType);
      end loop;  
  dbms_stats.gather_database_stats(options => 'LIST STALE',objlist => obj_stale);
  for j in 1..obj_stale.count
    loop
      dbms_output.put_line('Empty statistics---> Owner:  '||obj_stale(j).ownname||'   Object name:  '||obj_stale(j).objName||'- -Object type:  '||obj_stale(j).objType);
      end loop;
end;
/
輸出結果如下：
Empty statistics---> Owner:  JASON   Object name:  TAB_NAME- -Object type:  TABLE
Empty statistics---> Owner:  JASON   Object name:  TAB_TEST- -Object type:  TABLE
Empty statistics---> Owner:  JASON   Object name:  TEST- -Object type:  TABLE
Empty statistics---> Owner:  LIUJL   Object name:  G_TEST- -Object type:  TABLE
Empty statistics---> Owner:  LIUJL   Object name:  LJL- -Object type:  TABLE
Empty statistics---> Owner:  LJL   Object name:  G_TEST- -Object type:  TABLE
Empty statistics---> Owner:  LJL   Object name:  LJL- -Object type:  TABLE
Empty statistics---> Owner:  SCOTT   Object name:  AA- -Object type:  TABLE
Empty statistics---> Owner:  SCOTT   Object name:  MAGAZINE_ARTICLES- -Object type:  TABLE
Empty statistics---> Owner:  SYS   Object name:  AQ$_KUPC$DATAPUMP_QUETAB_1_D- -Object type:  TABLE
最後我們可以用DBMS_STATS.GATHER包來收集相應的統計資訊