SQL Execution Plan Baselines

• US 20090106306A1
• Filed: 08/08/2008
• Published: 04/23/2009
• Est. Priority Date: 10/17/2007
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Das, Dinesh, Zait, Mohamed, Belknap, Peter, Yagoub, Khaled, Ozbutun, Cetin, Ziauddin, Mohamed, Su, Hong, Zhu, Yali
• Granted Patent: US 9,189,522 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• 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 maintaining a set of baseline query plans for a database command. Except in rare circumstances, a database server may only execute a command according to a baseline plan, even if the database server predicts that a different plan has a lower cost. The set of baseline plans are plans that, for one reason or another, have been determined to provide acceptable actual performance in at least one execution context. When the database server receives a request to execute a particular command, the database server, if possible, always executes the command according to the lowest predicted cost baseline plan. The database server may evolve the plan baseline to include additional plans by generating and testing new plans in response to new requests to execute the database command, or as part of a query optimization or tuning process.

Claims

1. A method for executing database commands, the method comprising:

persistently maintaining, for a particular command, baseline data indicating a plurality of acceptable plans for executing the particular command;

while persistently maintaining the baseline data, receiving a first request to execute the particular command;

estimating, for each plan in the plurality of acceptable plans, predicted costs for satisfying the first request according to the plan;

based on the predicted costs for each plan in the plurality of acceptable plans, selecting a particular plan from the set of acceptable plans; and

in response to the first request to execute the particular command, executing the particular command according to the particular plan.

2. The method of claim 1, further comprising persistently maintaining, for each particular plan in the plurality of acceptable plans, an outline of the particular plan, from which the particular plan may be reconstructed.

3. The method of claim 1, further comprising, for each plan in the plurality of acceptable plans, the database server selecting the plan to include in the plurality of acceptable plans in response to determining that the plan is optimal for at least one execution context.

4. The method of claim 1, further comprising:

while persistently maintaining the baseline data, receiving a second request to execute the particular command;

estimating, for each plan in the plurality of acceptable plans, second predicted costs for satisfying the second request according to the plan;

based on the second predicted costs for each plan in the plurality of acceptable plans, selecting a second particular plan from the set of acceptable plans, wherein the second particular plan is different than the particular plan; and

in response to the second request to execute the particular command, executing the particular command according to the second particular plan.

5. The method of claim 1, wherein the method is performed in a current execution context, the method further comprising:

while persistently maintaining the baseline data, receiving a second request to execute the particular command;

determining that no plan in the plurality of acceptable plans is capable of satisfying the second request in the current execution context;

determining a new plan for executing the particular command;

in response to the second request to execute the particular command, executing the particular command according to the new plan.

12. A computer-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 1.

13. A computer-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 2.

14. A computer-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 3.

15. A computer-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 4.

16. A computer-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 5.

6. A method for managing a set of acceptable execution plans for a database command, the method comprising:

persistently maintaining, for a particular command, baseline data indicating a plurality of acceptable plans for executing the particular command;

while persistently maintaining the baseline data, receiving a first request to execute the particular command;

generating a first plan for executing the particular command;

if the first plan is in the plurality of acceptable plans, executing the particular command according to the first plan; and

if the first plan is not in the plurality of acceptable plans;

in response to the request to execute the particular command, executing the particular command according to a plan in the plurality of acceptable plans;

adding the first plan to a set of potentially acceptable plans;

subsequent to executing the particular command and adding the first plan to the set of potentially acceptable plans, determining that the plan is acceptable and adding the plan to the plurality of acceptable plans.

7. The method of claim 6 wherein:

the information indicating each plan in the set of potential acceptable plans is stored in a plan history for the particular command; and

wherein the set of potential acceptable plans is the subset of plans in the plan history that are not indicated by the baseline data.

8. The method of claim 6 wherein determining that the plan is acceptable comprises:

in a scheduled job, executing the plan;

while executing the plan, capturing performance statistics related to the execution of the plan;

determining to accept the plan based, at least in part, on the captured performance statistics.

9. The method of claim 6 wherein determining that the plan is acceptable comprises accepting the plan in response to input from a user.

17. A computer-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 6.

18. A computer-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 7.

19. A computer-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 8.

20. A computer-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 9.

10. A method for managing a set of acceptable execution plans for a database command, the method comprising:

persistently maintaining, for a particular command, baseline data indicating a plurality of acceptable plans for executing the particular command;

wherein the plurality of acceptable plans is such that, for a current execution context, if any plan in the plurality of acceptable plans is capable of correctly executing the particular command, the database server must execute the particular command according to one plan from the plurality of acceptable plans;

during a query optimization process for the current execution context, storing profile data for the particular database command;

in response to storing the profile data, determining a plan for executing the particular command in the current execution context, said determining being based on the profile data; and

adding, to the baseline data, data indicating the determined plan.

11. The method of claim 10, further comprising storing, in a plan history, an outline of the determined plan, from which the determined plan may be reconstructed.

21. A computer-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 10.

22. A computer-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 11.