Selective Database Execution Plan Refreshing
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face inefficiencies due to stored execution plans becoming sub-optimal or stale over time, especially when data demographics change, leading to potential missed opportunities for more efficient plans, and manual recompilation is impractical for frequently executed queries.
Innovation Solution
Implementing techniques to selectively recompile stored execution plans by using version numbers, cost parameters, and sub-optimal indicators, such as Stale_Flag, to automatically detect and refresh plans when significant changes occur in software versions, hardware configurations, or table statistics, ensuring only used plans are recompiled.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of energy
If stored execution plans are reused over a long period, then CPU cost is saved by avoiding repeated parsing and optimization, but the plans become sub-optimal or stale when data demographics change
Solution Approach 1:
The system dynamically determines whether to reuse or recompile stored execution plans based on current system state. Version numbers associated with plans are compared against current database software versions, and cost parameters are reevaluated to determine if recompilation is necessary. This dynamic approach allows the system to adapt between plan reuse and recompilation based on changing conditions.
Solution Approach 2:
The system changes key parameters associated with execution plans including version numbers that track database software versions, cost parameters that reflect current system performance characteristics, and validity indicators that track whether plans have been superseded. These parameter changes enable the system to identify when plans need recompilation without manual intervention.
2Reliability
If manual recompilation is implemented, then plan optimality can be maintained, but it is impractical for systems with hundreds or thousands of frequently executed queries
Solution Approach 1:
The system automatically manages execution plan recompilation without requiring manual intervention. It self-monitors version numbers, reevaluates cost parameters, and automatically triggers recompilation when plans become sub-optimal. This self-service mechanism scales to handle hundreds or thousands of queries that would be impossible to manage manually.
Solution Approach 2:
The system implements feedback mechanisms through version number tracking and cost parameter reevaluation. When database software versions change or when cost parameters indicate sub-optimal performance, the system receives feedback and automatically initiates recompilation. This feedback loop ensures plan optimality is maintained without manual oversight.
3Reliability
If all stored execution plans are recompiled frequently, then plan optimality is maintained, but system performance degrades due to repeated compilation overhead
Solution Approach 1:
The system applies different treatments to different execution plans based on their individual characteristics and current validity. Rather than uniformly recompiling all plans, it selectively recompiles only those plans whose version numbers have changed or whose cost parameters indicate sub-optimal performance. This localized approach maintains optimality where needed while preserving efficient reuse where appropriate.
4Loss of energy
If stored execution plans are reused, then parsing and optimization CPU costs are saved, but the system cannot adapt to significant changes in data demographics
Solution Approach 1:
The system performs preliminary checks before executing stored plans by validating version numbers and cost parameters. This preliminary action detects changes in data demographics or system configuration before execution, triggering recompilation only when necessary. This approach saves CPU costs by avoiding unnecessary recompilation while ensuring adaptability when changes occur.
Data Source
AI summary
A system and method for selectively recompiling stored execution plans within a database system. In one technique the execution plans include respective version numbers of database software operable within the database system during compilation of the respective plans. A request is received to execute one of the stored execution plans, and the stored execution plan is retrieved from computer memory. A version number is obtained of database software operable within the database system during retrieval of the stored execution plan. The obtained version number is compared with the version number associated with the retrieved execution plan. On detecting a mismatch between the obtained version number and the retrieved version number, the stored execution plan is recompiled. In another technique the stored execution plans include respective cost parameters representing the hardware capabilities of the system at the time of plan compilation. A set of cost parameters representing the cost of executing the stored execution plan within the database system is compared with the stored cost parameters associated with the retrieved execution plan. On detecting a significant difference between the defined cost parameters and the retrieved cost parameters, the stored execution plan is recompiled.