First you have to install the "Dieter Noeth Collect Views", you can find it by search in T@YS
What is this query doing?
This query just do the following:
- Translate the flags in the views to its meaning (ex. C -> Column , I -> Index) and so on.
- Consider if the column or index is having full stat or sample.
- You can specify the collect duration or since what this collect done.
- Build a collect stat statement based on the above points.
/*Determine which collect stats take a long time or had a stale stats */
select
-- databasename, tablename, columnname, collectdate,collectduration,samplesize
'COLLECT STATISTICS' || case when samplesize is not null then ' USING SAMPLE' when samplesize is null then '' end || ' ON ' || databasename || '.' || trim(tablename) || ' '|| case when statstype='c' then 'COLUMN' when statstype='i' then 'INDEX' when statstype='m' then 'COLUMN' end || ' (' || trim(columnname) || ');'
--*
from {DB Contains Dieter Noeth Collect Views}.stats_details
where
collectduration > '0:30:00.00' -- For collect takes longer than 30 minutes
databasename = '{Database you need to inquire on}'
and tablename = '{Table Name}' -- Tables that you need to inquire, you can hash it if you need to see all tables
and columnname = '{Column Name}' -- COlumn that you need to inquire, you can hash it if you need to see all Column
and tablename not like '%temp%' -- If you need to exclude some tables, you can hash it if you need to see all tables
and cast(collectdate as date) < current_date -5 -- This means get all collect stat that run older than 5 days
order by collectduration ;
