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

We know that Teradata statistics can significantly impact how efficiently SQL queries are executed.

So we need a protocol that precisely provides us with this information.

Statistics can be collected on various objects (tables, join index, etc.). When we create statistics, as a performance tuner, we want to know if they are being used because statistics that are not needed cause unnecessary resource consumption collecting them.

The Solution: Logging the Detailed Information with StatUseCountV

Teradata offers us the possibility to find out precisely this information. For this, DBQL UseCount logging and StatusUsage logging must be activated.

Since the USECOUNT logging consumes resources, we recommend not activating it for all databases but only for analysis purposes on the specific ones.

Enabling the logging for StatUseCountV and Object Use Counts

In our example, we enable logging only 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;

Please consider that StatusUsage Logging can’t be defined on the database level. Any attempt will lead to this 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

In the example below, you can see how to identify the usage of statistics in the DBC.StatUseCountV view:

COLLECT STATISTICS COLUMN(a) ON DWHPRO.StatisticsExample;

SELECT * FROM  DWHPRO.StatisticsExample
WHERE a = 1;

SELECT * FROM dbc.StatUseCountV;