Tracking Teradata Statistics Usage with StatUseCountV

How to find out if the Teradata Statistics we created for a specific workload are used?

Teradata statistics greatly affect SQL query efficiency.

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.

Want more practical data engineering analysis like this?

Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. Early subscribers keep launch access before the paid plan launches.

Get the next issue

The Solution: Logging the Detailed Information with StatUseCountV

Teradata enables precise information retrieval by activating DBQL UseCount logging and StatsUsage logging.

Get the next issue by email.

To 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;

Planning or surviving an enterprise data platform migration?

I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.

Subscribe before the paid plan launches and keep launch access.

Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.