15.10 - AnalyzeStatsReport - Teradata Database

Teradata Database Application Programming Reference

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1090-151K

Displays a summary of events recorded from a prior invocation of the AnalyzeStats external stored procedure.

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

Parameter.

Description

AnalysisId

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

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 SQL External Routine Programming.

 

Parameter

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.

    Note: 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.

    Note: 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.

    Note: 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.

    Note: 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” on page 488.

    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.

    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