Collect Stats

Database
Enthusiast

Collect Stats

Collecting stats can be executed with collecting a sample percentage which is supposed to be a good way to get stats for very large tables, but this is set somewhere which is system wide, ie when collecting stats and using sampling, this percentage value will be used each time sampling is used on small or large tables.

Does anyone know where I can find this percentage value and how to change this if needed, as I would like to test this in our environment to see the performance difference..?

thanks
5 REPLIES
Enthusiast

Re: Collect Stats

Hi Luke,
reading from the manuals: " the system automatically determines the appropriate sample size to generate accurate statistics for good query plans and performance."

Basically, when you collect stats on table using sample, Teradata itself reads the table and collects "incremental" statistics until their values are stable enough.

Once you have collected statisdtics on a column using sample, recollection are also sampled. If you want to change the way you are collecting them, you have to drop them and recollect in the "standard" way.

Hope this helps, bye,
TDUser

Enthusiast

Re: Collect Stats

Hi TDuser,

yes thank you that has helped, I saw that in the documentation but did not understand fully, but now do

Senior Apprentice

Re: Collect Stats

Hi Luke,
the global flag is set by dbscontrol -> internal field 46: CollectStatsSample

On a session level it's:
DIAGNOSTIC "COLLECTSTATS, SAMPLESIZE=xx" ON FOR SESSION;

For a singe Collect Stats:
DIAGNOSTIC "COLLECTSTATS, SAMPLESIZE=xx" ON FOR REQUEST;
followed by a "Collect stats using sample..."

Dieter
Enthusiast

Re: Collect Stats

Hi dnoeth,

Thanks so much, this is so helpful, just ideal as you can have the sampling per collection, great.

Is this available in any doc as I could not find anything like this anywhere..?

Senior Apprentice

Re: Collect Stats

Hi Luke,
sorry, but you won't find any documentation about it.
Most DIAGNOSTIC statements are undocumented, but sometimes you can guess the meaning of a keyword :-)

Dieter