All Forums Database
teradatauser2 236 posts Joined 04/12
28 Jul 2012
Query to find tables with no stats defined/old statistics

Hi,

I need a query to find the the tables in a database for which there are no stats defined yet (This will help me to tell uses to define stats). Also, i want to list down the tables in a database for which the stats are old (say 1 month old).

I dont want to do a help stats on every tables and find the details (practically not possible). Is there a query for this.

This can be done using the dbc.tvfields and dbc.index (my guess), but i m not sure how.

If someone has the query , then it will be very helpful.

Thanks

teradatauser2 236 posts Joined 04/12
28 Jul 2012

Hi ,

i have a little update on this. i could figure out one quey for this..but i have another question on this..

The single column statistics are stored in dbc.tvfields and the statistics of the indexes (even multicolumn) is stored in dbc.indexes.

If i have stats on multicolumn but they are not indexes, they are not stored in dbc.tvfields and since they are not indexes, they are stored in dbc.indexes. So, where are they stored ?

I wish to find list of tables having no indexes defined

Thx!

 

dnoeth 4628 posts Joined 11/04
29 Jul 2012

You sould check my StatsInfo view:

http://developer.teradata.com/node/9598

Based on this it's like:

SELECT databasename, tablename, MAX(CollectTimestamp)
FROM statsinfo
GROUP BY 1,2
HAVING MIN(MissingStats) = 'Y'
OR MAX(CollectTimestamp) < CURRENT_TIMESTAMP - INTERVAL '30'DAY

Dieter

Dieter

teradatauser2 236 posts Joined 04/12
29 Jul 2012

Hi Dnoeth,

Your work is great !!

I just tried one code, i saw in some of your post and tweaked a little to macth my requirement. Its gives the same result as you statsinfo view..just wanted to share..comments welcome

SELECT c.databasenamei AS databasename , b.tvmnamei AS tablename
FROM dbc.tvfields a , dbc.tvm b , dbc.dbase c
WHERE a.tableid = b.tvmid
AND b.databaseid = c.databaseid
AND a.fieldstatistics IS null
AND databasename = 'databasename'
-- and b.tablekind='T'
and (databasename,tablename)
not in (
SELECT c.databasenamei AS databasename , b.tvmnamei AS tablename
FROM dbc.tvfields a , dbc.tvm b , dbc.dbase c
WHERE a.tableid = b.tvmid
AND b.databaseid = c.databaseid
AND a.fieldstatistics IS not null
AND databasename = 'databasename'
-- and b.tablekind='T'

union

sel tp.databasename,tp.tablename
from
(
SELECT d.databasenamei AS databasename , t.tvmnamei AS tablename,
MAX(SUBSTR(i.IndexStatistics, 1, 80)) AS STATS
FROM
dbc.dbase d
JOIN dbc.tvm t
ON d.databaseid = t.databaseid
JOIN dbc.tvfields c
ON t.tvmid = c.tableid
JOIN dbc.Indexes i
ON c.tableid = i.tableid
AND c.fieldid = i.fieldid
/*** Add list of DBs to exclude***/
WHERE d.DatabaseName IN ('databasename')

AND
(i.IndexType IN ('M','S','K','U','H','O','V','P','Q'))

-- and t.tablekind='T'
group by 1,2
HAVING STATS IS not NULL
) tp
)
group by 1,2

 

ashikmh 11 posts Joined 02/12
06 Mar 2013

Hi Dnoeth,
I was looking for your statsinfo views and new version of your stats query which has been fixed all the bugs so for.
I tried with no luck on all of your blogs here, even in http://developer.teradata.com/node/9598
I looked at John Hall's TeradataForum, but that is old version.
Please help me,
Thanks.
 

dnoeth 4628 posts Joined 11/04
06 Mar 2013

I just noticed that the attachement is lost, i'll send it to you directly.
There was some maintenance going on this week and apparently it's still not back to normal, you were the guy with the empty comment on my blog :-)
Dieter

Dieter

ashikmh 11 posts Joined 02/12
08 Mar 2013

Hi Dieter,
Yesterday, I got those attachments in http://developer.teradata.com/node/9598, thanks for that, also it looks like sites are back to normal.
The other day, I was trying to put comment on your blog, but it was not allowing me. But I accidentally pressed submit button and it registered an empty comment :-)
-Thanks

Niesh20us 78 posts Joined 06/13
04 Dec 2013

Hi Deiter,
How are you?
I was going through above blog. Actually i dont have access to dbc.tvfields.
Is there a way i can get the missing collect stats table and their index in the same query.
Thanks,
Nilesh
 

dnoeth 4628 posts Joined 11/04
04 Dec 2013

Hi Nilesh,
if you need to get that info you have to talk to your DBA to grant access.

Dieter

madhavkumars 1 post Joined 10/10
25 Jan 2014

Hi Dieter,
Thanks for putting together the query. When I execute the below query
 

SELECT databasename, tablename, CollectTimestamp

FROM statsinfo
I am getting null values for collecttimestamp column. I know that we have collected stats on the tables. Any suggestions ?

 

dnoeth 4628 posts Joined 11/04
25 Jan 2014

Did you run my the version for TD13 on a TD14 system?
The old statsinfo will still run and return NULL for everything, as stats are no longer stored in tvfields and indices.
You have to use the TD14 version found at:
http://developer.teradata.com/node/182743

Dieter

jonathanofsmith 1 post Joined 02/14
10 Feb 2015

FWIW, here is a query which we recently developed to ID tables that are in need of stats;
 

SELECT

 (TABLEV.databasename||'.'||TABLEV.tablename) AS TableName

, COALESCE(TO_CHAR(MAX(COALESCE(STATSV.lastcollecttimestamp,STATSV.createtimestamp)), 'YYYY-MM-DD'), 'No Stats') AS LastStatsCollection

, CAST(SUM(TABLEV.currentperm) AS DECIMAL(38,0)) AS TableSpace

FROM

DBC.tablesizeV TABLEV

LEFT OUTER JOIN DBC.statsv STATSV

ON STATSV.databasename = TABLEV.databasename

AND STATSV.tablename = TABLEV.tablename

WHERE

TABLEV.databasename NOT IN('CrashDumps','DBC') --enter databases to exclude here

AND TABLEV.tablename NOT IN (--enter tables to exclude here)

GROUP BY

1

HAVING

SUM(TABLEV.currentperm) > 1000000 --limit this to only return larger tables

AND (

MAX(COALESCE(STATSV.lastcollecttimestamp,STATSV.createtimestamp)) < current_date - 30 --adjust your days here

OR

laststatscollection = 'No Stats'

)

ORDER BY

3 DESC

;

 

This was run on TD14

Ibrahim 307 1 post Joined 07/15
29 Jul 2015

Dear dnoeth,
If i want to view how many time that the user use Stored Procedure.

<p>Many Thanks &amp; Best Regards<br /> ---------------------------------------<br /> Ibrahim Khalil Ibrahim | TE |Datawere House Development Team<br /> Mobile: 01001990327<br /> E: <a href="mailto:ibrahim.ibrahim@te.eg">ibrahim.ibrahim@te.eg</a> | W: te.eg<br /> K28 Cairo-Alex Desert Road-Smart Village-B7-LR 46</p>
dnoeth 4628 posts Joined 11/04
29 Jul 2015

You need to query dbc.AccessLogV (preferred) or join dbc.QryLogObjectsV and dbc.QryLogV to find out which user used any kind of object. (Hopefully either Access or Query Log is enabled)

Dieter

You must sign in to leave a comment.