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:
For more information, see AddQueryList. |
MarkApproved | Possible values:
|
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.
- Recently automated queries referencing databases or tables.
- A significantly changed query workload.
Analyzing Statistics
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
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 |
|
displaying the AnalyzeStatsUsage results | AnalyzeStatsUsageReport. |
the DBC.DBQLogTbl table | Teradata Vantage™ - Database Administration, B035-1093. |
the STATSUSAGE and XMLPLAN logging options |
|
the Analyzer-related external stored procedure | AnalyzeStats. |