Executing alternative plans for a SQL statement

• US 7,877,373 B2
• Filed: 07/10/2006
• Issued: 01/25/2011
• Est. Priority Date: 06/30/2006
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Zait, Mohamed
• Primary Examiner(s): Pham; Khanh B
• Assistant Examiner(s): CHEEMA, AZAM M
• Application Number:
  US11/484,108
• Publication Number:
  US 20080010240A1
• Time in Patent Office: 1,660 Days
• Field of Search: None
• US Class Current: 707/713
• CPC Class Codes: G06F 16/24542 Plan optimisation

Abstract

Under automated alternate plan analysis, a query optimizer generates candidate execution plans. The candidate execution plans are selected as alternate execution plans for the query and execution. Output describing characteristics of each alternate execution plan and/or its execution is generated and/or compared. From this information, it may be determined, for example, whether results returned by any of the alternate execution plans are the same and whether the least cost execution plan is actually the most efficiently executed.

Claims

1. A method of generating output for analyzing how well a query optimizer generates alternate execution plans and selects an optimal execution plan, the method comprising:

receiving a query;

in response to receiving the query, automatically performing;

generating at least two alternate execution plans for the received query, wherein each alternate execution plan of the at least two alternate execution plans defines operations for executing said received query differently from each other alternate execution plan of the at least two alternate execution plans;

for each execution plan of the at least two alternate execution plans, causing execution of said received query by a database server that performs the operations defined by the execution plan;

wherein the step of causing execution of said received query for each execution plan of the at least two alternate execution plans causes at least two executions of the received query; and

generating and storing output that describes characteristics of each of the at least two executions of said received query that is executed according to the operations defined by the execution plan of the at least two alternate execution plans;

wherein the output is useable for analyzing how well the query optimizer generates alternate execution plans and selects the optimal execution plan;

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

2. The method of claim 1, wherein:

said step of generating at least two alternate execution plans is based on a set of parameters;

said set of parameters specify one or more versions of said query optimizer upon which an alternate execution plan of said at least two execution plans should be based; and

wherein generating said at least two alternate execution plans includes generating for each version of said one or more versions an alternate execution plan based on optimization behavior associated with said each version.

3. The method of claim 1, wherein the step of generating at least two alternate execution plans includes:

generating a search space of a plurality of candidate execution plans; and

selecting from the search space said at least two alternate execution plans.

4. The method of claim 3, wherein the step of selecting is based on an estimated query cost computed from a set of parameters.

5. The method of claim 3, further comprising:

ranking the plurality of candidate execution plans based on at least one of the following metrics;

estimated query cost,

estimated CPU usage,

estimated memory usage, and

estimated disk input and output.

6. The method of claim 5, wherein said step of selecting said at least two alternate execution plans comprises selecting a set of top ranked candidate execution plans based on said step of ranking the plurality of candidate execution plans.

7. The method of claim 3, wherein said step of selecting said at least two alternate execution plans comprises randomly selecting at least one of said plurality of candidate execution plans.

8. The method of claim 1, wherein for the at least two alternate execution plans, said output indicates at least one of the following characteristics:

a performance realized for each alternate execution plan of said at least two alternate execution plans,

an indication of whether a first result returned for one of said at least two alternate execution plans differs from a second result for another of said at least two alternate execution plans, or

an indication that an execution error was encountered during execution of said at least two alternate execution plans.

9. The method of claim 1, wherein said step of generating at least two alternate execution plans is based on a set of parameters that specify how to form execution plans.

10. The method of claim 9, wherein:

the set of parameters specify certain one or more execution plan operations to exclude; and

wherein the step of generating at least two alternate execution plans includes foregoing generation of execution plans that include said certain one or more execution plan operations.

11. The method of claim 9, wherein:

the set of parameters specify certain one or more execution plan operations to include; and

wherein the step of generating at least two alternate execution plans includes limiting generation of alternate execution plans to execution plans that include said certain one or more execution plan operations.

12. The method of claim 9, wherein:

the set of parameters specify at least one query transformation not to perform; and

the steps further include foregoing performing one or more query transformations of said each query that do not include said at least one query transformation.

13. The method of claim 9, wherein:

the set of parameters specify to perform at least one query transformation; and

the steps further include performing one or more query transformations in response to detecting that said set of parameters specify to perform said at least one query transformation.

14. The method of claim 1, further comprising, based at least in part on the output, analyzing whether the optimal execution plan selected by the optimizer was more efficiently executed than other alternate execution plans not selected by the optimizer.

15. A non-transitory computer-readable storage medium that stores instructions which, when executed by one or more processors, cause the one of more processors to generate output for analyzing how well a query optimizer generates alternate execution plans and selects an optimal execution plan by causing the one or more processors to perform:

receiving a query;

in response to receiving the query, automatically performing;

generating at least two alternate execution plans for the received query, wherein each alternate execution plan of the at least two alternate execution plans defines operations for executing said received query differently from each other alternate execution plan of the at least two alternate execution plans;

for each execution plan of the at least two alternate execution plans, causing execution of said received query by a database server that performs the operations defined by the execution plan;

wherein the step of causing execution of said received query for each execution plan of the at least two alternate execution plans causes at least two executions of the received query; and

generating and storing output that describes characteristics of each of the at least two executions of said received query that is executed according to the operations defined by the execution plan of the at least two alternate execution plans;

wherein the output is useable for analyzing how well the query optimizer generates alternate execution plans and selects the optimal execution plan.

16. The computer-readable storage medium of claim 15, wherein the instructions, when executed by the one or more processors, cause the one of more processors to perform said step of generating at least two alternate execution plans based on a set of parameters;

wherein said set of parameters specify one or more versions of said query optimizer upon which an alternate execution plan of said at least two execution plans should be based; and

wherein the instructions, when executed by the one or more processors, cause the one of more processors to perform said step of generating said at least two alternate execution plans by generating for each version of said one or more versions an alternate execution plan based on optimization behavior associated with said each version.

17. The computer-readable storage medium of claim 15, wherein the instructions, when executed by the one or more processors, cause the one of more processors to perform the step of generating at least two alternate execution plans by:

generating a search space of a plurality of candidate execution plans; and

selecting from the search space said at least two alternate execution plans.

18. The computer-readable storage medium of claim 17, wherein the instructions, when executed by the one or more processors, cause the one of more processors to perform the step of selecting based on an estimated query cost computed from a set of parameters.

19. The computer-readable storage medium of claim 17, wherein the instructions, when executed by the one or more processors, further cause the one or more processors to perform:

ranking the plurality of candidate execution plans based on at least one of the following metrics;

estimated query cost,

estimated CPU usage,

estimated memory usage, and

estimated disk input and output.

20. The computer-readable storage medium of claim 19, wherein the instructions, when executed by the one or more processors, cause the one of more processors to further perform said step of selecting said at least two alternate execution plans by selecting a set of top ranked candidate execution plans based on said step of ranking the plurality of candidate execution plans.

21. The computer-readable storage medium of claim 17, wherein the instructions, when executed by the one or more processors, cause the one of more processors to further perform said step of selecting said at least two alternate execution plans by randomly selecting at least one of said candidate execution plans.

22. The computer-readable storage medium of claim 15, wherein for the at least two alternate execution plans, said output indicates at least one of the following characteristics:

a performance realized for each alternate execution plan of said at least two alternate execution plans,

an indication of whether a first result returned for one of said at least two alternate execution plans differs from a second result for another of said at least two alternate execution plans, or

an indication that an execution error was encountered during execution of said at least two alternate execution plans.

23. The computer-readable storage medium of claim 15, wherein the instructions, when executed by the one or more processors, cause the one of more processors to perform said step of generating at least two alternate execution plans based on a set of parameters that specify how to form execution plans.

24. The computer-readable storage medium of claim 23, wherein:

the set of parameters specify certain one or more execution plan operations to exclude; and

wherein the instructions, when executed by the one or more processors, cause the one of more processors to perform the step of generating at least two alternate execution plans by foregoing generation of execution plans that include said certain one or more execution plan operations.

25. The computer-readable storage medium of claim 23, wherein:

the set of parameters specify certain one or more execution plan operations to include; and

wherein the instructions, when executed by the one or more processors, cause the one of more processors to perform the step of generating at least two alternate execution plans by limiting generation of alternate execution plans to execution plans that include said certain one or more execution plan operations.

26. The computer-readable storage medium of claim 15, wherein:

the set of parameters specify at least one query transformation not to perform; and

the instructions, when executed by the one or more processors, cause the one of more processors to further perform foregoing performance of one or more query transformations of said each query that do not include said at least one query transformation.

27. The computer-readable storage medium of claim 15, wherein:

the set of parameters specify to perform at least one query transformation; and

the instructions, when executed by the one or more processors, cause the one of more processors to further perform one or more query transformations in response to detecting that said set of parameters specify to perform said at least one query transformation.

28. The computer-readable storage medium of claim 15, wherein the instructions, when executed by the one or more processors, cause the one of more processors to further perform analyzing whether the optimal execution plan selected by the optimizer was more efficiently executed than other alternate execution plans not selected by the optimizer.