AnalyzeStatsUsage Stored Procedure | Application Programming Reference | Vantage - 17.05 - AnalyzeStatsUsage - 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 objects referenced within logged query plans to find conditions where automated statistics management tasks on them might be improved.

Definition

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)
           .
           .
           .
;

Input Parameters

Parameter Description
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 “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 “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.

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 “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 “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 “Usage Notes.”

QBName 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 “Usage Notes.”

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

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.

Output Parameters

Parameter Description
AnalysisId Results ID for all events generated by this operation.
NumEvents Number of events recorded by this operation.

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 Topics

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.