Adaptive cursor sharing

• US 7,689,550 B2
• Filed: 07/31/2007
• Issued: 03/30/2010
• 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.
• Primary Examiner(s): Ali; Mohammad
• Assistant Examiner(s): FILIPCZYK, MARCIN R
• Application Number:
  US11/831,952
• Publication Number:
  US 20090037405A1
• Time in Patent Office: 973 Days
• Field of Search: 707 1- 6, 707/10, 707/100, 707/101, 707/200, 707/203
• 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:

while a particular cursor is in a first state, performing the steps of;

determining, using a first cursor sharing technique, whether the particular cursor that was created for a particular query should be used to execute one or more queries;

in response to determining that the particular cursor should be used to execute the one or more queries, using the particular cursor to execute the one or more queries;

in response to determining that the particular cursor should not be used to execute the one or more queries, executing the one or more queries without using the particular cursor;

generating statistics that reflect suitability of using the first cursor sharing technique to execute queries that are semantically-equivalent to the particular query;

after executing the one or more queries and generating the statistics, determining whether the statistics indicate that the first cursor sharing technique is suitable to use to execute queries that are semantically-equivalent to the particular query;

in response to determining that the statistics indicate that the first cursor sharing technique is not suitable to use to execute queries that are semantically-equivalent to the particular query, changing the state of the particular cursor from the first state to a second state that is different than the first state; and

while the particular cursor is in the second state, using a second cursor sharing technique to determine whether to use the particular cursor to execute a subsequently-received 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:

for a first cursor that has already been generated for a first query, always using the first cursor for queries that are received subsequent to the first query and that are semantically equivalent to the first query.

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

for a second cursor that has already been generated for a second query, using the second cursor for certain queries that are received subsequent to the second query and that are semantically equivalent to the second query only if certain statistics generated for the certain queries satisfy certain criteria;

wherein the first cursor is the same as the second cursor;

wherein the first query is the same as the second query.

4. The method of claim 3, wherein:

the first technique is the first cursor sharing technique; and

the second technique is the second cursor sharing technique.

5. The method of claim 3, wherein:

the first technique is the second cursor sharing technique; and

the second technique is the first cursor sharing technique.

6. The method of claim 2, wherein:

the first technique is the first cursor sharing technique;

the second technique is the second cursor sharing technique;

the second technique involves, for the first cursor, always using, while in the first cursor is in the second state, the first cursor for first queries that are subsequently-received and semantically equivalent to the first query; and

the second technique does not involve generating, while the first cursor is in the second state, statistics during execution of any queries that are received subsequent to the first query and that are semantically equivalent to the first query.

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

for a first cursor that has already been generated for a first query, using the first cursor for queries that are (a) received subsequent to the first query and (b) semantically equivalent to the first query, only if statistics generated for the queries satisfy certain criteria.

8. The method of claim 1, wherein determining whether the statistics indicate that the first cursor sharing technique is suitable to use to execute queries that are semantically-equivalent to the particular query includes determining whether the statistics reflect a variation that exceeds a threshold variation.

9. The method of claim 1, wherein determining whether the statistics indicate that the first cursor sharing technique is suitable to use to execute queries that are semantically-equivalent to the particular query includes determining whether a threshold number of a plurality of execution plans have been generated, using the first cursor sharing technique, that are substantially equivalent to an execution plan associated with the particular cursor that is used to execute the one or more queries.

10. 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 input/output operations that occurred during execution of the one or more queries, or

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

11. 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 particular cursor used to execute the one or more queries.

12. The method of claim 1, wherein:

the particular cursor is associated with a plurality of buckets that each represents 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 particular cursor and (2) that are associated with certain statistics that fall within the range of values that correspond to said each bucket.

13. One or more volatile machine-readable media or non-volatile machine-readable media comprising memory and storing instructions which, when executed by one or more processors, cause:

while a particular cursor is in a first state, performing the steps of;

determining, using a first cursor sharing technique, whether the particular cursor that was created for a particular query should be used to execute one or more queries;

in response to determining that the particular cursor should be used to execute the one or more queries, using the particular cursor to execute the one or more queries;

in response to determining that the particular cursor should not be used to execute the one or more queries, executing the one or more queries without using the particular cursor;

generating statistics that reflect suitability of using the first cursor sharing technique to execute queries that are semantically-equivalent to the particular query;

after executing the one or more queries and generating the statistics, determining whether the statistics indicate that the first cursor sharing technique is suitable to use to execute queries that are semantically-equivalent to the particular query;

in response to determining that the statistics indicate that the first cursor sharing technique is not suitable to use to execute queries that are semantically-equivalent to the particular query, changing the state of the particular cursor from the first state to a second state that is different than the first state; and

while the particular cursor is in the second state, using a second cursor sharing technique to determine whether to use the particular cursor to execute a subsequently-received 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.

14. The one or more volatile machine-readable media or non-volatile machine-readable media of claim 13, wherein the first technique involves:

for a first cursor that has already been generated for a first query, always using the first cursor for queries that are received subsequent to the first query and that are semantically equivalent to the first query.

15. The one or more volatile machine-readable media or non-volatile machine-readable media of claim 14, wherein the second technique involves:

for a second cursor that has already been generated for a second query, using the second cursor for certain queries that are (a) received subsequent to the second query and (b) semantically equivalent to the second query, only if certain statistics generated for the certain queries satisfy certain criteria;

wherein the first cursor is the same as the second cursor;

wherein the first query is the same as the second query.

16. The one or more volatile machine-readable media or non-volatile machine-readable media of claim 15, wherein:

the first technique is the first cursor sharing technique; and

the second technique is the second cursor sharing technique.

17. The one or more volatile machine-readable media or non-volatile machine-readable media of claim 15, wherein:

the first technique is the second cursor sharing technique; and

the second technique is the first cursor sharing technique.

18. The one or more volatile machine-readable media or non-volatile machine-readable media of claim 14, wherein:

the first technique is the first cursor sharing technique;

the second technique is the second cursor sharing technique;

the second technique involves, for the first cursor, always using, while in the first cursor is in the second state, the first cursor for first queries that are subsequently-received and semantically equivalent to the first query; and

the second technique does not involve generating, while the first cursor is in the second state, statistics during execution of any queries that are received subsequent to the first query and that are semantically equivalent to the first query.

19. The one or more volatile machine-readable media or non-volatile machine-readable media of claim 13, wherein the first technique involves:

for a first cursor that has already been generated for a first query, using the first cursor for queries that are received subsequent to the first query and that are semantically equivalent to the first query only if statistics generated for the queries satisfy certain criteria.

20. The one or more volatile machine-readable media or non-volatile machine-readable media of claim 13, wherein determining whether the statistics indicate that the first cursor sharing technique is suitable to use to execute queries that are semantically-equivalent to the particular query includes determining whether the statistics reflect a variation that exceeds a threshold variation.

21. The one or more volatile machine-readable media or non-volatile machine-readable media of claim 13, wherein determining whether the statistics indicate that the first cursor sharing technique is suitable to use to execute queries that are semantically-equivalent to the particular query includes determining whether a threshold number of a plurality of execution plans have been generated, using the first cursor sharing technique, that are substantially equivalent to an execution plan associated with the particular cursor that is used to execute the one or more queries.

22. The one or more volatile machine-readable media or non-volatile machine-readable media of claim 13, 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 input/output operations that occurred during execution of the one or more queries, or

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

23. The one or more volatile machine-readable media or non-volatile machine-readable media of claim 13, 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 particular cursor used to execute the one or more queries.

24. The one or more volatile machine-readable media or non-volatile machine-readable media of claim 13, wherein:

the particular cursor is associated with a plurality of buckets that each represents 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 particular cursor and (2) that are associated with certain statistics that fall within the range of values that correspond to said each bucket.