SQL EXECUTION PLAN VERIFICATION

• US 20140236921A1
• Filed: 04/11/2014
• Published: 08/21/2014
• 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
• Granted Patent: US 10,229,158 B2
• Time in Patent Office: Days
• US Class Current: 707/718
• 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 query optimizer configured to generate query plans for executing database commands;

a repository configured to store 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 query optimizer is further configured to select particular query plans, of the query plans generated by the query optimizer, to execute particular database commands, based at least in part on whether the verification data indicates that the particular query plans are within the first set;

a database server configured to execute particular query plans selected by the query optimizer;

a verification component configured to;

perform comparisons of first performance statistics collected during execution of first query plans in the first set of the query plans to second performance statistics collected during execution of second query plans in the second set of the query plans, and update the verification data to move one or more of the second query plans from the second set to the first set based on the comparisons.

2. The system of claim 1, wherein the query optimizer is further configured to determine predicted costs for the generated query plans, and to add 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, further comprising:

a scheduler component that causes collection of at least some of the second performance statistics by causing the database server to execute certain query plans, in the second set of query plans, as background processes when database server resources become available.

4. The system of claim 1, wherein the query optimizer is configured to select query plans from the first set over query plans that are 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;

wherein the query optimizer is configured to select query plans from both the first set and the second set, but not the third set;

wherein the verification component is further configured to update the verification data to move one or more of the second query plans from the second set to the third set, based on the comparisons.

6. The system of claim 1,

wherein the query optimizer is configured to select both a first plan from the first set and a second plan from the second set to execute a first database command;

wherein the database server is configured to concurrently execute both the first plan and the second plan, and return results for the first database command from whichever of the first plan or the second plan finishes executing first;

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

wherein the verification component is configured to update the verification data based on the concurrent execution.

7. The system of claim 1, wherein the verification component is configured to update the verification data to move a particular second query plan for executing a first database command from the second set to the first set based on a particular second performance statistic for the particular second query plan being within a threshold amount of a particular first performance statistic for a particular first query plan for executing the first database command.

8. The system of claim 1, further comprising:

wherein the repository further stores the first performance statistics in association with identifiers of execution contexts in which the first performance statistics were collected;

wherein, prior to certain comparisons of the comparisons, the verification component is configured to determine that the first performance statistics do not include certain statistics collected within a current execution context, and consequently cause the database server to re-execute certain plans of the first query plans in order to collect the certain statistics for the current execution context.

9. A method comprising:

generating query plans for executing database commands;

storing 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;

selecting particular query plans, of the query plans generated by the query optimizer, to execute particular database commands, based at least in part on whether the verification data indicates that the particular query plans are within the first set;

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

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

updating the verification data to move one or more of the second query plans from the second set to the first set based on the comparisons.

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 when database server resources become available.

12. The method of claim 9, further comprising selecting a particular first query plan from the first set over a particular second query plan from the second set, the particular second query plan being predicted to perform better than the particular first query plan, because particular 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 move one or more of the second query plans from the second set to the third set, based on the comparisons.

14. The method of claim 9, further comprising:

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

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

returning results for the request to perform the first database command from whichever of the first plan or the second plan finishes executing first;

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

updating the verification data based on the concurrent execution.

15. The method of claim 9, further comprising updating the verification data to move a particular second query plan for executing a first database command from the second set to the first set based on a particular second performance statistic for the particular second query plan being within a threshold amount of a particular first performance statistic for a particular first query plan for executing the first database command.

16. The method of claim 9:

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

prior to certain comparisons of the comparisons, determining that the first performance statistics do not include certain statistics collected within a current execution context, and consequently re-executing certain plans of the first query plans in order to collect the certain 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;

storing 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;

selecting particular query plans, of the query plans generated by the query optimizer, to execute particular database commands, based at least in part on whether the verification data indicates that the particular query plans are within the first set;

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

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

updating the verification data to move one or more of the second query plans from the second set to the first set based on the comparisons.

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 when database server resources 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 a particular first query plan from the first set over a particular second query plan from the second set, the particular second query plan being predicted to perform better than the particular first query plan, because particular 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 move one or more of the second query plans from the second set to the third set, based on the comparisons.

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 a first database command, selecting both a first plan from the first set and a second plan from the second set to execute the first database command;

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

returning results for the request to perform the first database command from whichever of the first plan or the second plan finishes executing first;

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

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 move a particular second query plan for executing a first database command from the second set to the first set based on a particular second performance statistic for the particular second query plan being within a threshold amount of a particular first performance statistic for a particular first query plan for executing the first 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;

prior to certain comparisons of the comparisons, determining that the first performance statistics do not include certain statistics collected within a current execution context, and consequently re-executing certain plans of the first query plans in order to collect the certain statistics for the current execution context.