AnalyzeStatsUsage Stored Procedure | Application Programming Reference | Vantage - 17.10 - AnalyzeStatsUsage - 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)

Analyzes objects referenced within logged query plans to find conditions where automated statistics management tasks on them might be improved.

Syntax

REPLACE PROCEDURE TDSTATS.AnalyzeStatsUsage (
  IN LogStartTime TIMESTAMP(6) WITH TIME ZONE,
  IN LogEndTime TIMESTAMP(6) WITH TIME ZONE,
  IN LogDatabaseName VARCHAR(128) CHARACTER SET UNICODE,
  IN DatabaseName VARCHAR(128) CHARACTER SET UNICODE,
  IN TableName VARCHAR(128) CHARACTER SET UNICODE,
  IN ObjectListName VARCHAR(128) CHARACTER SET UNICODE,
  IN ApplName VARCHAR(128) CHARACTER SET UNICODE,
  IN UserName VARCHAR(128) CHARACTER SET UNICODE,
  IN AcctString VARCHAR(128) CHARACTER SET UNICODE,
  IN QBName VARCHAR(128) CHARACTER SET UNICODE,
  IN QBValue VARCHAR(256) CHARACTER SET UNICODE,
  IN QueryListName VARCHAR(128) CHARACTER SET UNICODE,
  IN MarkApproved CHAR(1) CHARACTER SET LATIN,
  OUT AnalysisId BIGINT,
  OUT NumEvents INTEGER
)
  ...
;

Syntax Elements

LogStartTime
Start time. LogStartTime limits the analysis to queries logged after the time you specify.
LogEndTime
End time. LogEndTime limits the analysis to queries logged before the time you specify.
LogDatabaseName
Name of the database containing the log tables.
If you specify the LogDatabaseName value as PDCRDATA, the operation automatically locates the valid query log tables.
If you specify the LogDatabaseName value as a database other than DBC or PDCRDATA, the operation assumes the log tables of the database specified are identical in name and structure to those in the DBC database, namely the DBQLogTbl and DBQLXMLTbl tables.
If you specify LogDatabaseName as NULL, DBC is the default database.
DatabaseName
User database. DatabaseName limits the analysis to referenced objects defined in the user database you specify.
This input parameter may contain wildcard characters (%, _). For more information about wildcard characters, see the usage notes.
TableName
Referenced user table. TableName limits the analysis to the referenced user table you specify.
This input parameter may contain wildcard characters (%, _). For more information about wildcard characters, see the 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 the usage notes or CreateObjectList.
ApplName
Application name. ApplName limits the analysis to logged queries with the name of the application you specify.
This input parameter may contain wildcard characters (%, _). For more information about wildcard characters, see the usage notes.
UserName
User name. UserName limits the operation to logged queries with the user name you specify.
This input parameter may contain wildcard characters (%, _). For more information about wildcard characters, see the usage notes.
AcctString
Account string. AcctString limits the operation to logged queries with the account string you specify.
This input parameter may contain wildcard characters (%, _). For more information about wildcard characters, see the usage notes.
QBName
QBValue
Query band pair consisting of the following:
QBName=QBValue
QBName limits the operation to logged queries you specify.
This input parameter may contain wildcard characters (%, _). For more information about wildcard characters, see the usage notes.
QueryListName
Name of the query list. QueryListName limits the operation to the queries defined in a list previously created by the AddQueryList external stored procedure.
When you specify a value for QueryListName, the following input parameters must be NULL:
  • ApplName
  • UserName
  • AcctString
  • QBName
  • QBValue
For more information, see AddQueryList.
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.
AnalysisId
Results ID for all events generated by this operation.
NumEvents
Number of events recorded by this operation.

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 AnalyzeStatsUsage to submit EXPLAIN COLLECT STATISTICS statements (see Granting Required Privileges).

Before using AnalyzeStatsUsage, you must enable DBQL while executing queries during the specified time period.

By default, the Analyzer operation assumes the DBQL tables reside within DBC, but you can specify an alternate database that contains archived query logs.

The AnalyzeStatsUsage results are written to the AnalyzerHistoryTbl table in the form of events, such as recommended missing statistics and identified stale statistics. The AnalyzeStatsUsage results are made available to subsequent calls to PrepCollect.

To display the AnalyzeStatsUsage results, you can call AnalyzeStatsUsageReport.

When to Use AnalyzeStatsUsage

AnalyzeStatsUsage incorporates logged recommendations from the Query Optimizer. It also performs a workload-level analysis to identify the subset of the Query Optimizer recommendations that occur frequently across the query log and in situations where they may be of most benefit (for example, query steps with inaccurate spool size estimates).

Teradata recommends that you use the AnalyzeStatsUsage external stored procedure when query log data is available and AnalyzeStats when it is not. For more information, see AnalyzeStats.

Enabling the STATSUSAGE and XMLPLAN Logging Options

For a more complete analysis, Teradata recommends that you enable both the BEGIN QUERY LOGGING XMLPLAN and STATSUSAGE options. However, query logs with only STATSUSAGE data is sufficient.

You must enable the STATSUSAGE and XMLPLAN options for:
  • Recently automated queries referencing databases or tables.
  • A significantly changed query workload.

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 statistics for a ... You can specify a value for ...
particular database DatabaseName
particular table both DatabaseName and TableName
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.

Example: Using AnalyzeStatsUsage

The following example shows how to analyze the statistics usage in the Personnel database for queries logged over a seven-day period.

CALL TDSTATS.AnalyzeStatsUsage ('2010-10-01 00:00:00.000000+00:00','2010-10-08 00:00:00.000000+00:00', NULL, 'Personnel',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Y',AnalysisId,NumEvents);
*** Procedure has been executed.
*** Total elapsed time was 10 seconds.
  AnalysisId   NumEvents
------------- -------------
      2          4

Related Information

For more information on ... See ...
the STATISTICS privilege GrantPrivileges.
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.
displaying the AnalyzeStatsUsage results AnalyzeStatsUsageReport.
the DBC.DBQLogTbl table Teradata Vantage™ - Database Administration, B035-1093.
the STATSUSAGE and XMLPLAN logging options
  • Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
  • Teradata Vantage™ - Database Administration, B035-1093.
  • Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.
the Analyzer-related external stored procedure AnalyzeStats.