How to find the tables that have stale statistics?

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.

7 thoughts on “How to find the tables that have stale statistics?

    • 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).

      • Hi

        Can you include arguments passed when you the executed the dbms_stats.gahter_database_stats that generated the error?

        Thanks

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s