Help with COLLECT STATS

General
Enthusiast

Help with COLLECT STATS

Can ny1 plse. ellaborate on Collect Stats..How does it work??

All I know abt it is it increases performance of Queries..But dont know the actual implementation..Can ny1 plse. throw some light on the same..

Also do collect stats work on Volatile tables??

Thanks in advance:)

7 REPLIES
Fan

Re: Help with COLLECT STATS

The optimizer needs fresh stats to be able to create a good plan to run queries, especially complicated queries.  The stats tell the optimizer things like the number of rows in a column and the distribution of the indexed values.

Without stats collected, the PE has to run a random AMP sample before making the plan.  This consumes CPU and it is not as precise as using already collected stats.  As a result it can cause the optimizer to make a bad decision, likely leading to bad performing queries.  This is more true when NUSI's are involved because they are less predictable.

I don't think it's possible to collect stats on a volatile table, but I think you can for global temporary tables.  I haven't looked into this, I just think I remember reading that somewhere recently.

Hope this helps.

- Kyle

Enthusiast

Re: Help with COLLECT STATS

You should typically collect stats on Index columns,Join Column,Filters in where clause.It helps Teradata it in coming up with better execution plan .

Enthusiast

Re: Help with COLLECT STATS

The Teradata Optimizer is a cost based optimizer. It builds the execution plans and picks the "cheapest" one it can find.  Collecting statistics is like putting "price tags" on the data so the optimizer can pick the least expensive, thus fastest, plan.

The optimizer can help you pick the stats to collect.   Execute diagnostic helpstats on for session; then run an explain on the query you want to check the stats for and in most cases you will find that the bottom of the explain plan contains one or more, sometimes several, nearly perfectly formed collect statistics statements on the column(s) or indexes that the optimizer thinks it needs stats on.

This is often a good first step to tuning any query.

With the recommendations in Jigar's post above you should be on your way to faster queries...

Enthusiast

Re: Help with COLLECT STATS

First of all, thanks ppl for ur valuable inputs..

Also, In my Opinion, u can collect stats on Volatile table..Tried it and it worked..whereas in many forums, i heard that u cannot??

Whats the actual scenario?? Ny ideas??

Junior Contributor

Re: Help with COLLECT STATS

Collect stats on volatile tables have been added in TD13.

All details about statistics can be found in the manuals:

SQL Request and Transaction Processing

Chapter 2: Query Rewrite and Optimization

"Optimizer Statistics and Demographics" and the following

Dieter

Enthusiast

Re: Help with COLLECT STATS

Dieter, Can you please provide the link for going through the manual..

Thanks

Enthusiast

Re: Help with COLLECT STATS

http://www.info.teradata.com/edownload.cfm?itemid=102320054 - Teradata 13.10 SQL Request and Transaction Processing manual