How to find out if the Teradata Statistics we created for a specific workload are used?
Teradata statistics greatly affect SQL query efficiency.
We require a protocol that can accurately furnish us with 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 StatusUsage 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;StatusUsage 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;
