Server-Side Cursor Mapping for Database Statement Caching
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In database systems, the reuse of cached cursors becomes inefficient when connections and database sessions are reassigned, leading to mismatches between client-side and server-side cursor identifiers, resulting in incorrect execution of SQL statements and increased resource usage due to repeated parsing and analysis.
Innovation Solution
Implementing a server-side mapping of cursor identifiers to SQL statements allows for continued use of client-side statement caches even after connections and database sessions are returned to shared pools, avoiding the need to clear the statement cache across session releases and leveraging server-based pooling solutions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If connections and database sessions are reassigned in shared pools, then resource utilization and scalability are improved, but cursor identifier mismatches occur between client-side and server-side caches
Solution Approach 1:
The patent introduces a proxy server as an intermediary component between the client and the database server. The proxy maintains a local cursor cache that maps cursor identifiers to SQL statements, acting as a mediator when connections are reassigned. When a connection is pooled and reassigned to a different session, the proxy intercepts cursor operations, resolves the identifier mapping locally, and forwards appropriate commands to the database server, thereby maintaining cursor identifier consistency despite connection reuse
Solution Approach 2:
The system segments the cursor caching functionality into separate components: client-side statement cache, proxy-side cursor cache, and server-side session cache. This segmentation allows each component to maintain its own cursor identifier namespace independently. The proxy's local cache serves as a buffer layer that decouples the client's cursor identifiers from the server's session-specific cursors, enabling connection pooling while preserving identifier matching through the segmented architecture
2Reliability
If client-side statement cache is cleared on session release, then cursor identifier mismatches are prevented, but performance and CPU usage deteriorate due to repeated parsing
Solution Approach 1:
The proxy server performs preliminary action by maintaining a local cursor cache that pre-resolves cursor identifier mappings before connections are reassigned. When a connection is pooled and later retrieved, the proxy already has the necessary cursor-to-SQL mapping information cached locally, eliminating the need to clear client-side caches and avoiding repeated parsing operations. This preliminary caching of identifier mappings preserves both reliability and performance
Solution Approach 2:
The proxy acts as an intermediary that absorbs the burden of cursor identifier management. Instead of clearing client-side caches, the proxy intercepts cursor operations and resolves identifier mappings in its own local cache. This mediator approach allows the client-side statement cache to be retained and reused, maintaining query execution efficiency while the proxy ensures cursor identifier accuracy through its own caching layer
Data Source
AI summary
Disclosed is an improved approach to implement cursor sharing in database systems, where a server-side mapping of cursor identifiers to SQL is maintained to allow for continued use of client-side statement caches even after connections and database sessions are returned to shared pools and then subsequently re-assigned. This avoids the need to clear the statement cache across session releases to the database session pools. Instead, applications can retain their statement cache while still leveraging the benefits of server based pooling solutions.