Get collect stats details from Dieter Noeth Collect Views

Here is useful Query to get detailed report and make it a script from Dieter Noeth Collect Views:

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 ;

How to detriment if your sample statistics is suitable

To detriment if your sample statistics is or not (TD 12) you may need to make a HELP STATISTICS statement on you table or column twice, one with full stats and the other after applying the sampling and see the difference

1- To make a help stats on table level:

HELP STATISTICS "tablename";


2- To make a help stats on Column level:

HELP STATISTICS "tablename" Column "columnname";

Collect Statistics using sample in TD 12

There is a new feature in TD 12 that you can specify the sample percent you need before running collect stat.

You can do it on the,

1- session level:

DIAGNOSTIC "COLLECTSTATS, SAMPLESIZE=20" ON FOR SESSION;

The above statment means that all collect stats starment with "USING SAMLE" keyword in the session will be collected with 20% (You can change it to what is suitable for your tables).

2- Request level:

DIAGNOSTIC "COLLECTSTATS, SAMPLESIZE=20" ON FOR REQUEST;

That's means it will applyed only on the next collect statment that follows the diagnostic statment.

How to clean / Purge DBQL Tables

delete from DBC.DBQLObjTbl Where CollectTimeStamp < '2009-10-01 00:00:00';
delete from DBC.DBQLogTbl where LogonDateTime < '2009-10-01 00:00:00';
delete from DBC.DBQLSqlTbl Where CollectTimeStamp < '2009-10-01 00:00:00';
delete from DBC.DBQLExplainTbl where CollectTimeStamp < '2009-10-01 00:00:00';
delete from DBC.DBQLStepTbl Where CollectTimeStamp < '2009-10-01 00:00:00';
delete from DBC.DBQLSummaryTbl where CollectTimeStamp < '2009-10-01 00:00:00';

Welcome to my teradata world !!

This is my first words in my new Teradata blog,

Actually i worked for a years as Teradata Database Administrator (DBA) and had a lot of experience, problem solving and tips and tricks in Teradata administration on going

I believe that we have to share our knowledge for all as the knowledge in increasing when your share it and decreasing when your keep it for your self

I will do my best to put all my knowledge on your hands.

and I hope that being useful for you

Mahmoud Karam, PMP, Teradata Certified Master