Database Query Plan Comparison Tool for DBMS Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current query plan viewing tools are cumbersome and time-consuming for database administrators and optimizer developers to analyze the impact of changes in database management systems (DBMS) attributes on query execution plans, due to the complexity and length of the plans, which include detailed information such as operators, estimated cardinalities, and costs.
Innovation Solution
A system and method for performing a row-by-row or operator-by-operator comparison of query execution plans using a computer-readable representation in XML, allowing users to define source and destination parameters for a comparison tool, which generates reports showing differences in cardinality, cost, and operator changes between pre-change and post-change plans.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of information
If current query plan viewing tools are used to display execution plans, then users can access detailed information about operators, estimated cardinalities, and costs, but the analysis becomes cumbersome and time-consuming due to the complexity and length of the plans
Solution Approach 1:
The execution plan is segmented into comparable units (operators, cardinalities, costs) that can be systematically compared between pre-change and post-change plans. The comparison tool processes each segment individually to identify differences, transforming the complex whole into manageable components for automated analysis.
Solution Approach 2:
The comparison tool automatically performs the analysis of differences between execution plans without requiring manual intervention. The system self-processes the comparison, generates difference reports, and presents results automatically, eliminating the need for users to manually analyze complex plans.
2Ease of operation
If execution plans are displayed in human-readable or machine-readable form with detailed information, then users can understand plan structure, but the complexity and length make manual analysis difficult
Solution Approach 1:
The comparison tool extracts only the relevant differences between pre-change and post-change execution plans, separating the essential information (changes in operators, cardinalities, costs) from the redundant common elements. This extraction presents a simplified view of what actually changed, making analysis easier despite the inherent complexity of the full plans.
Solution Approach 2:
The comparison tool acts as an intermediary between the complex execution plans and the user's analysis needs. It processes the complex plan data, transforms it into comparable formats, identifies differences, and presents results in an accessible manner, bridging the gap between complexity and ease of analysis.
Data Source
AI summary
A method and device may accept a first database query plan including a plurality of rows and a second database query plan including a plurality of rows, compare each row in the first database query plan with a corresponding row in the second database query plan, and based on each comparison, calculate a difference between the compared rows. Each difference may be recorded in an output file.