US 7,689,550 B2Granted

Adaptive cursor sharing

AssigneeOracle International Corporation

InventorsAllison W. Lee, Mohamed Zait

Life of this patent

Estimating the term…

Loading patent…

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.

Claims24

Dependent claims

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.

8 more elements

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.

8 more elements

Dependencies are inferred from the claim text.