Database Statement Risk Identification via Plan Sensitivity Index
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database systems face challenges in predicting and maintaining optimal execution plans for SQL statements due to fluctuating data attributes, leading to potential performance deterioration, which is difficult to identify and mitigate manually.
Innovation Solution
A database management system performs series of experiments by generating sets of statistics with varying attribute values to identify high-risk database statements and calculates Plan Sensitivity Index (PSI) values to quantify the sensitivity of execution plans to changes, allowing for automatic tuning and proactive measures to mitigate risks.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If manual review of database statements is performed to identify high-risk statements, then expertise in query optimization is required, but the process becomes costly and time-consuming
Solution Approach 1:
The system performs self-diagnosis by automatically analyzing execution plans and calculating PSI values to identify high-risk database statements without requiring manual expert review. The database management system itself generates the risk assessment through automated experiments and statistical analysis.
Solution Approach 2:
The manual expert review process is replaced with an automated computational system that uses statistical algorithms and machine learning models to assess execution plan risk. The mechanical process of manual analysis is substituted with automated electronic processing of database statistics and execution plan data.
2Measurement precision
If automated experiments with varying statistics are performed to identify high-risk statements, then identification accuracy improves, but system complexity increases
Solution Approach 1:
The complex task of identifying high-risk database statements is segmented into manageable components: generating varied statistics sets, executing experiments for each statement, calculating PSI values, and ranking statements by risk. This modular approach reduces overall system complexity while maintaining high identification precision.
Solution Approach 2:
The system changes statistical parameters (data distribution, table sizes, selectivity estimates) to create multiple experiment scenarios. By systematically varying these parameters, the system achieves precise risk identification without requiring overly complex experimental designs.
3Adaptability or versatility
If execution plans are allowed to change dynamically based on data attributes, then adaptability improves, but performance stability deteriorates
Solution Approach 1:
The system performs preliminary analysis by calculating PSI values and identifying high-risk statements before actual execution occurs. By pre-assessing the sensitivity of execution plans to statistical changes, the system can take preventive actions (such as fixing execution plans or adjusting statistics) to maintain performance stability while preserving necessary adaptability.
Solution Approach 2:
The system implements feedback mechanisms by continuously monitoring actual execution performance and comparing it with predicted performance based on PSI analysis. This feedback loop allows the system to adjust execution plan selection strategies dynamically, maintaining stability when plans are high-risk while allowing adaptability when plans are stable.
Data Source
AI summary
High-risk database statements are identified. A DBMS performs a series of experiments by generating sets of statistics describing data over which particular database statements run. The DBMS submits each of these sets of statistics to a query optimizer, which returns information for an execution plan that the optimizer would use to implement the statements given the submitted set of data statistics. If the DBMS determines that the optimizer changes the established execution plan for the database statements for any of the submitted sets of statistics, the DBMS performs one or more actions to mitigate the risk of changing execution plans, such as automatically tuning the database statements or the entire workload, and/or sending information about the database statements to a database administrator. Techniques are also described for calculating diagnostic values based on the results of such experiments, which quantify the sensitivity of the execution plans to changes in data statistics.