When Objects statistics Become Stale...

So, something else I learned recently...

Objects are considered stale when 10% of the total rows have been changed. When you issue GATHER_TABLE_STATS with GATHER STALE, the procedure checks the USER_TAB_MODIFICATIONS view. If a monitored table has been modified more than 10%, then statistics are gathered again .

test@PWTK52> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

test@PWTK52> create table t as select * from all_objects ;

Table created.

test@PWTK52> exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T',estimate_percent=>dbms_stats.auto_sample_size);

PL/SQL procedure successfully completed.

test@PWTK52> alter table t monitoring;

Table altered.

test@PWTK52> select count(*) from T;

  COUNT(*)
----------
     25982

test@PWTK52> declare
  2    l_ObjectTab dbms_stats.ObjectTab;
  3  begin
  4    dbms_stats.gather_database_stats(options=>'LIST STALE',objlist=>l_ObjectTab);
  5    if l_ObjectTab.count > 0 then
  6      for i in l_ObjectTab.first..l_ObjectTab.last
  7      loop
  8       if l_objecttab(i).ownname = USER then
  9             dbms_output.put_line ( l_objecttab(i).ownname||'-'|| l_objecttab(i).objtype||'-'|| l_objecttab(i).objname||'-'|| l_objecttab(i).partname );
 10       end if;
 11      end loop;
 12    end if;
 13    l_ObjectTab.delete;
 14  end;
 15  /

PL/SQL procedure successfully completed.

So, now updating 10% of the records in Table ( 25982 * 10% = 2598)

test@PWTK52> update t
  2  set object_name = initcap(object_name)
  3  where rownum <= 2600;

2600 rows updated.

test@PWTK52> commit;

Commit complete.

Now, the objects become Stale due to 10% changes in rows.

test@PWTK52> SELECT table_name, inserts, updates, deletes
  2  FROM all_tab_modifications
  3  WHERE table_owner ='TEST';

TABLE_NAME                        INSERTS    UPDATES    DELETES
------------------------------     ----------     ----------     ----------
T                                                0                   2600           0

test@PWTK52> declare
  2    l_ObjectTab dbms_stats.ObjectTab;
  3  begin
  4    dbms_stats.gather_database_stats(options=>'LIST STALE',objlist=>l_ObjectTab);
  5    if l_ObjectTab.count > 0 then
  6      for i in l_ObjectTab.first..l_ObjectTab.last
  7      loop
  8       if l_objecttab(i).ownname = USER then
  9             dbms_output.put_line ( l_objecttab(i).ownname||'-'|| l_objecttab(i).objtype||'-'|| l_objecttab(i).objname||'-'|| l_objecttab(i).partname );
 10       end if;
 11      end loop;
 12    end if;
 13    l_ObjectTab.delete;
 14  end;
 15  /
TEST-TABLE-T-

PL/SQL procedure successfully completed.

Script to Analyze objects having missing statistics and Empty statistics.

scott@ORCL> DECLARE
  2    l_ObjectTab dbms_stats.ObjectTab;
  3  BEGIN
  4    -- Empty Statistics
  5    dbms_stats.gather_schema_stats(ownname=>USER,OPTIONS=>'list empty',objlist=>l_ObjectTab);
  6    IF l_ObjectTab.count > 0 THEN
  7      FOR i             IN 1..l_ObjectTab.count
  8      LOOP
  9        IF l_objecttab(i).objtype = 'TABLE' THEN
 10          dbms_stats.gather_table_stats(ownname=>l_objecttab(i).ownname,
 11                                        tabname=>l_objecttab(i).objname,
 12                                        estimate_percent=>dbms_stats.auto_sample_size,
 13                                        partname=>l_objecttab(i).partname);
 14        ELSIF l_objecttab(i).objtype = 'INDEX' THEN
 15          dbms_stats.gather_index_stats(ownname=>l_objecttab(i).ownname,
 16                                        indname=>l_objecttab(i).objname,
 17                                        estimate_percent=>dbms_stats.auto_sample_size,
 18                                        partname=>l_objecttab(i).partname);
 19        END IF;
 20      END LOOP;
 21    END IF;
 22    -- Stale Statistics
 23    dbms_stats.gather_schema_stats(ownname=>USER,OPTIONS=>'list stale',objlist=>l_ObjectTab);
 24    IF l_ObjectTab.count > 0 THEN
 25      FOR i             IN 1..l_ObjectTab.count
 26      LOOP
 27        IF l_objecttab(i).objtype = 'TABLE' THEN
 28          dbms_stats.gather_table_stats(ownname=>l_objecttab(i).ownname,
 29                                        tabname=>l_objecttab(i).objname,
 30                                        estimate_percent=>dbms_stats.auto_sample_size,
 31                                        partname=>l_objecttab(i).partname);
 32        ELSIF l_objecttab(i).objtype = 'INDEX' THEN
 33          dbms_stats.gather_index_stats(ownname=>l_objecttab(i).ownname,
 34                                        indname=>l_objecttab(i).objname,
 35                                        estimate_percent=>dbms_stats.auto_sample_size,
 36                                        partname=>l_objecttab(i).partname);
 37        END IF;
 38      END LOOP;
 39    END IF;
 40  END;
 41  /

PL/SQL procedure successfully completed.