Monday, July 8, 2013

How to check if you have empty or stale statistics?

I have created and one can use this simple script.

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('Stale statistics--->' ||' Owner: '||obj_stale(j).ownname||'--'||'Object name: '||obj_stale(j).objName||'--'||'Object type: '||obj_stale(j).objType);
       end loop;
   end;
/

Sample output below:

Empty statistics---> Owner: HORIA--Object name: CONTRACTS_SEC--Object type: TABLE
Empty statistics---> Owner: SH--Object name: SYS_IOT_TOP_76625--Object type: INDEX
Empty statistics---> Owner: SH--Object name: SYS_IOT_TOP_76627--Object type: INDEX
Stale statistics---> Owner: SYS--Object name: HISTGRM$--Object type: TABLE
Stale statistics---> Owner: SYS--Object name: HIST_HEAD$--Object type: TABLE
Stale statistics---> Owner: SYS--Object name: IND$--Object type: TABLE
Stale statistics---> Owner: SYS--Object name: MON_MODS$--Object type: TABLE

1 comment: