AnalyzeStatsUsageReport Stored Procedure | Teradata Vantage - 17.10 - AnalyzeStatsUsageReport - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Application Programming Reference

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1090-171K
Language
English (United States)

Displays a summary of events recorded from a prior call to the AnalyzeStatsUsage external stored procedure (see AnalyzeStatsUsage).

Syntax

REPLACE PROCEDURE TDSTATS.AnalyzeStatsUsageReport (
  IN AnalysisId BIGINT
)
  ...
;

Syntax Elements

AnalysisId
Result ID from a prior call to the AnalyzeStatsUsage external stored procedure.

Output

Column Description
EventType Possible values:
  • M means missing statistic whose initial collection is recommended.
  • S means stale existing statistic whose recollection is recommended.
  • U means used statistic during query optimization.
EventDescription Text description of the event or action.
EventFreq Frequency of the event across the query log.
EventTimeStamp Time in which the event occurred.
DatabaseName Database on which the analysis event was recorded.
TableName Table on which the analysis event was recorded
IndexNumber Index number on which the event was recorded.
The value is NULL for non-index objects.
IndexName User assigned name for the index, if any.
FieldNames Column or expressions on which the event was recorded.
Multiple names are comma separated.
MissingCritical Possible values:
  • Y means missing statistics were deemed critical and have been stored in the TDSTATS.StatsTbl table.
  • N means missing statistics were not deemed critical and have only been recorded in the TDSTATS.AnalyzerHistoryTbl table.
MissingRank Relative rank among all missing events.
Approved Flag indicates the corresponding collection is approved for subsequent calls to the PrepCollect external stored procedure.
This value is valid only for those statistics where the MissingCritical value is Y. For more information, see the MissingCritical column.
Stale Possible values:
  • Y means statistic is stale and needs to be refreshed.
  • N means statistic is not stale or cannot be determined.
EstPercentDataChange Estimated percent of data changed since the last collection.
EstPercentDataChange returns NULL when the EventType value is M or when the EstPercentDataChange value is not a reliable estimate.
Age Number of days since the statistic was last collected.
Threshold Dictionary defined THRESHOLD setting for the statistic encoded as a text signature.

The Threshold column may be defined with the Age and EstPercentDataChange column settings. For details, see the EstPercentDataChange and Age columns.

CardEstErrFreq Number of occurrences of cardinality estimation error for this observed event.
AvgImportance Average recorded importance across query log.
MinImportance Minimum recorded importance across query log.
MaxImportance Maximum recorded importance across query log.
MarkedInvalid Flag indicates that the statistic is no longer usable as determined by the query optimizer.
MarkedSummaryOnly Flag indicates that only summary statistics were needed during query optimization. For more information about the SQL COLLECT STATISTICS SUMMARY option, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
NeedsAutomation Possible values:
  • Y means that the analyzed existing statistic is not automated
  • N means that the analyzed existing statistic is automated

To automate the statistic, see AutomateStats.

StatsId Dictionary assigned ID for the statistic as recorded in the DBC.StatsTbl.StatsId column.
StatsName User assigned name for the statistic, if any.
SCOID Automated Statistic Management assigned ID for the statistic as recorded in the SCOID column of the TDSTATS.StatsTbl table.
AnalysisId Automated Statistic Management assigned ID for the history results from this operation.

Usage Notes

For each distinct event, the AnalyzeStatsUsage external stored procedure reports the total number of occurrences across the specified logged period along with the name or ID of the object on which the event applies.

Returning Result Set

The output of this external stored procedure is in the form of a stored procedure dynamic result. That is, the external stored procedure can return result sets to the client application or to the caller of the external stored procedure (in addition to consuming the result sets itself) upon completion of the external stored procedure.

For more information about stored procedure dynamic result sets, see Teradata Vantage™ - SQL External Routine Programming, B035-1147.

Example: Using AnalyzeStatsUsageReport

The following example shows how to report a summary of events recorded on the Personnel database from a prior invocation of AnalyzeStatsUsage.

CALL TDSTATS.AnalyzeStatsUsageReport(2);
 *** Procedure has been executed.
 *** Warning: 3212 The stored procedure returned one or more result sets.
 *** Total elapsed time was 1 second.
 *** ResultSet# 1 : 3 rows returned by "TDSTATS.ANALYZESTATSUSAGEREPORT".
EventType  EventDescription          DataBaseName   TableName  IndexNumber   FieldNames
---------  -----------------------   ------------   ---------  -----------   ----------------
M          MISSING STATS ON INDEX    PERSONNEL      CHARGES       1          EMPNO, PROJ_ID
M          MISSING STATS ON INDEX    PERSONNEL      CHARGES       4          PROJ_ID
S          STALENESS DETECTED        PERSONNEL      EMPOYEE       ?          NAME