How to List the Objects with Stale Statistics Using dbms_stats.gather_schema_stats options=>'LIST STALE' (Doc ID 457666.1)

 How to List the Objects with Stale Statistics Using dbms_stats.gather_schema_stats options=>'LIST STALE' (Doc ID 457666.1)

Even though automated statistics gathering is in place and tables are monitored, some objects show very old dates in the last_analyzed column of dba_tables. This article shows you hove to use dbms_stats.gather_schema_stats options=>'LIST STALE' to resolve this.

Automatic statistics collection job using DBMS_STATS package depends on monitoring data to determine when to collect statistics on objects with stale statistics. If a monitored table has been modified more than 10%, then these statistics are considered stale and are re-gathered.

Procedure can be used to determine if more than 10% has been modified:

set serveroutput on

declare
mystaleobjs dbms_stats.objecttab;
begin
-- check whether there is any stale objects
dbms_stats.gather_schema_stats(ownname=>'SCOTT', options=>'LIST STALE',objlist=>
mystaleobjs);
for i in 1 .. mystaleobjs.count loop
dbms_output.put_line(mystaleobjs(i).objname);
end loop;
end;
/

Procedure to Check for Objects at Database Level:

set serveroutput on

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;
/

SELECT TABLES.OWNER, TABLES.TABLE_NAME,
ROUND((DELETES + UPDATES + INSERTS)/NUM_ROWS*100) PERCENTAGE
FROM DBA_TABLES TABLES, DBA_TAB_MODIFICATIONS MODIFICATIONS
WHERE TABLES.OWNER = MODIFICATIONS.TABLE_OWNER
AND TABLES.TABLE_NAME = MODIFICATIONS.TABLE_NAME AND NUM_ROWS > 0
AND ROUND ( (DELETES + UPDATES + INSERTS) / NUM_ROWS * 100) >= 10
ORDER BY 3 desc
/