Query Optimizer Using Empirical Performance Statistics
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database management systems face challenges in selecting the most efficient query access plan due to the vast number of possible execution plans, often relying on estimated costs rather than empirical performance statistics, which can lead to suboptimal performance, especially with complex queries and data skew issues.
Innovation Solution
A query optimizer that utilizes past performance statistics from a database monitor to generate access plans, considering similarity and past performance of previous queries to automatically tune and optimize query execution, including re-optimization when data skew is detected.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If conventional query optimizers rely on estimated costs rather than empirical performance statistics, then the system can operate with limited historical data, but the query execution performance deteriorates due to suboptimal access plan selection
Solution Approach 1:
The system collects and stores performance statistics for query access plans in advance during normal database operations. The database monitor continuously tracks actual performance data (execution time, I/O operations, CPU usage) for various access plans and stores this information in a performance statistics database. When a query is executed, the optimizer can immediately query this pre-collected statistical data rather than relying solely on cost estimates, enabling faster and more accurate access plan selection without adding delay to the query execution process.
2Reliability
If the query optimizer considers a vast number of possible execution plans, then the likelihood of finding an optimal plan increases, but the computational resources and time required for optimization increase significantly
Solution Approach 1:
The system implements a feedback mechanism where the database monitor continuously collects actual performance data from executed queries and feeds this information back to the query optimizer. The performance statistics database stores empirical data about which access plans performed well or poorly in similar situations. When optimizing a new query, the optimizer queries this feedback data to identify patterns and use them to prune the search space of possible execution plans, focusing computational effort on plans that have historically performed well rather than evaluating all possible plans equally.
Solution Approach 2:
The system changes the parameters used for query optimization from purely theoretical cost estimates to empirical performance statistics. Instead of relying on optimizer cost models that estimate execution costs, the system transforms the optimization process to use actual measured performance data (execution time, resource consumption) from previous runs of similar queries. This parameter transformation allows the optimizer to make more accurate decisions with fewer candidate plans to evaluate, improving both plan quality and processing throughput.
3Measurement precision
If the database monitor collects detailed performance statistics for all queries, then the accuracy of performance analysis increases, but the storage requirements and data processing complexity increase
Solution Approach 1:
The system extracts and stores only the most critical performance statistics in the performance statistics database, rather than collecting and managing all possible query performance data. The database monitor identifies and extracts key metrics such as execution time, I/O operations, CPU usage, and access plan identifiers. By selectively extracting only the essential performance parameters needed for optimization decisions, the system maintains high measurement precision for the most important metrics while reducing overall data volume and simplifying the statistics management infrastructure.
Data Source
AI summary
A database monitor tracks performance statistics and information about the execution of different SQL statements. A query optimizer benefits from these statistics when generating an access plan. In particular, the query optimizer, upon receiving an SQL statement, searches the records of the database monitor for similar SQL statements that have previously been executed. As part of determining the best access plan for the current SQL statement, the query optimizer considers the information retrieved from the database monitor. In this way, the access plan that is generated can automatically be tuned based on empirical performance evidence.