How to find out if the Teradata Statistics we created for a specific workload are used?
Teradata statistics greatly affect SQL query efficiency.
Part of the Teradata Statistics and Cardinality Estimation guide.
We need a reliable method to get this information.
Various objects, such as tables and join indexes, can have statistics collected on them. As performance tuners, it is important to confirm their usage since collecting unnecessary statistics can lead to excessive resource consumption.
The Solution: Logging the Detailed Information with StatUseCountV
DBQL USECOUNT and STATSUSAGE can provide evidence of statistics use within the configured scope. Availability was checked with SELECT * FROM DBC.StatUseCountV WHERE 1=0; scoped logging behavior, cache flushing and retention were not tested. Consult the release documentation before enabling the illustrated rules and restore prior rules after analysis. An absent use count does not prove that a statistic is never useful: retention, logging gaps and unobserved workloads matter.
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 bookTo conserve resources, it is advisable to activate USECOUNT logging only for analysis purposes on specific databases rather than for all of them.
Enabling the logging for StatUseCountV and Object Use Counts
We only enable logging for our test database DWHPRO.
-- EnableUse Counts on database DWHPRO only
BEGIN QUERY LOGGING WITH USECOUNT ON DWHPRO;
-- Enable StatsUsage Logging for user DWHPRO_ADMIN
BEGIN QUERY LOGGING WITH STATSUSAGE ON DWHPRO_ADMIN;StatsUsage logging cannot be defined at the database level, and any such attempt will result in an error.
BEGIN QUERY LOGGING WITH STATSUSAGE ON DWHPRO;
REPLACE QUERY LOGGING Failed. [3706] Syntax error: Only USECOUNT option allowed with databases.Example Query with Statistics Logging
Below is an example of how to identify statistics usage in the DBC.StatUseCountV view:
COLLECT STATISTICS COLUMN(a) ON DWHPRO.StatisticsExample;
SELECT * FROM DWHPRO.StatisticsExample
WHERE a = 1;
SELECT * FROM dbc.StatUseCountV;
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