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