Triggering hard parses

Patent metadata

Field	Value
Publication number	US-9727609-B2
Application number	US-201314041952-A
Country	US
Kind code	B2
Filing date	Sep 30, 2013
Priority date	Sep 28, 2012
Publication date	Aug 8, 2017
Grant date	Aug 8, 2017

Abstract

Techniques for processing queries are provided. In one approach, an execution plan for a query includes multiple sub-plans, one or more of which are selected at runtime while one or more other sub-plans are not executed during execution of the execution plan. In another approach, data about misestimate is generated and stored persistently for subsequent queries. In another approach, statistics for a database object are generated automatically and efficiently while the database object is created or data items are added thereto. In another approach, a hybrid histogram is created that includes a feature of frequency histograms and a feature of height-balanced histograms. In another approach, computer jobs are executed in such a way to avoid deadlock. In another approach, changes to a database object trigger a hard parse of a query even though an execution plan already exists for the query.

First claim

What is claimed is:

1. A method comprising:
in response to receiving a first query, generating a first execution plan that is used to execute the first query and that involves accessing a particular database object;
executing the first query by executing the first execution plan;
storing a change threshold that indicates a first number of changes to the particular database object or a first change in size of the particular database object;
receiving a second query, wherein the first execution plan may be used to execute the second query;
in response to receiving the second query:
determining a change metric that is associated with the particular database object, wherein the change metric indicates a second number of changes to the particular database object or a second change in size of the particular database object; and
determining, based on a difference between the change metric and the change threshold, whether to generate an execution plan for the second query;

in response to determining to generate an execution plan for the second query, generating a second execution plan;
determining whether the second execution plan is the same as the first execution plan; and
modifying the change threshold in response to determining that the second execution plan is the same as the first execution plan;
wherein the method is performed by one or more computing devices.

2. The method of claim 1 , wherein:
the change metric indicates the second number of changes to the particular database object since a time that is previous to a current time.

3. The method of claim 2 , wherein the second number of changes includes one or more of a number of inserts, deletes, or updates to data items in the particular database object.

4. The method of claim 1 , wherein:
the change metric indicates the second change in size of the particular database object.

5. The method of claim 1 , wherein:
the second number of changes to the particular database object is a number of changes since the first execution plan was generated, or
the second change in size is a change in size of the particular database object since the first execution plan was generated.

6. The method of claim 1 , wherein determining whether to generate an execution plan for the second query comprises determining whether the change metric exceeds the change threshold.

7. A method comprising:
while executing a first execution plan, for a first query, that indicates a database object and a plurality of operations, generating particular statistics about an operation, of the plurality of operations, that is (a) a join operation of multiple database objects that includes the database object or (b) a filter operation on the database object, wherein the filter operation includes a predicate that was applied to the database object;
storing the particular statistics that indicate information about the join operation or the filter operation;
after storing the particular statistics and in response to receiving a second query that is not equivalent to the first query, determining whether the particular statistics are relevant to the second query;
wherein the first execution plan cannot be used to generate valid results for the second query;
wherein determining whether the particular statistics are relevant to the second query comprises:
identifying the database object in the second query;
using an identifier of the database object to identify an entry in a data structure that comprises a plurality of entries, each containing certain statistics about one or more operations involving different database objects; and
analyzing the entry to determine whether a particular join or a particular predicate in the second query matches, in the entry, the predicate or a join that was indicated in the first query;

wherein the operation is the join operation that involves the database object and one or more other database objects;
wherein the join indicated in the first query indicates a first order of the database object and the one or more other database objects;
wherein the particular join in the second query indicates a second order of the database object and the one or more other database objects;
wherein the first order is different than the second order;
indicating that the particular join in the second query matches the join indicated in the first query;
in response to determining that the particular statistics are relevant to the second query, optimizing the second query based on the particular statistics, wherein optimizing comprises selecting a second execution plan based on the particular statistics; and
executing the second execution plan.

8. The method of claim 7 , wherein the first query was issued in a first database session and the second query was issued in a second database session that is different than the first database session.

9. The method of claim 1 , wherein:
the change metric is a first change metric;
the first change metric is associated with a first column of the particular database object;
a second change metric that is different than the first change metric is associated with a second column of the particular database object; and
determining whether to generate an execution plan for the second query comprises determining whether to generate an execution plan for the second query based on the first change metric but not on the second change metric.

10. The method of claim 7 , further comprising:
generating second statistics about a second operation that involved one or more second database objects that includes a second database object;
determining, based on one or more criteria, whether to store the second statistics; and
in response to determining that the one or more criteria are not satisfied, refraining from storing the second statistics for a subsequent query.

11. The method of claim 7 , further comprising, prior to storing the particular statistics:
determining, based on one or more criteria, whether to store the particular statistics; and
determining whether to store the particular statistics comprises determining a time that lapsed to execute the first execution plan or determining a number or amount of computer resources that were required to execute the first execution plan.

12. The method of claim 7 , further comprising:
storing execution time data that indicates a time to execute the first execution plan; and
determining, based on the execution time data, an amount of time to devote to processing dynamic statistics that includes the particular statistics.

13. One or more storage media storing instructions which, when executed by one or more processors, further cause:
in response to receiving a first query, generating a first execution plan that is used to execute the first query and that involves accessing a particular database object;
executing the first query by executing the first execution plan;
storing a change threshold that indicates a first number of changes to the particular database object or a first change in size of the particular database object;
receiving a second query, wherein the first execution plan may be used to execute the second query; and
in response to receiving the second query:
determining a change metric that is associated with the particular database object, wherein the change metric indicates a second number of changes to the particular database object or a second change in size of the particular database object; and
determining, based on a difference between the change metric and the change threshold, whether to generate an execution plan for the second query;

in response to determining to generate an e

Assignees

• Oracle Int Corp

Inventors

• Su Hong
• Chakkappen Sunil P
• Zait Mohamed

Classifications

• G06F16/24542 Primary

  Plan optimisation · CPC title

• G06F16/24544 Primary

  Join order optimisation · CPC title

• G06F17/30463

  Physics · mapped topic

• G06F17/30466 Primary

  Physics · mapped topic