SQL execution plan verification

• US 10,229,158 B2
• Filed: 04/11/2014
• Issued: 03/12/2019
• Est. Priority Date: 10/17/2007
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Belknap, Peter, Dageville, Benoit, Das, Dinesh, Yagoub, Khaled, Zait, Mohamed, Ziauddin, Mohamed
• Primary Examiner(s): Chen, Susan
• Application Number:
  US14/251,521
• Publication Number:
  US 20140236921A1
• Time in Patent Office: 1,796 Days
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/24545 Selectivity estimation or determination

Abstract

Approaches, techniques, and mechanisms are disclosed for ensuring that a database command is executed according to a query plan that has been verified to be actually optimal. Except in rare circumstances, a database server does not execute a query plan unless it is first verified by the database server. The database server receives a request to execute a database command. The database server determines an unverified plan is the best plan for satisfying the request. Rather than risk the unknown behavior of an unverified plan, the database server instead satisfies the request according to a verified plan. Subsequently—for example as part of a scheduled job—the database server executes the unverified plan to determine performance statistics. Based at least on the performance statistics, the database server determines whether or not to verify the unverified plan. Techniques for concurrent and optimistic verifications are also disclosed.

Claims

1. A system comprising:

a persistent storage device;

one or more processors, operatively coupled to the persistent storage device;

memory, operatively coupled to the one or more processors, storing instructions executable by the one or more processors;

wherein the instructions include instructions which, when executed by the one or more processors, cause;

generating query plans for executing database commands;

wherein the query plans include a first query plan for performing operations specified by a particular database command and a second query plan for performing the operations specified by the particular database command;

wherein the first query plan is a plan for performing the operations specified by the particular database command in a manner that is different than how the second query plan performs the operations specified by the particular database command;

storing, at the persistent storage device, verification data that identifies a first set of the query plans as having been designated as acceptable for executing database commands, and a second set of the query plans as awaiting verification;

wherein the verification data indicates that the first query plan is in the first set of query plans, and therefore designated as acceptable for executing the particular database command;

wherein the verification data indicates that the second query plan is in the second set of query plans, and therefore awaiting verification as to whether the second query plan is acceptable for executing the particular database command;

executing first query plans in the first set of query plans and second query plans in the second set of query plans;

wherein executing the first query plans includes executing the first query plan;

wherein executing the second query plans includes executing the second query plan;

performing comparisons of first performance statistics collected during execution of the first query plans in the first set of query plans to second performance statistics collected during execution of the second query plans in the second set of query plans;

wherein performing comparisons includes comparing performance statistics collected during execution of the first query plan with performance statistics collected during execution of the second query plan; and

based on the comparisons, updating the verification data to cause;

the first set to include the second query plan, and

the second set to cease to include the second query plan.

2. The system of claim 1, wherein the instructions include instructions which, when executed by the one or more processors, further cause:

determining predicted costs for the generated query plans, and

adding a newly generated query plan that is not in the first set to the second set responsive to determining that a predicted cost for the newly generated query plan is within a threshold amount of a predicted cost for another query plan, in the first set, that is capable of performing the same database command as the newly generated query plan.

3. The system of claim 1, wherein the instructions include instructions which, when executed by the one or more processors, further cause:

collecting at least some of the second performance statistics by executing certain query plans, in the second set of query plans, as background processes become available.

4. The system of claim 1, wherein the instructions include instructions which, when executed by the one or more processors, further cause selecting, for execution of the particular database command, the first query plan from the first set over the second query plan from the second set, the second query plan being predicted to perform better than the first query plan, because the second query plan is not in the first set.

5. The system of claim 1, wherein:

the verification data further identifies a third set of query plans that have been designated as unacceptable;

the instructions include instructions which, when executed by the one or more processors, further cause;

selecting a particular query plan from the first set or the second set over a particular third query plan from the third set, the particular third query plan being predicted to perform better than the particular query plan, because the particular third query plan is in the third set;

updating the verification data to cause one or more of the second query plans from the second set to (a) to be included in the third set, and (b) to cease to be included in the second set.

6. The system of claim 1, wherein the instructions include instructions which, when executed by the one or more processors, further cause:

in response to a request to perform the particular database command, selecting both the first query plan from the first set and the second query plan from the second set to execute the particular database command;

executing, concurrently, both the first query plan and the second query plan in response to the request;

wherein a particular plan, of the first query plan and the second query plan, finishes executing before the other plan;

returning results for the request to perform the particular database command from the particular plan; and

updating the verification data based on the concurrent execution.

7. The system of claim 1, wherein the instructions include instructions which, when executed by the one or more processors, further cause updating the verification data to cause the second query plan for executing the particular database command from the second set to (a) be included in the first set, and (b) cease to be included in the second set, based on a particular second performance statistic for the second query plan being within a threshold amount of a particular first performance statistic for the first query plan for executing the particular database command.

8. The system of claim 1,

wherein the comparisons include particular comparisons;

wherein the persistent storage device further stores the first performance statistics in association with identifiers of execution contexts in which the first performance statistics were collected; and

wherein the instructions include instructions which, when executed by the one or more processors, further cause;

prior to performing the particular comparisons, determining that the first performance statistics do not include particular statistics collected within a current execution context; and

in response to determining that the first performance statistics do not include particular statistics, re-executing particular plans of the first query plans in order to collect the particular statistics for the current execution context.

9. A method comprising:

generating query plans for executing database commands;

wherein the query plans include a first query plan for performing operations specified by a particular database command and a second query plan for performing the operations specified by the particular database command;

wherein the first query plan is a plan for performing the operations specified by the particular database command in a manner that is different than how the second query plan performs the operations specified by the particular database command;

storing, at a persistent storage accessible to one or more computing devices, verification data that identifies a first set of the query plans as having been designated as acceptable for executing database commands, and a second set of the query plans as awaiting verification;

wherein the verification data indicates that the first query plan is in the first set of query plans, and therefore designated as acceptable for executing the particular database command;

wherein the verification data indicates that the second query plan is in the second set of query plans, and therefore awaiting verification as to whether the second query plan is acceptable for executing the particular database command;

executing first query plans in the first set of query plans and second query plans in the second set of query plans;

wherein executing the first query plans includes executing the first query plan;

wherein executing the second query plans includes executing the second query plan;

performing comparisons of first performance statistics collected during execution of the first query plans in the first set of query plans to second performance statistics collected during execution of the second query plans in the second set of query plans;

wherein performing comparisons includes comparing performance statistics collected during execution of the first query plan with performance statistics collected during execution of the second query plan; and

based on the comparisons, updating the verification data to cause;

the first set to include the second query plan, and

the second set to cease to include the second query plan;

wherein the method is performed by the one or more computing devices.

10. The method of claim 9, further comprising:

determining predicted costs for the generated query plans;

adding a newly generated query plan that is not in the first set to the second set responsive to determining that a predicted cost for the newly generated query plan is within a threshold amount of a predicted cost for another query plan, in the first set, that is capable of performing the same database command as the newly generated query plan.

11. The method of claim 9, further comprising collecting at least some of the second performance statistics by executing certain query plans, in the second set of query plans, as background processes become available.

12. The method of claim 9, further comprising selecting, for execution of the particular database command, the first query plan from the first set over the second query plan from the second set, the second query plan being predicted to perform better than the first query plan, because the second query plan is not in the first set.

13. The method of claim 9, wherein the verification data further identifies a third set of query plans that have been designated as unacceptable, the method further comprising:

selecting a particular query plan from the first set or the second set over a particular third query plan from the third set, the particular third query plan being predicted to perform better than the particular query plan, because the particular third query plan is in the third set;

updating the verification data to cause one or more of the second query plans from the second set to (a) to be included in the third set, and (b) to cease to be included in the second set.

14. The method of claim 9, further comprising:

in response to a request to perform the particular database command, selecting both the first query plan from the first set and the second query plan from the second set to execute the particular database command;

concurrently executing both the first query plan and the second query plan in response to the request;

wherein a particular plan, of the first query plan and the second query plan, finishes executing before the other plan;

returning results for the request to perform the particular database command from the particular plan; and

updating the verification data based on the concurrent execution.

15. The method of claim 9, further comprising updating the verification data to cause the second query plan for executing the particular database command from the second set to (a) be included in the first set, and (b) cease to be included in the second set, based on a particular second performance statistic for the second query plan being within a threshold amount of a particular first performance statistic for the first query plan for executing the particular database command.

16. The method of claim 9, further comprising:

storing the first performance statistics in association with identifiers of execution contexts in which the first performance statistics were collected;

wherein the comparisons include particular comparisons;

prior to performing the particular comparisons, determining that the first performance statistics do not include particular statistics collected within a current execution context; and

in response to determining that the first performance statistics do not include particular statistics, re-executing particular plans of the first query plans in order to collect the particular statistics for the current execution context.

17. One or more non-transitory computer-readable media, storing instructions that, when executed by one or more computing devices, cause performance of:

generating query plans for executing database commands;

wherein the query plans include a first query plan for performing operations specified by a particular database command and a second query plan for performing the operations specified by the particular database command;

wherein the first query plan is a plan for performing the operations specified by the particular database command in a manner that is different than how the second query plan performs the operations specified by the particular database command;

storing, at a persistent storage accessible to one or more computing devices, verification data that identifies a first set of the query plans as having been designated as acceptable for executing database commands, and a second set of the query plans as awaiting verification;

wherein the verification data indicates that the first query plan is in the first set of query plans, and therefore designated as acceptable for executing the particular database command;

wherein the verification data indicates that the second query plan is in the second set of query plans, and therefore awaiting verification as to whether the second query plan is acceptable for executing the particular database command;

executing first query plans in the first set of query plans and second query plans in the second set of query plans;

wherein executing the first query plans includes executing the first query plan;

wherein executing the second query plans includes executing the second query plan;

performing comparisons of first performance statistics collected during execution of the first query plans in the first set of query plans to second performance statistics collected during execution of the second query plans in the second set of query plans;

wherein performing comparisons includes comparing performance statistics collected during execution of the first query plan with performance statistics collected during execution of the second query plan; and

based on the comparisons, updating the verification data to cause;

the first set to include the second query plan, and

the second set to cease to include the second query plan.

18. The one or more non-transitory computer-readable media of claim 17, wherein the instructions, when executed by the one or more computing devices, further cause performance of:

determining predicted costs for the generated query plans;

adding a newly generated query plan that is not in the first set to the second set responsive to determining that a predicted cost for the newly generated query plan is within a threshold amount of a predicted cost for another query plan, in the first set, that is capable of performing the same database command as the newly generated query plan.

19. The one or more non-transitory computer-readable media of claim 17, wherein the instructions, when executed by the one or more computing devices, further cause performance of collecting at least some of the second performance statistics by executing certain query plans, in the second set of query plans, as background processes become available.

20. The one or more non-transitory computer-readable media of claim 17, wherein the instructions, when executed by the one or more computing devices, further cause performance of selecting, for execution of the particular database command, the first query plan from the first set over the second query plan from the second set, the second query plan being predicted to perform better than the first query plan, because the second query plan is not in the first set.

21. The one or more non-transitory computer-readable media of claim 17, wherein the verification data further identifies a third set of query plans that have been designated as unacceptable, wherein the instructions, when executed by the one or more computing devices, further cause performance of:

selecting a particular query plan from the first set or the second set over a particular third query plan from the third set, the particular third query plan being predicted to perform better than the particular query plan, because the particular third query plan is in the third set;

updating the verification data to cause one or more of the second query plans from the second set to (a) to be included in the third set, and (b) to cease to be included in the second set.

22. The one or more non-transitory computer-readable media of claim 17, wherein the instructions, when executed by the one or more computing devices, further cause performance of:

in response to a request to perform the particular database command, selecting both the first query plan from the first set and the second query plan from the second set to execute the particular database command;

concurrently executing both the first query plan and the second query plan in response to the request;

wherein a particular plan, of the first query plan and the second query plan, finishes executing before the other plan;

returning results for the request to perform the particular database command from the particular plan; and

updating the verification data based on the concurrent execution.

23. The one or more non-transitory computer-readable media of claim 17, wherein the instructions, when executed by the one or more computing devices, further cause performance of updating the verification data to cause the second query plan for executing the particular database command from the second set to (a) be included in the first set, and (b) cease to be included in the second set, based on a particular second performance statistic for the second query plan being within a threshold amount of a particular first performance statistic for the first query plan for executing the particular database command.

24. The one or more non-transitory computer-readable media of claim 17, wherein the instructions, when executed by the one or more computing devices, further cause performance of:

storing the first performance statistics in association with identifiers of execution contexts in which the first performance statistics were collected;

wherein the comparisons include particular comparisons;

prior to performing the particular comparisons, determining that the first performance statistics do not include particular statistics collected within a current execution context; and

in response to determining that the first performance statistics do not include particular statistics, re-executing particular plans of the first query plans in order to collect the particular statistics for the current execution context.