15.10 - AnalyzeStatsUsage - Teradata Database

Teradata Database Application Programming Reference

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1090-151K

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

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

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.

Note: 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” on page 603.

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.

Note: When you specify a value for QueryListName, the following input parameters must be NULL:

  • ApplName
  • UserName
  • AcctString
  • QBName
  • QBValue
  • For more information, see “AddQueryList” on page 609.

    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.
  • 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” on page 484).

    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” on page 515.

    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

    Notice:

    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.

    Note: 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 SQL Functions, Operators, Expressions, and Predicates.

     

    Parameter

    Description

    AnalysisId

    Results ID for all events generated by this operation.

    NumEvents

    Number of events recorded by this operation.

    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
     

    For more information on ...

    See ...

    the STATISTICS privilege

    “GrantPrivileges” on page 485.

    the EXPLAIN COLLECT STATISTICS request

  • SQL Data Definition Language.
  • SQL Request and Transaction Processing.
  • displaying the AnalyzeStatsUsage results

    “AnalyzeStatsUsageReport” on page 529.

    the DBC.DBQLogTbl table

    Database Administration.

    the STATUSAGE and XMLPLAN logging options

  • SQL Data Definition Language.
  • Database Administration.
  • SQL Request and Transaction Processing.
  • the Analyzer-related external stored procedure

    “AnalyzeStats” on page 515.