CleanupStats Stored Procedure | Application Programming Reference | Vantage - CleanupStats - Advanced SQL Engine - Teradata Database

Application Programming Reference
Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
cpn1571792172880.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1090
lifecycle
previous
Product Category
Teradata Vantage™

Identifies statistics not used by query optimization for a specified period of time according to the system maintained activity counters and optionally reactivates them.

Definition

REPLACE PROCEDURE TDSTATS.CleanupStats
 (IN OlderThan      TIMESTAMP(6) WITH TIME ZONE,
  IN  DatabaseName   VARCHAR(128) CHARACTER SET UNICODE,
  IN  TableName     VARCHAR(128) CHARACTER SET UNICODE,
  IN ObjectListName VARCHAR(128) CHARACTER SET UNICODE,
  IN  MarkInactive   CHAR(1) CHARACTER SET LATIN,
  OUT AnalysisId     BIG,
  OUT NumEvents      INTEGER)
           .
           .
           .
;

Input Parameters

Parameter Description
OlderThan Time. OlderThan identifies statistics that have not been used since the time you specify as inactive.
DatabaseName Name of the database. DatabaseName limits the cleanup operation to statistics within the database you specify.

This input parameter may contain wildcard characters (%, _). For more information about wildcard characters, see “Usage Notes.”
TableName Name of the table within the specified DatabaseName. TableName limits the cleanup operation to the statistics defined on the table you specify within DatabaseName.

This input parameter may contain wildcard characters (%, _). For more information about wildcard characters, see “Usage Notes.”
ObjectListName Name of the object list. ObjectListName 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 cleaned.

For more information about this input parameter, see “Usage Notes” or CreateObjectList.
MarkInactive Possible values:
  • Y. If you specify Y, statistics are automatically marked and not considered in subsequent calls to the PrepCollect external stored procedure.

    Any statistics identified for reactivation are automatically reactivated for PrepCollect. For more information, see PrepCollect.

  • N. If you specify N, statistics remain in the same active or inactive state. You must call InactivateStat to inactivate the statistics or ActivateStat to reactivate them. For more information on these external stored procedures, see InActivateStat and ActivateStat. This is the default.

Usage Notes

The CleanupStats results are written to the AnalyzerHistoryTbl table. To display the results, you can call CleanupStatsReport.

Enabling Object Use Counts for Statistics

Before calling CleanupStats, you must enable object use counts for statistics for queries accessing the specified DatabaseName and TableName.

The USECOUNT logging option in the BEGIN QUERY LOGGING statement must be enabled long enough to evaluate the value specified for the OlderThan input parameter.

Identifying 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 identify only the statistics not used for query optimization for a ... You must specify a value for ...
particular database DatabaseName
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.

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.

Output Parameters

Parameter Description
AnalysisId Results ID for all events generated by this operation.
NumEvents Number of statistics inactivated or reactivated as a result of the cleaning.

Example: Using CleanupStats

The following example shows how to identify the statistics not used on the Personnel.Employee database in the last year.

CALL TDSTATS.CleanupStats(,(CAST(CURRENT_DATE - INTERVAL '1' YEAR AS TIMESTAMP(6)), 'Personnel', 'Employee', NULL, 'N', AnalysisId, NumEvents);
*** Procedure has been executed.
*** Total elapsed time was 1 second.
AnalysisId   NumEvents
----------   ---------
         6           3

Related Topics

For more information on ... See ...
displaying the CleanupStats results report CleanupStatsReport.
the SQL BEGIN QUERY LOGGING statement and the USECOUNT logging option Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.