Preventing Statistic Overcollection - Teradata Viewpoint - Teradata Workload Management

Teradata® Viewpoint User Guide - 23.04

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
Lake
VMware
Product
Teradata Viewpoint
Teradata Workload Management
Release Number
23.04
Published
April 2023
Language
English (United States)
Last Update
2023-05-05
dita:mapPath
cxb1678987903668.ditamap
dita:ditavalPath
tky1501004671670.ditaval
dita:id
B035-2206
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 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.