SQL execution plan baselines

• US 9,189,522 B2
• Filed: 08/08/2008
• Issued: 11/17/2015
• 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, Ozbutun, Cetin, Su, Hong, Zait, Mohamed, Zhu, Yali, Ziauddin, Mohamed, Belknap, Peter, Yagoub, Khaled
• Primary Examiner(s): Chen, Susan
• Application Number:
  US12/188,678
• Publication Number:
  US 20090106306A1
• Time in Patent Office: 2,657 Days
• Field of Search: 707/1, 707/102, 707/713, 717/101, 717/121, 717/123, 705/2, 705/37
• 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 comprising:

identifying a plurality of query execution plans that have been designated as acceptable for use by a database server to execute a database command, the database command being a query statement that instructs the database server to perform one or more operations on data stored in a database;

persistently maintaining, at the database server, for the database command, baseline data specifying the plurality of query execution plans that have been designated as acceptable for executing the database command, wherein the baseline data includes a plurality of query execution plan outlines, wherein the plurality of query execution plan outlines is a set of data from which the database server reconstructs each query execution plan in the plurality of query execution plans;

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

estimating, by the database server, for each given query execution plan in the plurality of query execution plans, predicted costs for executing the database command in response to the first request according to the given query execution plan;

based on the predicted costs for each query execution plan in the plurality of query execution plans, the database server selecting a particular query execution plan from the plurality of query execution plans that have been designated as acceptable for executing the database command; and

in response to the first request to execute the database command, the database server executing the database command according to the particular query execution plan;

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

2. The method of claim 1, further comprising, for each given query execution plan in the plurality of query execution plans, the database server designating the given query execution plan as acceptable for use by the database server to execute the database command in response to determining that the query execution plan is optimal for executing the database command in at least one execution context.

3. The method of claim 1, further comprising:

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

estimating, for each given query execution plan in the plurality of query execution plans, second predicted costs for executing the database command in response to the second request according to the given query execution plan;

based on the second predicted costs for each query execution plan in the plurality of query execution plans, selecting a second particular query execution plan from the plurality of query execution plans that have been designated as acceptable for executing the database command, wherein the second particular query execution plan is different than the particular query execution plan; and

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

4. 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 database command;

determining that no query execution plan in the plurality of query execution plans that have been designated as acceptable for executing the database command will satisfy the second request in the current execution context;

determining a new query execution plan for executing the database command that is not in the plurality of query execution plans that have been designated as acceptable for executing the database command; and

in response to the second request to execute the database command, executing the database command in the current execution context according to the new query execution plan.

5. A method comprising:

identifying a plurality of query execution plans that have been designated as acceptable for use by a database server to execute a database command, the database command being a query statement that instructs the database server to perform one or more operations on data stored in a database;

persistently maintaining, at the database server, for the database command, baseline data specifying the plurality of query execution plans that have been designated as acceptable for executing the database command;

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

identifying, by the database server, a first query execution plan as being optimal for executing the database command in response to the first request;

when the first query execution plan is in the plurality of query execution plans, the database server executing the database command according to the first query execution plan; and

when the first query execution plan is not in the plurality of query execution plans;

in response to the first request to execute the database command, the database server executing the database command according to a different query execution plan that is in the plurality of acceptable query execution plans;

the database server adding the first query execution plan to a set of query execution plans that have been designated as potentially acceptable for use by the database server to execute the database command;

subsequent to;

(a) executing the database command and (b) adding the first query execution plan to the set of query execution plans that have been designated as potentially acceptable,

(c) the database server identifying the first query execution plan as having been designated as acceptable for use by the database server to execute the database command, and

(d) the database server updating the baseline data to specify that the first query execution plan is one of the plurality of query execution plans that have been designated as acceptable for executing the database command;

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

6. The method of claim 5 wherein:

the set of query execution plans that have been designated as potentially acceptable for use by the database server to execute the database command is stored in a query execution plan history for the database command; and

wherein the set of potential acceptable query execution plans is a subset of query execution plans in the query execution plan history that are not specified by the baseline data.

7. The method of claim 5 wherein identifying the first query execution plan as having been designated as acceptable for use by the database server to execute the database command comprises:

in a scheduled job, executing the first query execution plan;

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

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

8. The method of claim 5 wherein identifying the first query execution plan as having been designated as acceptable for use by the database server to execute the database command occurs in response to input from a user designating the first query execution plan as acceptable for use by the database server to execute the database command.

9. A method comprising:

identifying a plurality of query execution plans that have been designated as acceptable for use by a database server to execute a database command, the database command being a query statement that instructs the database server to perform one or more operations on data stored in a database;

persistently maintaining, at the database server, for the database command, baseline data specifying the plurality of query execution plans that have been designated as acceptable for executing the database command;

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

based on input received from a user during a query optimization process for the current execution context, the database server storing, in association with the baseline data, profile data for the database command, wherein the profile data includes one or more preferences for selecting a query execution plan to execute the database command;

in response to the input from the user, the database server determining a particular query execution plan for executing the database command in the current execution context, said determining being based on the profile data; and

the database server adding, to the baseline data, data specifying that the particular query execution plan has been designated as acceptable for use by the database server for executing the database command;

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

10. The method of claim 9, further comprising storing an outline of the particular query execution plan, from which the database server reconstructs the particular query execution plan.

11. One or more non-transitory computer-readable storage media storing one or more sequences of instructions which, when executed by one or more processors, cause:

identifying a plurality of query execution plans that have been designated as acceptable for use by a database server to execute a database command, the database command being a query statement that instructs the database server to perform one or more operations on data stored in a database;

persistently maintaining, at the database server, for the database command, baseline data specifying the plurality of query execution plans that have been designated as acceptable for executing the database command, wherein the baseline data includes a plurality of query execution plan outlines, wherein the plurality of query execution plan outlines is a set of data from which the database server reconstructs each query execution plan in the plurality of query execution plans;

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

estimating, by the database server, for each given query execution plan in the plurality of query execution plans, predicted costs for executing the database command in response to the first request according to the given query execution plan;

based on the predicted costs for each query execution plan in the plurality of query execution plans, the database server selecting a particular query execution plan from the plurality of query execution plans that have been designated as acceptable for executing the database command; and

in response to the first request to execute the database command, the database server executing the database command according to the particular query execution plan.

12. The computer-readable non-transitory medium of claim 11, wherein said one or more sequences of instructions include instructions, that when executed by said one or more processors, cause for each given query execution plan in the plurality of query execution plans, the database server designating the given query execution plan as acceptable for use by the database server to execute the database command in response to determining that the query execution plan is optimal for executing the database command in at least one execution context.

13. The computer-readable non-transitory medium of claim 11, wherein said one or more sequences of instructions include instructions, that when executed by said one or more processors, cause:

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

estimating, for each given query execution plan in the plurality of query execution plans, second predicted costs for executing the database command in response to the second request according to the given query execution plan;

based on the second predicted costs for each query execution plan in the plurality of query execution plans, selecting a second particular query execution plan from the plurality of query execution plans that have been designated as acceptable for executing the database command, wherein the second particular query execution plan is different than the particular query execution plan; and

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

14. The computer-readable non-transitory medium of claim 11, wherein said one or more sequences of instructions include instructions, that when executed by said one or more processors in a current execution context, cause:

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

determining that no query execution plan in the plurality of query execution plans that have been designated as acceptable for executing the database command will satisfy the second request in the current execution context;

determining a new query execution plan for executing the database command that is not in the plurality of query execution plans that have been designated as acceptable for executing the database command; and

in response to the second request to execute the database command, executing the database command in the current execution context according to the new query execution plan.

15. One or more non-transitory computer-readable storage media storing one or more sequences of instructions which, when executed by one or more processors, causes:

identifying a plurality of query execution plans that have been designated as acceptable for use by a database server to execute a database command, the database command being a query statement that instructs the database server to perform one or more operations on data stored in a database;

persistently maintaining, at the database server, for the database command, baseline data specifying the plurality of query execution plans that have been designated as acceptable for executing the database command;

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

identifying, by the database server, a first query execution plan as being optimal for executing the database command in response to the first request;

if the first query execution plan is in the plurality of query execution plans, the database server executing the database command according to the first query execution plan; and

if the first query execution plan is not in the plurality of query execution plans;

in response to the first request to execute the database command, the database server executing the database command according to a different query execution plan that is in the plurality of acceptable query execution plans;

the database server adding the first query execution plan to a set of query execution plans that have been designated as potentially acceptable for use by the database server to execute the database command;

subsequent to;

(a) executing the database command and (b) adding the first query execution plan to the set of query execution plans that have been designated as potentially acceptable,

(c) the database server identifying the first query execution plan as having been designated as acceptable for use by the database server to execute the database command, and

(d) the database server updating the baseline data to specify that the first query execution plan is one of the plurality of query execution plans that have been designated as acceptable for executing the database command.

16. The computer-readable non-transitory medium of claim 15, wherein said one or more sequences of instructions include instructions, that when executed by said one or more processors, cause:

the set of query execution plans that have been designated as potentially acceptable for use by the database server to execute the database command is stored in a query execution plan history for the database command; and

wherein the set of potential acceptable query execution plans is a subset of query execution plans in the query execution plan history that are not specified by the baseline data.

17. The computer-readable non-transitory medium of claim 15,

wherein identifying the first query execution plan as having been designated as acceptable for use by the database server to execute the database command comprises:

in a scheduled job, executing the first query execution plan;

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

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

18. The computer-readable non-transitory medium of claim 15, wherein identifying the first query execution plan as having been designated as acceptable for use by the database server to execute the database command occurs in response to input from a user designating the first query execution plan as acceptable for use by the database server to execute the database command.

19. One or more non-transitory computer-readable storage media storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the steps of:

identifying a plurality of query execution plans that have been designated as acceptable for use by a database server to execute a database command, the database command being a query statement that instructs the database server to perform one or more operations on data stored in a database;

persistently maintaining, at the database server, for the database command, baseline data specifying the plurality of query execution plans that have been designated as acceptable for executing the database command;

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

based on input received from a user during a query optimization process for the current execution context, the database server storing, in association with the baseline data, profile data for the database command, wherein the profile data includes one or more preferences for selecting a query execution plan to execute the database command;

in response to the input from the user, the database server determining a particular query execution plan for executing the database command in the current execution context, said determining being based on the profile data; and

the database server adding, to the baseline data, data specifying that the particular query execution plan has been designated as acceptable for use by the database server for executing the database command.

20. The computer-readable non-transitory medium of claim 19, wherein said one or more sequences of instructions include instructions, that when executed by said one or more processors, cause:

storing an outline of the particular query execution plan, from which the database server reconstructs the particular query execution plan.