Analyzing User Objects and Logged Query Plans | Teradata Vantage - 17.10 - Analyzing User Objects and Logged Query Plans - 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)
You can use Analyzer open APIs to analyze user objects and logged query plans to identify conditions where future automated statistics management operations might be improved. These conditions include:
  • Recommendation of additional statistics
  • Identification of stale statistics that need recollected
  • Identification of statistics that are not used by the Query Optimizer

When available, Analyzer open API results are leveraged by the PrepCollect open API in generating a prioritized list of SQL COLLECT STATISTICS statements.

Teradata recommends that you call Analyzer open APIs when there are major changes to:
  • A query workload in which automated statistics are used.
  • Table structures (for example, new indexes) on which automated statistics are defined.

By default, the status of each recommended new statistic from an Analyzer-related open API is unapproved which disqualifies it for inclusion by the PrepCollect and RunCollect open APIs. For more information on these open APIs, see Preparing and Collecting Statistics.

After reviewing the recommendations from the Analyzer-related call (for example, AnalyzeStatsUsage open API), you can call the ApproveStat open API to approve one or more of the recommended statistics. Analyzer-related open APIs contain an input parameter that can mark newly recommended statistics approved and allow them to become eligible for inclusion by the PrepCollect open API.

Functionality

If you want to ... Use the following SQL interface ...
analyze statistics for a specified database or table using physical design information available from the dictionary AnalyzeStats.
analyze statistics usage on specified objects referenced within the database query log AnalyzeStatsUsage.
identify inactive statistics that have not been used during query optimization for a specified duration CleanupStats.
display results from prior call to the AnalyzeStats open API AnalyzeStatsReport.
display results from prior call to the AnalyzeStatsUsage open API AnalyzeStatsUsageReport.
displays results from prior call to the CleanupStats open API CleanupStatsReport.
approve a recommendation for collecting a new statistic ApproveStat.
disapprove a recommendation for collecting a new statistic DisapproveStat.
confirm a recommendation to inactivate a statistic InActivateStat.
reactivate a previously inactivated statistic ActivateStat.