Preventing Statistic Overcollection - Teradata Viewpoint - Teradata Workload Management

Teradata® Viewpoint User Guide

Product
Teradata Viewpoint
Teradata Workload Management
Release Number
16.50
16.20
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
yqb1520516846011.ditamap
dita:ditavalPath
yqb1520516846011.ditaval
dita:id
B035-2206
lifecycle
previous
Product Category
Analytical Ecosystem

The Stats Manager portlet can help prevent unnecessary statistic collection which may be contributing to an inefficient use of system resources. You may be collecting:

  • Statistics that are not needed or provide no value
  • Statistics that are needed but collected too frequently

Ensuring Unneeded Statistics Are Not Collected

Using the Stats Manager portlet, you can identify statistics that are not used by the Optimizer and stop collection of those statistics.
  1. Enable Teradata Database query logging with the USECOUNT option.
  2. Create an analyze job.
    • Enable the option to evaluate statistics to determine if they should be collected or deactivated.
    • Set the length of time a statistic is not used in order for it to be considered inactive and recommended for deactivation.
  3. Manually run or schedule the analyze job to run.

    The analyze job generates deactivate recommendations and these statistics are no longer collected by collect jobs.

Ensuring Statistics Are Collected at the Right Frequency

You can specify collect settings thresholds to decrease the frequency of statistics collection.

  1. Determine the statistics that are collected too frequently. For example, these could be statistics for data that is updated infrequently but the statistics are collected daily or are lower priority statistics that require additional time to collect.
  2. From the Statistics tab, drill down to locate the statistic.
  3. Edit the collect settings for the statistic.
    • Change the collect age threshold, data change thresholds, or both.
    • Select system-defined or user-defined thresholds to determine when to collect or skip collecting a statistic. System-defined thresholds are recommended initially, however user-defined thresholds provide more control over collection thresholds.
  4. [Optional] Review the collect job reports to verify that the statistics are skipped as expected based on the thresholds you set.