Collect stats taking long time

Database
Enthusiast

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

PRIMARY INDEX XNUPI ( A)
UNIQUE INDEX XUSI1 ( B ,C)
INDEX XNUSI2_NUR ( D ,E)
INDEX XNUSI3_NUR( F);

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 ??
8 REPLIES
Enthusiast

Re: Collect stats taking long time

Hi Team,

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

Senior Apprentice

Re: Collect stats taking long time

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?

Enthusiast

Re: Collect stats taking long time

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

Enthusiast

Re: Collect stats taking long time

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.

Thanks

Srichakra




Senior Apprentice

Re: Collect stats taking long time

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.

Enthusiast

Re: Collect stats taking long time

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.

Senior Apprentice

Re: Collect stats taking long time

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...

Enthusiast

Re: Collect stats taking long time

oh ok ...Thanks dieter