Query Advisor Engine for Slow SQL Plan Diagnosis
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Customers experience slow query performance in database management systems due to factors such as outdated statistics, sub-optimal database flag values, and sub-optimal query optimizer results, making it challenging to identify and address the underlying issues.
Innovation Solution
A query advisor module that analyzes query execution history and plans to generate automatic recommendations for tuning, including query rewriting, database flag tuning, vacuum operations, and statistics updates, with a plan management engine to track and manage query plans for improved performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of operation
If the query optimizer generates query plans automatically, then query execution is simplified, but query performance may deteriorate due to outdated statistics or wrong estimations
Solution Approach 1:
The system implements feedback by analyzing actual query execution results and performance metrics, then using this information to update statistics and improve future query plan generation. The query advisor module continuously monitors query performance and provides recommendations based on observed patterns, creating a closed-loop system that learns from past executions.
Solution Approach 2:
The system performs preliminary actions by pre-calculating and storing query statistics, maintaining up-to-date metadata about data distributions, and proactively generating query plans before actual query execution. This allows the optimizer to make informed decisions without requiring complex real-time analysis during query processing.
2Measurement precision
If manual analysis of query performance issues is performed, then accurate problem identification is achieved, but time consumption and operational complexity increase
Solution Approach 1:
The system implements self-service by automatically monitoring its own performance, detecting issues, and generating diagnostic information without requiring manual intervention. The query advisor module autonomously analyzes execution patterns, identifies performance bottlenecks, and provides actionable recommendations, allowing the database system to self-diagnose and self-optimize.
Solution Approach 2:
The query advisor module acts as an intermediary between the query optimizer and the user, translating complex performance issues into understandable recommendations. It mediates between the raw execution data and the user's decision-making process, providing synthesized insights that bridge the gap between system internals and user actions.
3Reliability
If comprehensive query analysis is performed to identify all performance issues, then complete problem detection is achieved, but system complexity and computational overhead increase
Solution Approach 1:
The system segments the query analysis process into distinct modules: statistics collection, execution monitoring, pattern recognition, and recommendation generation. Each module handles a specific aspect of analysis, allowing the system to comprehensively examine query performance while maintaining manageable complexity through modular architecture.
Solution Approach 2:
The system applies partial action by focusing analysis on the most impactful query performance factors rather than examining every possible parameter. It prioritizes statistics updates, execution patterns, and optimizer settings that have the greatest influence on query performance, achieving effective problem detection without exhaustive analysis of all system parameters.
Data Source
Figure 1
Figure 2
Figure 3
AI summary
Aspects of the disclosure are directed to a query advisor module that generates recommendations for tuning a query template to improve performance. The recommendations may include suggestions for performing actions, such as rewriting the query, tuning database flags at a session level, fixing table/column statistics, running a vacuum, or planning hints or a SQL Patch. The query advisor may provide recommendations automatically. In other examples, the query advisory may be activated manually. Once the query advisor is enabled, the database advisor may maintain the query plan that was analyzed along with its associated tuning recommendations for later retrieval.