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.

The Solution: Logging the Detailed Information with StatUseCountV

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

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;