Cursor pre-fetching

• US 9,489,424 B2
• Filed: 12/20/2004
• Issued: 11/08/2016
• Est. Priority Date: 12/20/2004
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Kaluskar, Sanjay, Gollapudi, Sreenivas
• Primary Examiner(s): Survillo, Oleg
• Application Number:
  US11/018,101
• Publication Number:
  US 20060149702A1
• Time in Patent Office: 4,341 Days
• Field of Search: 707/2, 707/713, 707/619, 709/217
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2438 Embedded query languages
• CPC Class Codes: G06F 16/2455 Query execution
• CPC Class Codes: G06F 16/24552 Database cache management
• CPC Class Codes: G06F 16/24561 Intermediate data storage techniques for performance improvement
• CPC Class Codes: H04L 2012/5681 Buffer or queue management
• CPC Class Codes: H04L 49/9005 using dynamic buffer space allocation
• CPC Class Codes: H04L 67/5681 Pre-fetching or pre-delivering data based on network characteristics

Abstract

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.

Claims

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.

2. The method of claim 1, the steps further including in response to the database server receiving said another fetch request, determining a second quantity of records to return to satisfy the another fetch request based on the first quantity of records and the certain quantity of records requested by said another fetch request.

3. The method of claim 2, wherein the step of determining a second quantity of records includes determining to return at least a sum of the first quantity of records and the certain quantity of records requested by the another fetch request.

4. The method of claim 1, wherein determining a first amount of memory and determining a first quantity of records is performed by the database server in response to the database server receiving a request to migrate a session.

5. The method of claim 1, wherein in response to the database server receiving a request to migrate a session, the database server transmits a message to the client, wherein the message causes the client to generate said another fetch request.

6. The method of claim 5, wherein the client generates said another fetch request within a call made by an application, and wherein said another fetch request is made before the call returns to the application.

7. The method of claim 1, wherein determining a first quantity of records is based on:

a number of sessions established on said database server for clients that reside on said computer;

a number of open cursors associated with a particular session of said sessions; and

a size of records generated for the particular cursor.

8. The method of claim 1, wherein determining the first quantity of records is based on:

the second amount of memory used to store data received over a network before a network protocol makes the data available to a requestor of said data;

a number of open cursors associated with a particular session; and

a size of records generated for a particular cursor of said open cursors.

9. The method of claim 1, wherein determining the first quantity of records is based on:

the second amount of memory calculated based on;

a number of sessions established on said database server for clients that reside on said computer,

a number of open cursors associated with a particular session of said sessions, and

a size of records generated for a particular cursor of said open cursors;

a third amount of memory calculated based on;

an amount of memory used to store data received over a network before a network protocol makes the data available to a requestor of said data,

the number of open cursors associated with the particular session of said sessions, and

the size of records generated for the particular cursor of said open cursors; and

the minimum of the second amount and the third amount.

10. A computer-readable non-transitory storage medium storing one or more sequences of instructions which, when executed by one or more processors, cause:

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.

11. The computer-readable non-transitory storage medium of claim 10, wherein the one or more sequences of instructions further include instructions, that when executed by said one or more processors, cause, in response to the database server receiving said another fetch request, determining a second quantity of records to return to satisfy the another fetch request based on the first quantity of records and the certain quantity of records requested by said another fetch request.

12. The computer-readable non-transitory storage medium of claim 11, wherein determining a second quantity of records includes determining to return at least a sum of the first quantity of records and the certain quantity of records requested by the another fetch request.

13. The computer-readable non-transitory storage medium of claim 10, wherein determining a first amount of memory and determining a first quantity of records is performed by the database server in response to the database server receiving a request to migrate a session.

14. The computer-readable non-transitory storage medium of claim 10, wherein the one or more sequences of instructions further include instructions, that when executed by said one or more processors, cause the database server to perform the step of transmmitting a message to the client in response to the database server receiving a request to migrate a session, wherein the message causes the client to generate said another fetch request.

15. The computer-readable non-transitory storage medium of claim 14, wherein the client generates said another fetch request within a call made by an application, and wherein said another fetch request is made before the call returns to the application.

16. The computer-readable non-transitory storage medium of claim 10, wherein determining a first quantity of records is based on:

a number of sessions established on said database server for clients that reside on said computer;

a number of open cursors associated with a particular session of said sessions; and

a size of records generated for the particular cursor.

17. The computer-readable non-transitory storage medium of claim 10, wherein determining the first quantity of records is based on:

the second amount of memory used to store data received over a network before a network protocol makes the data available to a requestor of said data;

a number of open cursors associated with a particular session; and

a size of records generated for a particular cursor of said open cursors.

18. The computer-readable non-transitory storage medium of claim 10, wherein determining the first quantity of records is based on:

the second amount of memory calculated based on;

a number of sessions established on said database server for clients that reside on said computer,

a number of open cursors associated with a particular session of said sessions, and

a size of records generated for a particular cursor of said open cursors;

a third amount of memory calculated based on;

an amount of memory used to store data received over a network before a network protocol makes the data available to a requestor of said data,

the number of open cursors associated with the particular session of said sessions, and

the size of records generated for the particular cursor of said open cursors; and

the minimum of the second amount and the third amount.