Extended Cursor Sharing for Database Performance
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Cursor sharing in database systems is suboptimal when using bind variables, as an execution plan optimal for one bind value may be inefficient for another, leading to unpredictable run-time performance and a trade-off between memory usage and performance.
Innovation Solution
Implement extended and adaptive cursor sharing techniques, where the database server determines whether to share a cursor based on selectivity ranges and execution plan criteria, and adjusts sharing methods based on execution metrics and database state changes.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If cursor sharing is implemented for queries with bind variables, then memory usage is reduced and hard parse operations are minimized, but execution plan optimality deteriorates because an execution plan optimal for one bind value may be inefficient for another
Solution Approach 1:
The patent changes the parameter of cursor sharing from a static binary decision (share or not share) to a dynamic, multi-level approach that considers bind variable values, selectivity ranges, and execution plan characteristics. The system adjusts sharing behavior based on runtime conditions, allowing optimal execution plans to be selected for different bind values while still benefiting from memory savings.
Solution Approach 2:
The patent introduces dynamic cursor sharing where the sharing decision is not fixed but adapts based on runtime conditions. The system monitors execution metrics and database state changes, adjusting cursor sharing behavior dynamically to balance memory efficiency with execution plan optimality for different bind variable values.
2Productivity
If cursor sharing is applied without considering bind variable values, then hard parse operations are reduced, but run-time performance becomes unpredictable due to suboptimal execution plans for different bind values
Solution Approach 1:
The patent implements feedback mechanisms where the database server monitors execution metrics and uses this information to adjust cursor sharing decisions. The system learns from actual execution performance and adapts its sharing behavior to ensure predictable run-time performance while maintaining high productivity.
Solution Approach 2:
The patent changes the approach from static cursor sharing to a multi-level dynamic approach that considers bind variable selectivity ranges and execution plan characteristics, enabling the system to maintain both high productivity and reliable run-time performance.
3Reliability
If extended cursor sharing with selectivity ranges is implemented, then execution plan optimality is improved for varying bind values, but device complexity increases due to additional criteria and adaptive mechanisms
Solution Approach 1:
The patent segments the cursor sharing decision process into multiple independent criteria and levels: bind variable value comparison, selectivity range assessment, execution plan characteristic analysis, and adaptive mechanisms. This segmentation allows the complex problem to be managed through modular, manageable components.
Solution Approach 2:
The patent manages complexity through dynamic adaptability, where the cursor sharing mechanism automatically adjusts its behavior based on runtime conditions. The adaptive nature of the system reduces the need for manual configuration and simplifies operation while maintaining high execution plan optimality.
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.