Machine Learning for Database Statistics Gathering Timing
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional database management techniques face challenges with stale statistics, leading to sub-optimal execution plans and inefficient resource usage, as they rely on time-intensive and sporadic statistics gathering methods that can negatively impact database performance.
Innovation Solution
The implementation of machine learning techniques to predict optimal statistics gathering times for database activity data, using methods such as time series forecasting and automated action generation to ensure timely and efficient statistics collection.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If conventional static scheduled statistics gathering is used, then statistics are gathered at fixed intervals, but this results in stale statistics and sub-optimal execution plans
Solution Approach 1:
The patent transitions from static scheduled statistics gathering to dynamic prediction-based gathering. Machine learning models analyze historical database activity patterns to dynamically determine when statistics gathering is needed, adapting to changing workloads rather than following fixed intervals. This resolves the contradiction by making the statistics gathering timing responsive to actual database conditions.
Solution Approach 2:
The system implements feedback loops where machine learning models continuously learn from historical statistics gathering data and database activity patterns. The prediction accuracy improves over time based on feedback from actual database performance and activity patterns, enabling more accurate determination of optimal statistics gathering times to prevent staleness while avoiding unnecessary gathering.
2Reliability
If continuous statistics gathering is performed, then statistics remain up-to-date, but computing resources are consumed and other database operations are negatively impacted
Solution Approach 1:
The patent implements periodic statistics gathering based on predicted optimal times rather than continuous gathering. Machine learning models identify specific time windows when statistics gathering is most beneficial, allowing the system to pause between gatherings and conserve computing resources while maintaining statistics freshness when it matters most for query optimization.
Solution Approach 2:
The system changes the timing parameter of statistics gathering from fixed intervals to dynamically predicted times based on database activity patterns. This parameter change allows the system to gather statistics when resource utilization is lower and when it will have maximum impact on query performance, balancing freshness with resource consumption.
3Ease of manufacture
If manual intervention or static scheduled gathering is used, then implementation is simple, but statistics gathering is inadequate for certain storage objects with varying transaction patterns
Solution Approach 1:
The patent implements self-service through automated machine learning models that independently analyze database activity patterns and determine optimal statistics gathering times for different storage objects. The system automatically adapts to varying transaction patterns without requiring manual configuration for each object, resolving the contradiction between implementation simplicity and adaptability.
Solution Approach 2:
The system segments the database into different storage objects and applies individualized statistics gathering predictions to each based on their specific activity patterns. This segmentation allows the system to handle diverse transaction patterns across different objects while maintaining a unified automated approach, balancing simplicity with adaptability.
4Use of energy by moving object
If sporadic statistics gathering is performed, then resource consumption is reduced, but statistics become stale and mislead the optimizer
Solution Approach 1:
The patent applies preliminary action by using machine learning models to predict future statistics gathering needs based on historical patterns. The system proactively identifies when statistics are likely to become stale and schedules gathering before staleness occurs, preventing information loss while avoiding unnecessary resource consumption through accurate prediction.
Data Source
AI summary
Methods, apparatus, and processor-readable storage media for predicting statistics gathering times for database activity data using machine learning techniques are provided herein. An example computer-implemented method includes obtaining activity data pertaining to at least one storage object of a given database; determining one or more times to gather statistics related to activity data pertaining to the at least one storage object of the given database by processing at least a portion of the obtained activity data using one or more machine learning techniques; and performing at least one automated action related to statistics gathering based at least in part on the one or more determined times.