15.10 - Analyzing User Objects and Logged Query Plans - Teradata Database

Teradata Database Application Programming Reference

Teradata Database
Programming Reference

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

    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.


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

    analyze statistics usage on specified objects referenced within the database query log

    “AnalyzeStatsUsage” on page 518.

    identify inactive statistics that have not been used during query optimization for a specified duration

    “CleanupStats” on page 523.

    display results from prior call to the AnalyzeStats open API

    “AnalyzeStatsReport” on page 526.

    display results from prior call to the AnalyzeStatsUsage open API

    “AnalyzeStatsUsageReport” on page 529.

    displays results from prior call to the CleanupStats open API

    “CleanupStatsReport” on page 532.

    approve a recommendation for collecting a new statistic

    “ApproveStat” on page 535.

    disapprove a recommendation for collecting a new statistic

    “DisapproveStat” on page 538.

    confirm a recommendation to inactivate a statistic

    “InActivateStat” on page 540.

    reactivate a previously inactivated statistic

    “ActivateStat” on page 542.