AnalyzeStats Stored Procedure | Application Programming Reference | Vantage - 17.05 - AnalyzeStats - Teradata Database

Teradata Vantage™ - Application Programming Reference

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1090-170K

Analyzes a set of specified objects to find events where statistics management tasks on them might be improved.

Definition

REPLACE PROCEDURE TDSTATS.AnalyzeStats(
 IN DatabaseName    VARCHAR(128) CHARACTER SET UNICODE,
 IN TableName       VARCHAR(128) CHARACTER SET UNICODE,
 IN ObjectListName  VARCHAR(128) CHARACTER SET UNICODE,
 IN NewerThan     TIMESTAMP(6) WITH TIME ZONE,
 IN MarkApproved    CHAR(1) CHARACTER SET LATIN,
 OUT AnalysisId     BIGINT,
 OUT NumEvents      INTEGER)
           .
           .
           .
;

Input Parameters

Parameter Description
DatabaseName Name of the database. DatabaseName limits the operation to objects defined in 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. TableName limits the operation to the table you specify.

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 analyzed.

For more information about this input parameter, see “Usage Notes” or CreateObjectList.

NewerThan Date. NewerThan limits the operation to tables created after the date you specify.
MarkApproved Possible values:
  • Y or NULL. If you specify Y or NULL, the recommended missing statistics are marked approved and included in subsequent calls to PrepCollect and RunCollect. The default is Y.
  • N. If you specify N, the recommended missing statistics are marked unapproved. To approve these statistics, you must call ApproveStat.

Usage Notes

If you have permissions to grant the STATISTICS privilege on the qualifying user objects, you should also grant that privilege to the TDSTATS database which allows AnalyzeStats to submit EXPLAIN COLLECT STATISTICS statements (see Granting Required Privileges).

AnalyzeStats results are made available to subsequent calls to the PrepCollect external stored procedure, which incorporates event information when generating collection commands.

To display the AnalyzeStats results, you can call AnalyzeStatsReport.

If logged query data from the database query log (DBQL) is unavailable, you can call AnalyzeStatsUsage to incorporate recommendations from the Query Optimizer.

Analyzing 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 analyze ... You can specify a value for ...
statistics for a particular database DatabaseName.
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.

only newly created tables NewerThan.

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 events recorded by this operation.

Example: Using AnalyzeStats

The following example shows how to analyze the statistics on the Personnel database for objects created after January 1, 2010.

CALL AnalyzeStats ('Personnel', NULL, NULL,'2010-10-01 00:00:00.00','N',Analysisid,Numevents);
*** Procedure has been executed.
 *** Total elapsed time was 10 seconds.
AnalysisId   NumEvents
------------ ---------
1            2

Related Topics

For more information on ... See ...
the STATISTICS privilege Granting Required Privileges.
the EXPLAIN COLLECT STATISTICS request
  • Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
  • Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.
how PrepCollect incorporates event information when generating collection commands PrepCollect.
the AnalyzeStats results AnalyzeStatsReport.
the Analyzer-related external stored procedure that is recommended when query log data is available AnalyzeStatsUsage.