Cursor pre-fetching

摘要

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.

主权项

1. A computer-implemented method, the method comprising the steps of:
satisfying at least one fetch request using a first cursor of a plurality of cursors, wherein each cursor of said plurality of cursors is stored on a database server and holds information about execution of a respective query issued for a client running on a computer; after satisfying at least one fetch request using the first cursor of the plurality of cursors, determining a first amount of memory of said computer on which said client of the database server is running, said first amount of memory being available to said client for storing records generated by the database server for said first cursor, wherein determining the first amount of memory includes:
(1) determining a respective cursor state of each cursor of said plurality of cursors, said respective cursor state being one of a plurality of cursor states, each cursor state of said plurality of cursor states representing a phase of execution of said respective query, and(2) determining a second amount of memory available to said client running on the computer; determining a first quantity of records to pre-fetch based on the first amount of memory; and in response to the database server receiving another fetch request for a certain quantity of records, satisfying said another fetch request using said first cursor by returning at least the first quantity of pre-fetched records to the client.