Find missing or stale statistics

Cyrille Modiano
Latest posts by Cyrille Modiano (see all)

Purpose

Statistics are primordial for the optimizer to choose the best execution plan possible. Sometimes you encounter a sub-optimal plan and need to find out if the tables involved in the statement are up to date. This query will help you find out which tables have been modified significantly since the last statistics gathering. For this query to work you will need the MONITORING to be activated on your tables, this is automatically the case since 11g.

Script to find missing or stale statistics

select 	m.TABLE_OWNER,
		m.TABLE_NAME,
		m.INSERTS,
		m.UPDATES,
		m.DELETES,
		m.TRUNCATED,
		m.TIMESTAMP as LAST_MODIFIED,		
		round((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0),2) as EST_PCT_MODIFIED,
		t.num_rows as last_known_rows_number,
		t.last_analyzed
From 	dba_tab_modifications m,
		dba_tables t
where 	m.table_owner=t.owner
and	m.table_name=t.table_name
and 	table_owner not in ('SYS','SYSTEM')
and 	((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0) > 10 or t.last_analyzed is null)
order by timestamp desc;

By default, I filter the tables using the rule of the 10% modified, but you can change this percentage and adapt it to your needs.

Note that this view is not updated in real time for performance reasons, if you want to have the last statistics available, use the following command before executing the query:

Exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

UPDATE

added the partitions as well as recommended by Marko

select 	m.TABLE_OWNER,
		'NO' as IS_PARTITION,
		m.TABLE_NAME as NAME,		
		m.INSERTS,
		m.UPDATES,
		m.DELETES,
		m.TRUNCATED,
		m.TIMESTAMP as LAST_MODIFIED,		
		round((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0),2) as EST_PCT_MODIFIED,
		t.num_rows as last_known_rows_number,
		t.last_analyzed
From 	dba_tab_modifications m,
		dba_tables t		
where 	m.table_owner=t.owner
and		m.table_name=t.table_name
and 	m.table_owner not in ('SYS','SYSTEM')
and 	((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0) > 10 or t.last_analyzed is null)
union
select 	m.TABLE_OWNER,
		'YES' as IS_PARTITION,		
		m.PARTITION_NAME as NAME,		
		m.INSERTS,
		m.UPDATES,
		m.DELETES,
		m.TRUNCATED,
		m.TIMESTAMP as LAST_MODIFIED,		
		round((m.inserts+m.updates+m.deletes)*100/NULLIF(p.num_rows,0),2) as EST_PCT_MODIFIED,
		p.num_rows as last_known_rows_number,
		p.last_analyzed		
From 	dba_tab_modifications m,		
		dba_tab_partitions p
where 	m.table_owner=p.table_owner
and		m.table_name=p.table_name
and 	m.PARTITION_NAME = p.PARTITION_NAME
and 	m.table_owner not in ('SYS','SYSTEM')
and 	((m.inserts+m.updates+m.deletes)*100/NULLIF(p.num_rows,0) > 10 or p.last_analyzed is null)
order by 8 desc;

Thanks for reading.

15 thoughts on “Find missing or stale statistics

  1. Hello Cyrille,

    it would be also useful to check table partitions with stale statistics.
    Nice report – thanks for sharing.

    Regards,
    Marko

  2. Created a new table, inserted 1 record. It does not appear in the results of your SQL.
    So tables with missing statistics are missing.

    1. Hi Igor,

      It’s because this query relies on existing table statistics, I should also include the tables with no statistics.
      I updated the query to fix that, thanks for pointing this out.
      Here is an example to illustrate the behavior.

      SQL> create table t2 (id number);

      Table created.

      SQL> insert into t2 values (1);

      1 row created.

      SQL> Exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

      PL/SQL procedure successfully completed.

      SQL> select m.TABLE_OWNER,
      m.TABLE_NAME,
      m.INSERTS,
      m.UPDATES,
      m.DELETES,
      m.TRUNCATED,
      m.TIMESTAMP as LAST_MODIFIED,
      round((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0),2) as EST_PCT_MODIFIED,
      t.num_rows as last_known_rows_number,
      t.last_analyzed
      From dba_tab_modifications m,
      dba_tables t
      where m.table_owner=t.owner
      and m.table_name=t.table_name
      and table_owner not in ('SYS','SYSTEM')
      and (m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0) > 10
      order by timestamp desc;

      no rows selected

      SQL> exec dbms_stats.gather_table_stats(user,'T2');

      PL/SQL procedure successfully completed.

      SQL> insert into t2 values (2);

      1 row created.

      SQL> Exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

      PL/SQL procedure successfully completed.

      SQL> select m.TABLE_OWNER,
      m.TABLE_NAME,
      m.INSERTS,
      m.UPDATES,
      m.DELETES,
      m.TRUNCATED,
      m.TIMESTAMP as LAST_MODIFIED,
      round((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0),2) as EST_PCT_MODIFIED,
      t.num_rows as last_known_rows_number,
      t.last_analyzed
      From dba_tab_modifications m,
      dba_tables t
      where m.table_owner=t.owner
      and m.table_name=t.table_name
      and table_owner not in ('SYS','SYSTEM')
      and (m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0) > 10
      order by timestamp desc;

      TABLE_OWNER TABLE_NAME INSERTS UPDATES DELETES TRU LAST_MODI EST_PCT_MODIFIED LAST_KNOWN_ROWS_NUMBER LAST_ANAL
      --------------- --------------- ---------- ---------- ---------- --- --------- ---------------- ---------------------- ---------
      CYRILLE T2 1 0 0 NO 26-OCT-17 100 1 26-OCT-17

  3. Unfortunately, your query uses the DBA_TAB_MODIFICATIONS view which is only populated for the tables with the MONITORING attribute… So it will probably give inadequate results for most of the people.

    One should use something like the one below to get more accurate results:

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

    — FOR SELECTED SCHEMAS (AND EVEN THE OBJECTS IN THE SCHEMAS)
    DECLARE
    ListOfStale dbms_stats.ObjectTab;
    ListOfSchemas dbms_stats.ObjectTab:=dbms_stats.objecttab();
    BEGIN
    ListOfSchemas.extend(2);
    ListOfSchemas(1).ownname:=’SCOTT’;
    ListOfSchemas(2).ownname:=’HR’;
    ListOfSchemas(2).objname:=’C%’;
    dbms_stats.gather_database_stats(objlist=>ListOfStale, obj_filter_list=>ListOfSchemas, options=>’LIST STALE’);
    FOR i in ListOfStale.FIRST..ListOfStale.LAST
    LOOP
    dbms_output.put_line(ListOfStale(i).ownname||’.’||ListOfStale(i).ObjName||’ ‘||ListOfStale(i).ObjType||’ ‘||ListOfStale(i).partname);
    END LOOP;
    END;

    1. Like I mentionned “For this query to work you will need the MONITORING to be activated on your tables, this is automatically the case since 11g.”

      Thanks for your input, I appreciate it.

  4. The Main Query before the update seems to work fine for me, however, the UPDATE query seems to be throwing an error “ORA-00904”.
    On the otherhand, Ilker Taysi’s procedure doesn’t producre any output.

    1. You have to enable dbms output… Whatever..
      The best query that works for me is the one below. It lists all segments that are stale or does not have any stats at all. It also excludes the objects whose stats is deliberately locked:

      SELECT ‘Total Number of Stale Tables: ‘||COUNT(*) OVER() TOTAL_COUNT, OWNER, TABLE_NAME OBJECT_NAME, ‘TABLE’ OBJECT_TYPE, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, GLOBAL_STATS, USER_STATS, STATTYPE_LOCKED, STALE_STATS
      FROM DBA_TAB_STATISTICS
      WHERE STATTYPE_LOCKED IS NULL AND
      (STALE_STATS IS NULL OR STALE_STATS=’YES’) AND
      OWNER NOT IN (‘ANONYMOUS’, ‘CTXSYS’, ‘DBSNMP’, ‘EXFSYS’,’LBACSYS’,’MDSYS’,’MGMT_VIEW’,’OLAPSYS’,’OWBSYS’,’ORDPLUGINS’,’ORDSYS’,’OUTLN’,’SI_INFORMTN_SCHEMA’,’SYS’, ‘SYSMAN’,’SYSTEM’,’TSMSYS’,’WK_TEST’,’WKSYS’,’WKPROXY’,’WMSYS’,’XDB’ ) AND
      OWNER NOT LIKE ‘FLOW%’
      UNION ALL
      SELECT ‘Total Number of Stale Indexes: ‘||COUNT(*) OVER() TOTAL_COUNT, OWNER, INDEX_NAME OBJECT_NAME, ‘INDEX’ OBJECT_TYPE, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, GLOBAL_STATS, USER_STATS, STATTYPE_LOCKED, STALE_STATS
      FROM DBA_IND_STATISTICS
      WHERE STATTYPE_LOCKED IS NULL AND
      (STALE_STATS IS NULL OR STALE_STATS=’YES’) AND
      OWNER NOT IN (‘ANONYMOUS’, ‘CTXSYS’, ‘DBSNMP’, ‘EXFSYS’,’LBACSYS’,’MDSYS’,’MGMT_VIEW’,’OLAPSYS’,’OWBSYS’,’ORDPLUGINS’,’ORDSYS’,’OUTLN’,’SI_INFORMTN_SCHEMA’,’SYS’, ‘SYSMAN’,’SYSTEM’,’TSMSYS’,’WK_TEST’,’WKSYS’,’WKPROXY’,’WMSYS’,’XDB’ ) AND
      OWNER NOT LIKE ‘FLOW%’;

        1. Hi Cyrille,

          Optimiser is suddenly using bad execution plan ( after application restart).

          i used ur query to find any stale stats..but no luck.
          stats are showing recent date.
          to avoid business impact i created base plan for that sql_id to use good plan(force optimizer to use specific hash plan).

          can you help me to find bottleneck in this case?

          how Optimizer suddenly using bad hash?

          there is no change in sql script.
          all stats are up to date.

  5. I like you script because i can use a different percent of stale. No sure if that’s the case of using dbms_stats

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.