SelectAutomatedStats Stored Procedure | Teradata Vantage - 17.10 - SelectAutomatedStats - 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 the list of automated statistic definitions that are stored in the TDSTATS database.

Syntax

REPLACE PROCEDURE TDSTATS.SelectAutomatedStats (
  IN DatabaseName   VARCHAR(128) CHARACTER SET UNICODE,
  IN TableName     VARCHAR(128) CHARACTER SET UNICODE,
  IN ObjectListName VARCHAR(128) CHARACTER SET UNICODE
)
  ...
;

Syntax Elements

DatabaseName
Name of the database.DatabaseName limits the display to those statistics defined within the database you specify.
This input parameter may contain wildcard characters (%, _). For more information about wildcard characters, see the usage notes.
TableName
Name of the table within the specified DatabaseName. TableName limits the display to those statistics for the table you specify within DatabaseName.
This input parameter may contain wildcard characters (%, _). For more information about wildcard characters, see the usage notes.
ObjectListName
Name of the object list. ObjecListName allows you to specify a previously defined list of objects (that is, one or more database names or fully qualified table names where each name entry may optionally contain wildcard characters) whose statistics should be displayed.
For more information about this input parameter, see the usage notes or CreateObjectList.

Output

Column Description
DatabaseName Database in which the statistic is defined.
TableName Table on which the statistic is defined.
ExpressionList List of comma separated columns or expressions on which the statistic is defined.
IndexNumber Dictionary assigned number of the index on which the statistic is defined as recorded in the DBC.StatsTbl.IndexNumber column.
This input parameter is NULL if the statistic is not collected on an index.
IndexName User assigned name for the index, if any.
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 TDSTATS database assigned ID for the automated statistic.
ApprovedStat Possible values:
  • Y means the statistic is approved for collection.
  • N means the statistic is unapproved for collection.
  • D means the statistic is disapproved by the user.
Excluded Possible values:
  • Y means the statistic is defined on an object that has become excluded since the initial automation. For more information, see AddExcludedObject.
  • N means the statistic is NULL.
StatsState Possible values:
  • M means the statistic is missing recommendations.
  • S means the statistic is stale.
  • C means the statistic is current.
StatsOrigin Possible values:
  • U means the statistic is imported from the user by an Automate-related external stored procedure.
  • A means the statistic is automatically recommended by the system.
StatsType Type of statistics identified by the dictionary:
  • B (Base table)
  • V (View)
  • Q (Query)
V and Q statistic types are reserved for future use.
LastCollectTimeStamp Time of when the statistic was last collected.
LastEvaluationTimeStamp Time when the statistics was last submitted for collection by Automated Statistics Management. Depending on the SQL COLLECT STATISTICS THRESHOLD option in effect, the statistic may be recollected.

For more information about the THRESHOLD option, see COLLECT STATISTICS statement in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 or Teradata Vantage™ - Data Dictionary, B035-1092.

SubmissionSkipCnt Number of consecutive runs where the statistic was not submitted for collection due to the time expiration or aborting.
ThresholdSkipCnt Number of consecutive submissions where the statistic did not meet its defined threshold for recollection.

For more information about the SQL COLLECT STATISTICS THRESHOLD option, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 or Teradata Vantage™ - Database Administration, B035-1093.

UserImportance User assigned value for an entry that can be used to influence the priority of the statistic during the PrepCollect operation.

For more information about this external stored procedure, see PrepCollect.

SystemImportance System assigned value that is used in calculating the priority of the statistic during the PrepCollect operation.

For more information on this external stored procedure, see PrepCollect.

ActiveStat Possible values:
  • N means the statistic is marked inactive by the CleanupStats external stored procedure as a result of no observed usage by the Optimizer and is no longer incorporated by the PrepCollect external stored procedure.
  • Y means the statistic is actively used.
LastAccessedByOpt Last recorded statistics usage by query optimizer according to system-wide activity counters.
QryUsageFrequency Number of logged queries observed using the statistic.
MissingFrequency Number of queries observed wanting the missing statistic.
EstPercentDataChange Estimated percent of data changed since the last collection as determined by the most recent call to the Analyzer-related external stored procedure or PrepCollect.

For more information on this external stored procedure, see PrepCollect.

The EstPercentDataChange value is NULL when the StatsState column value is M or when the EstPercentDataChange value is not a reliable estimate.
Age Number of days since the last collected statistic. For more information, see the LastCollectTimeStamp output column.
Threshold Dictionary defined THRESHOLD setting for the statistic encoded as a text signature.

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

EstimateError Indicator that one or more queries observed have cardinality estimation inaccuracy involving the use or absence of this statistic.
EstErrFrequency Number of queries observed with cardinality estimation inaccuracies.
CreateUser User who originally automated the statistic.
CreateTimeStamp Time in which the statistic was originally automated.
LastAlterUser User who last altered the TDSTATS database stored data for the statistic.
LastAlterTimeStamp Time in which the TDSTATS database data for this statistic was last altered.

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.

Wildcard Characters

The following table describes the wildcard characters that the names of some databases, tables, and objects contain.
Character Description
% (PERCENT SIGN) Represents any string of zero or more arbitrary characters. Any string of characters is acceptable as a replacement for the percent.
_ (LOW LINE) Represents exactly one arbitrary character. Any single character is acceptable in the position in which the underscore character appears.

For more information on how to use these wildcard characters, see the SQL LIKE clause in Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.

Displaying Statistics

If you do not specify a value for the following input parameters, the operation is performed on all user created databases in the system.
To display ... You must specify a value for ...
all statistics for a particular database DatabaseName.
all statistics for a particular table both DatabaseName and TableName.
a list of objects that cannot be specified by DatabaseName or TableName

ObjectListName that was previously created by the CreateObjectList and AddObjectListEntry external stored procedures.

ObjectListName usage is mutually exclusive with DatabaseName and TableName . When you specify a value other than NULL for ObjectListName , DatabaseName and TableName must be NULL.