AnalyzeStatsReport Procedure | Application Programming Reference | Vantage - 17.10 - AnalyzeStatsReport - 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 invocation of the AnalyzeStats external stored procedure.

Syntax

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

Syntax Elements

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

Output

Column Description
EventType Possible values:
  • M means a missing statistic whose initial collection is recommended.
  • S means a stale statistic whose recollection is recommended.
EventDescription Text description of the event.
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.
For non-index objects, the value is NULL.
IndexName User assigned name for the index, if any.
FieldNames Column or expression names on which the event was recorded.
Multiple names are comma separated.
MissingCritical Possible values:
  • Y means the missing statistic is deemed critical and stored in the TDSTATS.StatsTbl table.
  • N means the missing statistic is not deemed critical and has 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 PrepCollect.
The Approved value is valid only to 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.
The EstPercentDataChange output parameter value is NULL when the EventType value is M or when the EstPercentDataChange output parameter 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.

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

AvgImportance System assigned average importance of the statistic.
NeedsAutomation Possible values:
  • Y means the analyzed existing statistic is not automated.
  • N means analyzed existing statistic is automated.

To automate the statistic, see AutomateStats.

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

Usage Notes

This external stored procedure provides a description of each event along with the name or ID of the objects on which the corresponding statistics are defined or found missing.

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 AnalyzeStatsReport

The following example reports the summary of events recorded on the Personnel database from a prior invocation of AnalyzeStats.

CALL TDSTATS.AnalyzeStatsReport(1);
 *** Procedure has been executed.
 *** Warning: 3212 The stored procedure returned one or more result sets.
 *** Total elapsed time was 1 second.
 *** ResultSet# 1 : 2 rows returned by "TDSTATS.ANALYZESTATSREPORT".
EventType EventDescription        DataBaseName  ObjectName    IdxNum  FieldNames
--------- ----------------------- ------------  -----------   ------- ---------------
M         MISSING STATS ON INDEX  PERSONNEL     CHARGES       1       EMPNO, PROJ_ID
M         MISSING STATS ON INDEX  PERSONNEL     CHARGES       4       PROJ_ID