teradata diagnostic helpstats

Statistics are the highest priority performance optimization task on Teradata. There is no way around them, and any sound optimization approach incorporates them as if they were natural.

You should never consider any further optimization method as long as you are not 100% sure that the statistics on the tables used in a query are up to date and complete.

It is quite easy to get a first impression about the  completeness and sufficiency of your statistics by issuing the following diagnostic statement (in SQL Assistant):

DIAGNOSTIC HELPSTATS ON FOR SESSION

After turning statistic diagnostic on, each explain statement executed will deliver some valuable information about the statistics the optimizer could use to create a better execution plan.

It is a good idea to collect all suggested statistics at once and check the execution plan again. If the execution plan improved and the query runtime decreased (or the resource usage decreased),  you could remove step by step the statistics considered to be redundant. Redundancy is both a matter of technical effects and your general knowledge of the nature of the columns or combinations.

After each removed statistic, you should recheck the execution plan by explaining the query again.

This approach helps in adding the missing statistics while it avoids keeping statistics which are not adding any value to the optimizer for the creation of the execution plan. Bear in mind that collecting statistical data on Teradata is a CPU consuming task. It should, therefore, be subject to intelligent decision making and based on a certain degree of selectiveness.

 

Our Reader Score
[Total: 8    Average: 3.8/5]
DIAGNOSTIC HELPSTATS ON FOR SESSION written by Roland Wenzlofsky on March 7, 2014 average rating 3.8/5 - 8 user ratings
SHARE
Next articleTeradata Stale Statistics
Roland Wenzlofsky
Roland Wenzlofsky is a graduated computer scientist and Data Warehouse professional working with the Teradata database system for more than 15 years. He is experienced in the fields of banking and telecommunication with a strong focus on performance optimization.

LEAVE A REPLY

Please enter your comment!
Please enter your name here