Hybrid SQL Tuning via Multi-Source Plan Testing

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Conventional SQL tuning approaches rely on single optimization strategies that are limited by stale statistics and incomplete analysis, leading to suboptimal execution plans and unpredictable performance, especially after database changes or upgrades.

Innovation Solution

Implementing a hybrid optimization strategy that collects and tests execution plans from multiple sources, including cost-based and rules-based optimizers, to identify a verifiably optimal execution plan through test execution in a reproduced environment, addressing issues of stale data and incorrect assumptions.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Productivity

If a cost-based optimizer is used to select execution plans, then the optimization process can consider multiple execution plans and compute costs based on system resources, but the decision becomes impaired when statistics are stale or incorrect

Engineering Contradiction:

ImproveSQL execution performanceVSAvoidaccuracy of execution plan selection

Core Design Contradiction:

ProductivityVSReliability

Solution Approach 1:

The patent implements feedback mechanisms where the query optimizer receives actual execution statistics and performance data from the database engine, compares these against predicted costs, and uses this feedback to refine future optimization decisions. This allows the system to learn from actual performance and correct deviations caused by stale or incorrect statistics.

Inventive Principle:

Principle #23Feedback

Solution Approach 2:

The patent replaces purely mechanical cost-based optimization with a hybrid approach that incorporates statistical analysis, machine learning models, and heuristics. The system substitutes rigid cost calculations with adaptive algorithms that can handle stale statistics by using alternative data sources and correction models.

Inventive Principle:

Principle #28Mechanics substitution (Replace mechanical system)

2Device complexity

If a rules-based optimizer is used to select execution plans, then the optimization process is simpler and faster, but the ability to select the best execution plan is limited without information concerning data characteristics

Engineering Contradiction:

Improveoptimization process complexityVSAvoidSQL execution performance

Core Design Contradiction:

Device complexityVSProductivity

Solution Approach 1:

The patent merges rules-based optimization with cost-based optimization into a hybrid query optimizer. The rules-based component provides fast initial filtering and simple optimization, while the cost-based component provides comprehensive analysis when needed. The system combines both approaches to achieve both simplicity and performance.

Inventive Principle:

Principle #5Merging (Combining)

Solution Approach 2:

The patent applies partial cost-based analysis to only the most critical execution plans while using rules-based optimization for less complex queries. This partial application of expensive analysis only where necessary maintains overall system efficiency while achieving optimal performance for important SQL statements.

Inventive Principle:

Principle #16Partial or excessive action

3Adaptability or versatility

If the query optimizer changes execution plans between runs, then the system can adapt to changing conditions, but performance becomes unpredictable and previously verified plans may perform poorly

Engineering Contradiction:

Improveadaptability to database changesVSAvoidpredictability of execution plan performance

Core Design Contradiction:

Adaptability or versatilityVSReliability

Solution Approach 1:

The patent performs preliminary analysis of database changes, schema modifications, and data characteristics before executing SQL queries. The system proactively updates statistics and reoptimizes execution plans in anticipation of changing conditions, rather than reacting after performance degrades. This preliminary action maintains predictability by preparing optimized plans before changes occur.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The patent implements safeguards and fallback mechanisms that prepare alternative execution plans in advance. When the primary execution plan degrades due to database changes, the system has pre-prepared backup plans that can be activated without causing performance spikes or unpredictable behavior. This cushioning ensures smooth transitions and maintains reliability during adaptation.

Inventive Principle:

Principle #11Beforehand cushioning (Prior cushioning)

Data Source

PatentUS7979422B2Hybrid optimization strategies in automatic SQL tuning

Publication Date: 2011.07.12 ORACLE INT CORP

AI summary

Systems, methods, and other embodiments associated with hybrid optimization strategies in automatic SQL tuning are described. One example method includes receiving a first (e.g., cost-based) execution plan for a user structured query language statement (User SQL) from a first (e.g., cost-based) optimizer. The example method may also include receiving a second (e.g., rules-based) execution plan for the User SQL from a second, different (e.g., rules-based) query optimizer. The method may include identifying a preferred execution plan based on data produced by test executing the execution plans in a reproduced execution environment that reproduces at least a portion of an execution environment in which the user SQL runs. The method may also include controlling a database to execute the User SQL using the preferred execution plan.