ADAPTIVE CURSOR SHARING

• US 20090037405A1
• Filed: 07/31/2007
• Published: 02/05/2009
• Est. Priority Date: 07/31/2007
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Zait, Mohamed, Lee, Allison W.
• Granted Patent: US 7,689,550 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching

Abstract

Techniques for sharing cursors are provided. When a new query is issued, a database server determines whether the new query is semantically equivalent to a previous query. If so, then database server computes statistics associated with the new query. Based on the statistics, the database server determines whether compiling the new query would produce an execution plan that satisfies certain criteria. If so, then the cursor is used to execute the new query. In another approach, one cursor sharing technique (CST) is used to determine which cursor to use to execute a first set of semantically-equivalent queries. Statistics are gathered during execution of the first set of queries. The database server determines, based on the statistics, when to switch from using the first CST to a different CST. The different CST is used to determine which cursor to use to execute a second set of queries that are semantically-equivalent to the first set.

Claims

1. A method comprising:

determining, using a first cursor sharing technique, which cursor to use to execute one or more queries that are semantically equivalent to a particular query;

generating statistics during execution of the one or more queries;

after executing the one or more queries and generating the statistics, determining whether the statistics satisfy a particular condition; and

in response to determining that the statistics satisfy the particular condition, using a second cursor sharing technique to determine which cursor to use to execute a subsequently-received query that is semantically equivalent to the particular query;

wherein the first cursor sharing technique is one of a first technique or a second technique;

wherein the second cursor sharing technique is the other of the first technique and the second technique.

2. The method of claim 1, wherein the first technique involves:

determining whether a cursor has already been generated for a previously-received semantically-equivalent query, and

in response to determining that a cursor has already been generated for a previously-received semantically-equivalent query, always using the cursor for subsequently-received semantically-equivalent queries.

3. The method of claim 1, wherein the second technique involves:

determining whether a cursor has already been generated for a previously-received semantically-equivalent query, and

in response to determining that a cursor has already been generated for a previously-received semantically-equivalent query, using the cursor for subsequently-received semantically-equivalent queries only if statistics generated for the subsequently-received semantically-equivalent queries satisfy certain criteria.

4. The method of claim 1, wherein the particular condition is a threshold variation among the statistics.

5. The method of claim 1, wherein the particular condition is a threshold number of execution plans are generated, using the first cursor sharing technique, that are substantially equivalent to an execution plan associated with the cursor that is used to execute the one or more queries.

6. The method of claim 1, wherein the statistics indicate at least one of the following:

a number of rows produced by one or more operations in an execution plan that was used to execute the one or more queries,

a number of CPU cycles required to execute the one or more queries,

a number of I/O operations that occurred during execution of the one or more queries, or

an amount of memory required to execute the one or more queries.

7. The method of claim 1, wherein:

the statistics indicate a number of compilations of queries that are semantically-equivalent to the particular query; and

the compilations include compilations that generated execution plans that were substantially equivalent to one or more execution plans associated with the cursor used to execute the one or more queries.

8. The method of claim 2, wherein:

the cursor is associated with a plurality of buckets that each represent a range of values associated with a type of data that the statistics indicate; and

each bucket of the plurality of buckets is associated with a value that indicates a number of query executions (1) that used the cursor and (2) that are associated with certain statistics that fall within the range of values that correspond to said each bucket.

9. A machine-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.

10. A machine-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.

11. A machine-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.

12. A machine-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.

13. A machine-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.

14. A machine-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.

15. A machine-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.

16. A machine-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.