Selective Database Statistics Recollection via Stale Flags
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Users face challenges in determining when to recollect database statistics due to resource-intensive processes, often resulting in either unnecessary recollections or stale statistics, especially during frequent batch load operations.
Innovation Solution
Implementing a system that maintains a stale flag and modified count for columns and indexes, allowing for selective statistics recollection only when data demographics have changed significantly, using a Boolean flag and threshold-based rules to determine when statistics need updating.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If statistics are recollected after every batch load operation, then data demographics are kept current, but resource consumption increases unnecessarily
Solution Approach 1:
The system performs preliminary tracking of data changes by maintaining a modified count for each column/index. Instead of recollecting statistics after every batch load, the system preliminarily monitors whether the modified count exceeds the threshold, and only then initiates the resource-intensive statistics recollection process.
Solution Approach 2:
The system implements feedback through the stale flag mechanism. After each batch load operation, the modified count is compared against the threshold, and the stale flag is updated accordingly. This feedback loop ensures that statistics are recollected only when actually needed, preventing unnecessary resource consumption while maintaining data freshness.
2Use of energy by moving object
If statistics are not recollected, then resource consumption is reduced, but query optimizer performance deteriorates due to stale statistics
Solution Approach 1:
The system makes the statistics recollection process dynamic by using threshold-based rules. Instead of static recollection schedules, the system dynamically adjusts when recollection occurs based on the modified count threshold. This allows the system to maintain query optimizer performance only when necessary, reducing resource consumption during periods when data changes are minimal.
3Use of energy by moving object
If manual determination of recollection need is performed, then unnecessary recollections are avoided, but operational complexity increases
Solution Approach 1:
The system performs self-service by automatically tracking data modifications and determining when statistics recollection is needed. The modified count mechanism automatically compares against the threshold and updates the stale flag without requiring manual intervention. This eliminates the need for users to manually assess recollection needs while avoiding unnecessary resource consumption.
Data Source
AI summary
The subject mater herein relates to database management and, more particularly, to selective database statistics recollection. Various embodiments provide one or more of system, methods, and software that identify database column and index states when statistics become stale to a point where query execution plans against respective columns and indexes fail to be efficient. These and other embodiments provide a way to update column and index statistics only when the statistics are determined to be stale. As a result, adverse database performance as a result of needless updating of database statistics is mitigated.