script to find tables that have stale statistics

Stale Statitics

options can have the following values

gather stale | gather  | gather empty | list stale | list empty | gather auto

DECLARE
 ObjList dbms_stats.ObjectTab;
 BEGIN
 dbms_stats.gather_database_stats(objlist=>ObjList, options=>’LIST STALE’);
 FOR i in ObjList.FIRST..ObjList.LAST
 LOOP
 dbms_output.put_line(ObjList(i).ownname || ‘.’ || ObjList(i).ObjName || ‘ ‘ || ObjList(i).ObjType || ‘ ‘ || ObjList(i).partname);
 END LOOP;
 END;
 /

Gather Histogram statistics

method_opt can have the following values
for all indexed columns size skewonly | ‘for all columns size repeat |for columns size auto

declare
 olist dbms_stats.objectTab;

 begin
 dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER',objlist=>olist);

 dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
 if olist.COUNT > 0 then
 FOR x in 1..olist.COUNT LOOP
 dbms_output.put_line('Object Name: ' || olist(x).objname );
 END LOOP;
 end if;
 end;
/

declare
olist dbms_stats.objectTab;

begin
dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER AUTO',objlist=>olist);

dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
if olist.COUNT > 0 then
FOR x in 1..olist.COUNT LOOP
dbms_output.put_line('Object Name: ' || olist(x).objname );
END LOOP;
end if;
end;