Using the code below one can find the tables/indexes that have stale statistics in a database, when options=>’GATHER AUTO’ is used oracle gathers statistics analyzes the tables/indexes that have stale statistics. Table monitoring is enabled by default in 10g to find table which is used to find table statistics, when STATISTICS_LEVEL is set to “BASIC” table monitoring is disabled. In 10g when the value of statistics_level is “typical” or “all” table monitoring is turned enabled. In 9i one can enable/disable table monitoring by calling DBMS_STATS.ALTER_SCHEMA_TABLE_MONITORING
SQL> SET SERVEROUTPUT ON
SQL> 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;
/
— shows tables that have stale statistics
SYS.COL_USAGE$ TABLE
SYS.DEPENDENCY$ TABLE
SYS.HISTGRM$ TABLE
SYS.HIST_HEAD$ TABLE
…
Note: To find schema level stats that are stale one can call DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>’SCOTT’, objlist=>ObjList, options=>’LIST STALE’);
The view has the monitoring information about tables user_tab_modifications, all_tab_modifications and dba_tab_modifications.
alexlima.com 
Thanks! Very helpful.
LikeLike
ERROR at line 4:
ORA-06550: line 4, column 1:
PLS-00306: wrong number or types of arguments in call to
‘GATHER_DATABASE_STATS’
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored
How were you able to resolve above error ? (I executed above anonymous block and got above error).
LikeLike
Hi
Can you include arguments passed when you the executed the dbms_stats.gahter_database_stats that generated the error?
Thanks
LikeLike
Little late…
Check if the quotes really are quotes. so it should be ‘ and not `
LikeLike
Nice … so beautiful and useful
LikeLike
Where do you get the list of tables? What is usage of dbms_stats.ObjectTab?
LikeLike
The list of tables with stale statistics is in sys.dba_tab_modifications. I am not sure what do you mean by usage of dbms_stats.ObjectTab, it is a datatype defined in the package specifications of DBMS_STATS which is a Oracle table.
type ObjectElem is record (
ownname varchar2(32), — owner
objtype varchar2(6), — ‘TABLE’ or ‘INDEX’
objname varchar2(32), — table/index
partname varchar2(32), — partition
subpartname varchar2(32) — subpartition
);
type ObjectTab is table of ObjectElem;
LikeLike