Triggering hard parses

• US 9,727,609 B2
• Filed: 09/30/2013
• Issued: 08/08/2017
• Est. Priority Date: 09/28/2012
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Chakkappen, Sunil P., Zait, Mohamed, Su, Hong
• Primary Examiner(s): Mofiz, Apu
• Assistant Examiner(s): Glasser, Dara J
• Application Number:
  US14/041,952
• Publication Number:
  US 20140095475A1
• Time in Patent Office: 1,408 Days
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/24544 Join order optimisation

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.

Claims

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.

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.

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.

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.

18. The method of claim 7, wherein the entry indicates when the certain statistics of the entry were generated or last used.

19. The method of claim 7, wherein the particular statistics include a number of data items that were read from the database object and a number of data items that resulted from the operation.

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

14. The one or more storage media of claim 13, wherein:

the change metric is one or more of a number of inserts, deletes, or updates to data items in the particular database object since a time that is previous to a current time.

15. The one or more storage media of claim 13, wherein:

the change metric is the second change in size of the particular database object.

17. The one or more storage media of claim 13, 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.

16. One or more storage media storing instructions which, when executed by one or more processors, cause:

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 that involves the database object or (b) a filter operation that includes a predicate that is 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.