Autonomic Database Statistics Maintenance via Query Feedback
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database query optimizers face challenges due to missing, outdated, or erroneous statistics, leading to poor query processing times, as existing methods are either costly or inefficient in maintaining accurate statistics, especially in databases with high data change rates or complex data distributions.
Innovation Solution
An autonomic database system that combines data-driven and query feedback-driven approaches to selectively gather and update statistics, prioritizing tables and columns based on activity and performance feedback to correct cardinality estimates and optimize query execution plans.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If incremental maintenance of statistics is performed during data manipulations, then statistics remain up-to-date, but system overhead and processing cost increase significantly
Solution Approach 1:
The system performs incremental maintenance only for selected statistics on selected tables during data manipulations, rather than maintaining all statistics for all tables. This partial action approach reduces overhead while keeping critical statistics up-to-date, resolving the contradiction between statistics accuracy and system overhead.
2Measurement precision
If all statistics are maintained for all tables, then query optimization is accurate, but resource consumption and maintenance complexity increase
Solution Approach 1:
The system applies different statistics maintenance strategies to different tables and columns based on their characteristics and importance. Critical tables with high data change rates receive incremental maintenance, while less critical tables use periodic maintenance. This local differentiation reduces overall complexity while maintaining accuracy where needed.
Solution Approach 2:
The statistics maintenance task is segmented into different types (incremental, periodic, on-demand) and applied to different table subsets. This segmentation allows the system to manage complexity by handling only necessary statistics updates rather than treating all statistics uniformly.
3Reliability
If statistics are updated frequently to maintain accuracy, then query performance is optimized, but system performance and throughput decrease
Solution Approach 1:
Instead of continuous incremental maintenance for all statistics, the system uses periodic maintenance schedules tailored to each table's characteristics. Tables with high data change rates are updated more frequently, while stable tables are updated less often. This periodic approach maintains statistics currency without continuously impacting system throughput.
4Manufacturing precision
If manual statistics management is performed, then configuration control is precise, but labor time and operational complexity increase
Solution Approach 1:
The system automatically determines which statistics need maintenance, selects appropriate maintenance strategies, and executes updates without administrator intervention. It self-configures based on observed data patterns and query workloads, eliminating manual management time while maintaining precise configuration through automated decision-making.
Solution Approach 2:
The system continuously monitors data change rates, query patterns, and statistics accuracy, using this feedback to automatically adjust maintenance schedules and strategies. This feedback loop enables precise statistics configuration to be maintained automatically, reducing administrator time while preserving configuration precision.
Data Source
AI summary
An autonomic tool that supervises the collection and maintenance of database statistics for query optimization by transparently deciding what statistics to gather, when and in what detail to gather them. Feedback from data-driven statistics collection is simultaneously combined with feedback from query-driven learning-based statistics collection, to better process both rapidly changing data and data that is queried frequently. The invention monitors table activity and decides if the data in a table has changed sufficiently to require a refresh of invalid statistics. The invention determines if the invalidity is due to correlation between purportedly independent data, outdated statistics, or statistics that have too few frequent values. Tables and column groups are ranked in order of statistical invalidity, and a limited computational budget is prioritized by ranking subsequent gathering of improved statistics. Multiple tables can have their statistics refreshed over time, and the maintenance effort is concentrated on the most important tables.