Cursor Pre-fetching for Faster Session Migration
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In multi-node database systems, the inefficiency in fetching cursor result sets leads to increased network traffic and resource consumption due to cursors remaining in the fetch state longer, which hinders performance and resource allocation, especially during session migration.
Innovation Solution
Implementing pre-fetching techniques to determine and fetch a larger quantity of rows based on available client memory and fetch state, using pre-fetch buffers to hasten the completion of the fetch phase and facilitate earlier session migration.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If traditional fetching methods are used, then network traffic and resource consumption increase, but implementing pre-fetching requires additional memory management complexity
Solution Approach 1:
The patent implements pre-fetching by determining in advance how many rows can be fetched based on available client memory, then fetching that quantity before the application actually requests it. This preliminary action reduces the number of fetch requests and improves fetching efficiency while managing memory proactively rather than reactively
Solution Approach 2:
The system continuously monitors available client memory and uses this feedback to dynamically adjust the number of rows to pre-fetch. By checking memory availability and adapting the fetch quantity accordingly, the system optimizes fetching efficiency while preventing memory overflow conditions
2Quantity of substance
If cursors remain in fetch state longer, then more data can be retrieved, but system performance and resource allocation are hindered
Solution Approach 1:
The patent determines the optimal fetch quantity in advance by calculating available client memory and estimating how many rows can be retrieved without keeping cursors in the fetch state too long. This preliminary calculation balances data retrieval volume with system performance by preventing excessive cursor dwell time in the fetch state
Solution Approach 2:
The system dynamically adjusts the fetch quantity based on real-time memory availability and cursor state. By making the fetch quantity adaptive rather than fixed, the system can retrieve appropriate data volumes while automatically reducing fetch state duration when memory constraints or performance considerations arise
3Quantity of substance
If pre-fetching is implemented to hasten session migration, then memory utilization improves, but fetch phase completion must be accelerated
Solution Approach 1:
The patent performs pre-fetching as a preliminary action before session migration is needed. By determining available memory and fetching rows in advance, the system hastens the completion of the fetch phase, allowing session migration to proceed sooner while effectively utilizing available memory resources
Solution Approach 2:
The system rushes through the fetch phase by pre-fetching rows in bulk before the application needs them. This skipping approach completes the fetch phase faster than traditional on-demand fetching, enabling earlier session migration while making efficient use of available client memory
Data Source
AI summary
Techniques determine a quantity of rows to fetch based on memory available on a client computer and the fetch state of cursors. The techniques are used to determine a quantity of rows for “pre-fetching”. Under pre-fetching, rows in addition to those requested by an application are fetched from a database instance and stored in a “pre-fetch buffer”. The pre-fetch quantities are based on an amount of memory that is deemed available for pre-fetch buffers on a client computer and the number of cursors in the fetch state for a session at a given moment. Pre-fetching is used to hasten session migration. By fetching a quantity of rows that is larger than would be fetched if only the application-specified quantity were fetched, the fetch phase of a cursor is completed sooner. An earlier completion of the fetch phase of a session's cursors allows migration of the session to be completed sooner.