15.10 - AnalyzeStatsUsageReport - 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 call to the AnalyzeStatsUsage external stored procedure (see “AnalyzeStatsUsage” on page 518).

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

Parameter

Description

AnalysisId

Results ID from a prior call to AnalyzeStatsUsage external stored procedure.

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

 

Parameter

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.

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

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

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

    Note: 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 SQL Data Definition Language.

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

    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.

    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