Adaptive Cursor Sharing for Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Traditional cursor sharing methods are suboptimal when using bind variables, as they can result in unpredictable run-time performance due to execution plans being optimal for one bind value but suboptimal for another, leading to a trade-off between memory usage and performance.
Innovation Solution
Implementing extended and adaptive cursor sharing techniques that determine whether to reuse a cursor based on selectivity ranges and execution plan similarity, using a 'soft parse' to assess the suitability of an existing cursor for a new query with bind variables, and dynamically switching between different cursor sharing approaches based on performance metrics.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If cursor sharing is used to reuse execution plans for queries with bind variables, then memory usage is reduced and hard parses are avoided, but run-time performance becomes unpredictable because execution plans optimal for one bind value may be suboptimal for another
Solution Approach 1:
The patent changes the parameter of cursor sharing from a binary state (share or not share) to a continuous spectrum by introducing soft parse and execution plan similarity comparison. The system dynamically adjusts the degree of cursor sharing based on bind value differences, allowing optimal performance by selectively sharing cursors only when execution plans remain suitable despite parameter changes.
Solution Approach 2:
The patent makes the cursor sharing mechanism dynamic by introducing adaptive behavior. The system monitors execution plan suitability and bind value differences, dynamically deciding whether to share or create new cursors. This dynamic adjustment resolves the contradiction by adapting to different scenarios: sharing when safe to reduce memory usage, and creating new cursors when performance reliability is at risk.
2Productivity
If traditional cursor sharing always reuses cursors for semantically equivalent queries, then the number of hard parses is reduced, but execution efficiency deteriorates when bind values differ significantly
Solution Approach 1:
The patent introduces feedback mechanisms through soft parse and execution plan similarity comparison. Before reusing a cursor, the system evaluates whether the execution plan remains suitable for the new bind values. This feedback loop ensures that cursor sharing only occurs when it maintains execution efficiency, resolving the contradiction between reducing hard parses and maintaining execution efficiency.
Solution Approach 2:
The patent performs preliminary evaluation through soft parse before cursor sharing. By assessing execution plan suitability in advance, the system prevents inefficient cursor reuse. This preliminary action filters out cases where cursor sharing would harm execution efficiency, allowing aggressive sharing only when pre-evaluation confirms safety.
3Reliability
If cursor sharing is disabled to ensure optimal execution plans for each query, then execution efficiency is maintained, but memory consumption increases and hard parses are performed unnecessarily
Solution Approach 1:
The patent changes the parameter of cursor lifecycle from fixed (always create new or always share) to flexible by introducing soft parse and similarity-based decisions. The system adjusts cursor sharing behavior based on bind value analysis, creating new cursors only when necessary and reusing them when safe, thereby optimizing the balance between memory consumption and execution efficiency.
Solution Approach 2:
The patent makes the cursor creation/sharing decision dynamic rather than static. The system adapts to each query's specific bind values and execution plan characteristics, dynamically deciding whether to share or create new cursors. This dynamic approach maintains execution efficiency by creating cursors only when needed while reducing memory consumption through selective sharing.
4Measurement precision
If a soft parse is performed to evaluate execution plan similarity before cursor sharing, then cursor sharing accuracy is improved, but processing overhead increases
Solution Approach 1:
The patent applies partial action by performing soft parse selectively rather than universally. The system performs execution plan similarity comparison only when cursor sharing is being considered, not for every query. This partial application of the evaluation process improves cursor sharing accuracy for relevant cases while minimizing unnecessary processing overhead.
Solution Approach 2:
The patent changes the parameter of parse intensity from full hard parse to partial soft parse. By performing only the necessary execution plan similarity comparison without full compilation, the system achieves sufficient accuracy for cursor sharing decisions while significantly reducing processing overhead compared to complete reparsing.
Data Source
AI summary
Techniques for sharing cursors are provided. When a new query is issued, a database server determines whether the new query is semantically equivalent to a previous query. If so, then database server computes statistics associated with the new query. Based on the statistics, the database server determines whether compiling the new query would produce an execution plan that satisfies certain criteria. If so, then the cursor is used to execute the new query. In another approach, one cursor sharing technique (CST) is used to determine which cursor to use to execute a first set of semantically-equivalent queries. Statistics are gathered during execution of the first set of queries. The database server determines, based on the statistics, when to switch from using the first CST to a different CST. The different CST is used to determine which cursor to use to execute a second set of queries that are semantically-equivalent to the first set.