Cursor pre-fetching

• US 20060149702A1
• Filed: 12/20/2004
• Published: 07/06/2006
• Est. Priority Date: 12/20/2004
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Gollapudi, Sreenivas, Kaluskar, Sanjay
• Granted Patent: US 9,489,424 B2
• Time in Patent Office: Days
• 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:

determining a first amount of memory of a computer of a client of a database server, said first amount of memory being for storing records generated by the database server for a cursor, wherein determining the amount of memory is based on;

a respective state of each cursor of a plurality of cursors stored on said database server, and

a second amount of memory of said computer;

determining a first quantity of records based on the first amount; and

in response to the database server receiving a fetch request for a certain quantity of records, satisfying the fetch request by returning at least the first quantity of records to the client.

2. The method of claim 1, the steps further including in response to the database server receiving the fetch request, determining a second quantity of records to return to satisfy the fetch request based on the first quantity of records and the certain quantity of records requested by said 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 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 fetch request.

6. The method of claim 5, wherein the client generates the fetch request within a call made by an application, and wherein the 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 a first quantity of records is based on:

a 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 of said sessions; and

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

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

a 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.

12. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 1.

13. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 2.

14. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 3.

15. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 4.

16. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 5.

17. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 6.

18. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 7.

19. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 8.

20. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 9.

10. A computer-implemented method, the method comprising the steps of:

a client on a computer transmitting a first fetch request to a database server for a plurality of records generated for a cursor;

receiving at least a first quantity of records, wherein the first quantity of records is determined based on a first amount of memory of the computer, said first amount of memory being for storing records generated by the database server for the cursor, wherein the first amount of memory is determined based on;

a respective state of each cursor of a plurality of cursors stored on said database server, and

a second amount of memory of said computer;

storing the first quantity of records in a buffer on a computer;

receiving a second fetch request from an application for at least a portion of said plurality of records generated for said cursor; and

satisfying the second fetch request using records in said buffer.

11. The method of claim 10, wherein:

wherein the application interacts with said database server via an interface that resides on the computer; and

the steps further include;

determining there are an insufficient number of records in said buffer to satisfy said first fetch request;

in response to determining there are an insufficient number of records to satisfy said first fetch request, transmitting a second fetch request to the database server;

receiving in response to said second fetch request a number of records equal to or greater than the first quantity of records; and

storing at least a portion of the records returned in response to the second fetch request in the buffer.

21. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 10.

22. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 11.