All Forums Database
Srichakra 15 posts Joined 11/11
29 May 2015
Collect stats taking long time

Hi Team,

One of my table containing 13,289,138,432 records with 26 columns and indexes are like below


My Query :

* It is taking 4 hours to take collect stats on this table..How to improve the performance on it. Why it is taking that much time ,How can I check the improvement areas ??
* Do I need to add any indexes ??

Srichakra 15 posts Joined 11/11
31 May 2015

Hi Team,
Can you please respond on it .. Last week it took 4hrs 55 mins to complete collect stats on single table.

dnoeth 4628 posts Joined 11/04
01 Jun 2015

How many stats exist for that table?
Single- or multi-column?
Sample stats?
What's your Teradata release?
How are the stats recollected, individually or at table level?


matterwaves 2 posts Joined 12/05
04 Jun 2015

Since it is a fairly large table (and hence i'm assuming it to have fair skew) , i would ask recommend you to collect sample stats.  We have tables with 54 billion rows and we collect sample stats once every fortnight. I'm surprised to see some of the stats absolutely exact

Srichakra 15 posts Joined 11/11
05 Jun 2015

Sorry dieter and Matterwaves for alte reply from me,
Actually we have prcedure to take collect stats ,It will take stats on all columns indicisually and it also check for the partition columns to take stats on it.
we are checking dbadmin_procs.Stats_Log inthis table ,if it stats already exsit for this table  then we are deleting and entering new stats .
I have checked the SKEW factor for this its 4.54 only.
We are not taking sample stats,We are taking on entire table that too weekend only.
Realease 14.10
1) Will it work to take stats on sample data ?
2)Secondary index is not recomenderd i guess becasue of SUB TABLE mechanism.
Please suggest me on it.


dnoeth 4628 posts Joined 11/04
05 Jun 2015

Hi Srichakra,
in 14.10 it's not recommended

  • to collect stats individually
  • to drop and recreate stats

There's an Orange Book on the new functionality in TD14.10, additionally have a look at:
Statistics Collection Recommendations – Teradata Database 14.10
Statistics Threshold Functionality 101
Easing Into Using the New AutoStats Feature
Regarding SAMPLE, in most cases this will be OK (in fact the optimizer starts doing sample stats automatically when you don't drop/recreate stats), there's just one special case where you need to take caution:
When sample statistics go wild
You can simply do a SHOW STATS VALUES ON column, collect SAMPLE stats (in 14.10 you must specify the sample size), do another SHOW STATS VALUES ON column and compare both. If the sample is too bad, you don't have to recollect full stats again, simply resubmit the result of the 1st SHOW.


Srichakra 15 posts Joined 11/11
11 Jun 2015

Thanks dnoeth for your quick response.
I just want to know the roles and responsibilities of a person in IT industry with respective of Teradata Collect stats..
As a developer I am just using Collect stats on **** Syntax from my end ,what about the DBA responsibility on it..Teradata by default refresh and store the stats information into data dictionaries or any manual process.. In my case my DBA completely working on dbadmin_procs.Stats_Log only,What exactly it is..I tried to access it but don't have access on it..What to do..Can anyone expalin breifly or send any links for this.

dnoeth 4628 posts Joined 11/04
11 Jun 2015

Re-collecting stats is not done automatically, there's usually an automated process for it.
In your case this seems to be done based on "dbadmin_procs.Stats_Log" which is not provided by Teradata...


Srichakra 15 posts Joined 11/11
11 Jun 2015

oh ok ...Thanks dieter

You must sign in to leave a comment.