Database Query Risk Ranking via Execution Variance Analysis

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

In complex database environments, identifying and mitigating high-risk queries and query execution plans is challenging due to their numerous variations, which can cause performance degradation, slow response times, and application downtime.

Innovation Solution

An automated predictive model using historical and real-time query data to generate execution variance data, ranking queries based on execution time and buffer gets variance, and adjusting execution settings to mitigate performance degradation.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Adaptability or versatility

If query optimizers generate multiple execution plans for each query, then query execution flexibility and optimization options increase, but identification and mitigation of high-risk queries becomes extremely difficult

Engineering Contradiction:

Improvequery execution flexibilityVSAvoididentification of high-risk queries

Core Design Contradiction:

Adaptability or versatilityVSDifficulty of detecting and measuring

Solution Approach 1:

The system assigns risk scores (e.g., high, medium, low risk categories) to different execution plans and queries, creating a visual and quantitative differentiation that makes it easy to identify high-risk items among many alternatives. This scoring system transforms the complex evaluation of multiple execution plans into a simple risk-based classification.

Inventive Principle:

Principle #32Color changes

Solution Approach 2:

The system replaces manual analysis and operator judgment with an automated machine-learning model that evaluates execution plans and assigns risk scores. This automated system processes thousands of queries and their multiple execution plans efficiently, identifying high-risk items without human intervention.

Inventive Principle:

Principle #28Mechanics substitution (Replace mechanical system)

2Reliability

If the system analyzes all database queries to identify high-risk ones, then comprehensive risk detection is achieved, but processing overhead and time consumption increase significantly

Engineering Contradiction:

Improverisk detection comprehensivenessVSAvoidquery analysis time

Core Design Contradiction:

ReliabilityVSLoss of time

Solution Approach 1:

The system applies risk analysis selectively rather than uniformly to all queries. By using the machine-learning model to identify and focus only on high-risk queries and their execution plans, the system achieves comprehensive risk detection for critical items while avoiding unnecessary analysis of low-risk queries, thus reducing overall processing time.

Inventive Principle:

Principle #16Partial or excessive action

Solution Approach 2:

The system applies different levels of analysis depth to different queries based on their risk profiles. High-risk queries receive detailed analysis of multiple execution plans, while low-risk queries receive minimal or no analysis. This localized approach ensures thorough detection where needed while minimizing processing overhead elsewhere.

Inventive Principle:

Principle #3Local quality

3Productivity

If the system ranks queries by variance metrics, then prioritization of high-risk queries is achieved, but calculation complexity increases

Engineering Contradiction:

Improvequery prioritization efficiencyVSAvoidvariance calculation complexity

Core Design Contradiction:

ProductivityVSDevice complexity

Solution Approach 1:

The system replaces complex manual variance calculations with an automated machine-learning model that computes risk scores based on historical performance data. The model automatically calculates metrics such as execution time variance, buffer gets variance, and other performance indicators, transforming complex statistical analysis into an automated black-box process that delivers results efficiently.

Inventive Principle:

Principle #28Mechanics substitution (Replace mechanical system)

Solution Approach 2:

The system uses its own historical query performance data to automatically train and update the machine-learning model, which then self-evaluates current queries and execution plans. This self-service approach allows the system to maintain and improve its ranking accuracy without external intervention, handling the calculation complexity internally.

Inventive Principle:

Principle #25Self-service

Data Source

PatentUS11030195B2Identifying and mitigating high-risk database queries through ranked variance analysis

Publication Date: 2021.06.08 FMR CORP

AI summary

Methods and apparatuses are described for system for identifying and mitigating high-risk database queries through ranked variance analysis. A server identifies database queries executed against databases in a production computing environment within a predetermined time period, each database query associated with execution plans and each execution plan having corresponding plan data elements. For each database query: the server generates execution variance data for the execution plans for a database query based upon the corresponding plan data elements, comprising: determining an execution time variance between the execution plans; and determining a buffer gets variance between the execution plans. The server ranks the database queries according to (i) the execution time variance, and (ii) the buffer gets variance. The server computing device identifies high-risk database queries based upon the rank, and adjusts execution settings for each of the identified high-risk database queries to mitigate performance degradation in the production computing environment.