Displays a summary of events recorded from a prior call to the AnalyzeStatsUsage external stored procedure (see AnalyzeStatsUsage).
REPLACE PROCEDURE TDSTATS.AnalyzeStatsUsageReport ( IN AnalysisId BIGINT ) ... ;
- Result ID from a prior call to the AnalyzeStatsUsage external stored procedure.
|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.
|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.
|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.|
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.|
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