What is the DIAGNOSTIC HELPSTATS ON FOR SESSION Command in Teradata?
Gathering statistics is a crucial aspect of optimizing performance.
Part of the Teradata Statistics and Cardinality Estimation guide.
Collecting statistics requires resources, and it matters whether they are used only once in the ETL process or cover multiple queries’ workloads simultaneously. Fortunately, Teradata’s recent versions can be configured so that the “COLLECT STATISTIC” statement runs only when the data demographics change significantly. This conditional collection enables us to add statistics to specific script locations that were previously impossible or impractical. However, it fails to address all issues arising from decentralized statistics collection. Collecting statistics always involves a tradeoff between collection costs and the cost of a suboptimal execution plan for queries. Key factors to consider are:
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book- For which column(s) should statistics be collected?
- What kind of statistics should be collected (full or sample statistics; random AMP sampling is an optimizer estimation mechanism, not a third COLLECT mode)?
- How often should statistics be collected?
Prioritizing the gathering of statistics is crucial for successful performance tuning. This preliminary task enables the Optimizer to formulate an optimal execution plan. Occasionally, this may be the sole necessary tuning action. Teradata offers a command that displays recommended statistics that may improve the execution plan.
What Is DIAGNOSTIC HELPSTATS ON FOR SESSION?
Enabling “DIAGNOSTIC HELPSTATS ON FOR SESSION” provides a practical option. The Optimizer displays recommended statistics for the query at the conclusion of the Explain statement’s output. Here is an example:
DIAGNOSTIC HELPSTATS ON FOR SESSION;
EXPLAIN SELECT * FROM Customer WHERE CustomerId IN
( SELECT CustomerId FROM Sales );
BEGIN RECOMMENDED STATS FOR THIS Relation->
-- "COLLECT STATISTICS COLUMN (CustomerId) ON DWHPRO.Customer"
(High Confidence)
-- "COLLECT STATISTICS COLUMN ( CustomerId ) ON DWHPRO.Sales"
(High Confidence)
<-END RECOMMENDED STATS
Determining the efficacy of the suggested statistics is at our discretion. We may opt to incorporate the statistics incrementally and promptly evaluate the query after each adjustment to assess any improvements. Alternatively, we could incorporate all recommended statistics at once and subsequently eliminate them iteratively, retaining only those that result in a superior execution plan.
My Approach
I add statistics with “high confidence” and primarily single-column statistics to quickly obtain results. Then, I assess the suggested multi-column statistics with “high confidence,” and only after that do I consider statistics proposals with “medium confidence.” Blindly accepting all optimizer suggestions is not advisable; statistics should only be added to enhance the execution plan. A query test run and comparison of required resources before and after the changes can be useful for evaluating the effectiveness of changes. One approach is to use a specific query band for each test, analyzing how changes affect CPU seconds, skew, and IOs in the table DBC.DBQLOGTBL. Experience has shown that redundant statistics do not impact the plan’s quality. Although DIAGNOSTIC HELPSTATS ON FOR SESSION provides an initial overview, it cannot replace detailed analysis later.
Traps When Using DIAGNOSTIC HELPSTATS ON FOR SESSION
Not all proposed statistics enhance the execution plan. Some recommendations may be redundant, incurring only additional costs for data collection without impacting the execution plan.
DIAGNOSTIC HELPSTATS ON FOR SESSION has the drawback of solely indicating the statistics to gather without specifying the method (full or sample); thus, further scrutiny is required to obtain comprehensive information.
What Is the Teradata Command HELPSTATS ON FOR SESSION?
Should statistics always be collected for all proposals?
No, because collecting statistics consumes resources. We only collect statistics if they improve our queries.
Does this command give us information about which statistics are more important than others?
Yes, by including the confidence level: “high confidence”, “medium confidence”, “low confidence”.
How do we know what kind of statistics to create?
The Optimizer does not provide this information. So it is up to us to determine if a column is skewed and then choose the appropriate type of statistic.
Does DIAGNOSTIC HELPSTATS ON FOR SESSION provide all required statistics for a table?
No, the proposed statistics only apply to the SQL query in question.
Can I use DIAGNOSTIC HELPSTATS ON FOR SESSION for Volatile Tables?
Teradata does not store the definition of volatile tables in the data dictionary. The optimizer’s suggestions help add statistics to volatile tables. Recommendations do not necessarily repeat: in the Teradata Database 20.00.28.81 fixture, the recommendation disappeared after collection and the estimate became high confidence. Absence from DBC.StatsV does not imply the optimizer cannot use session-local statistics.
Need help with query performance or platform costs?
Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book
I usually also run these commands along with
DIAGNOSTIC VERBOSEEXPLAIN ON FOR SESSION
to get a recommendation for collecting statistics for a specific step.
Thanks for this hint. I will add the command to the article.