Database Statistics Management for Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Dynamic multi-tenant databases often experience suboptimal query plans due to outdated database statistics, leading to inefficiencies and timeouts, as the statistics do not reflect the current state of the database.
Innovation Solution
Modifying the development database to reflect expected utilization patterns, generating model database statistics, and updating the on-demand database statistics to ensure query plans are optimized based on anticipated usage, thereby influencing query generation and execution.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If database statistics are maintained by database management software, then query plans can be generated, but the statistics become outdated and suboptimal query plans result
Solution Approach 1:
The system performs preliminary actions by proactively updating database statistics before they become completely outdated. The statistics are updated in anticipation of data changes rather than reactively after changes occur, ensuring the query optimizer always has relatively fresh information to generate optimal query plans.
Solution Approach 2:
The system implements feedback mechanisms where query execution results and data change events are fed back into the statistics maintenance process. This continuous feedback loop ensures statistics remain accurate by adjusting them based on actual database state changes and query performance observations.
2Productivity
If database statistics are updated frequently to reflect current state, then query plan optimization improves, but system complexity and overhead increase
Solution Approach 1:
Instead of continuous updates, the system employs periodic action by updating statistics at specific intervals or triggered by significant data change events. This periodic approach maintains statistics accuracy while reducing the overhead and complexity of continuous maintenance operations.
Solution Approach 2:
The database management software performs self-service by automatically maintaining its own statistics without requiring external intervention. The system self-updates statistics based on data change events and query execution, eliminating the need for manual statistics management while reducing overall system complexity.
3Quantity of substance
If statistics reflect expected utilization patterns, then resource allocation optimizes, but measurement of actual usage becomes difficult
Solution Approach 1:
The system uses preliminary action by incorporating expected utilization patterns into statistics updates before actual usage occurs. This allows the system to anticipate resource needs and allocate resources proactively, avoiding the difficulty of measuring actual usage patterns in real-time.
Solution Approach 2:
The system applies parameter changes by adjusting statistics based on expected utilization patterns rather than strictly adhering to measured actual usage. This allows flexible resource allocation that adapts to changing conditions, making it easier to manage than strict measurement-based approaches.
Data Source
AI summary
Methods and systems are provided for querying a database. One exemplary method for obtaining data from an on-demand database supporting one or more tenants involves obtaining model database statistics based on expected utilization information for a tenant and providing the model database statistics to the on-demand database. The on-demand database utilizes the model database statistics to generate a query plan and executes the query plan to obtain data from the model database statistics.