- Renaming a RAC cluster - 27/09/2018
- Stop/Start all RAC databases at once - 26/09/2018
- RMAN Backup script - 08/11/2017
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.
Thank you Cyrille for the share.
Foued
You’re welcome Foued.
Hello Cyrille,
it would be also useful to check table partitions with stale statistics.
Nice report – thanks for sharing.
Regards,
Marko
Hi Marko,
Thanks for your feedback, I updated the article to include the partitions as well.
See you
Created a new table, inserted 1 record. It does not appear in the results of your SQL.
So tables with missing statistics are missing.
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
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;
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.
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.
Thanks Isaac, there was a mistake actually, it is fixed now.
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%’;
Those are the objects that you should really consider gathering the stats for…
Thanks a lot for your input, I will test and add you query to the post.
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.
I like you script because i can use a different percent of stale. No sure if that’s the case of using dbms_stats