Analyzing User Objects and Logged Query Plans - Teradata Database - Teradata Vantage NewSQL Engine

Application Programming Reference

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-02
dita:mapPath
vwf1492987142269.ditamap
dita:ditavalPath
changebar_rev_16_20_exclude_audience_ie.ditaval
dita:id
B035-1090
lifecycle
previous
Product Category
Teradata Vantageā„¢
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.