Executing alternative plans for a SQL statement

• US 20080010240A1
• Filed: 07/10/2006
• Published: 01/10/2008
• 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
• Granted Patent: US 7,877,373 B2
• Time in Patent Office: Days
• US Class Current: 707/2
• 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 comprising,

for each query in a plurality of queries:

a query optimizer generating a plurality of alternate execution plans based on said each query and a set of parameters;

causing execution by a database server of each alternate execution plan of said plurality of alternate execution plans; and

generating output that describes characteristics of said execution.

2. The method of claim 1, wherein:

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

wherein generating a plurality of 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 a plurality of alternate execution plans includes:

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

selecting from the search space said plurality of alternate execution plans.

4. The method of claim 3, wherein the step of selecting is based on criteria set forth by said set of parameters.

5. The method of claim 3, wherein:

a ranking ranks 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 1, wherein for the plurality of alternate execution plans of a query from said plurality of queries, said output indicates at least one of the following characteristics:

the performance realized for each alternate execution plan of said one or more execution plans,

an estimated query cost for each alternate execution plan of said plurality of execution plans,

an indication of whether the result returned for an alternate execution plan differs from another alternate execution plan, and

an indication that an execution error was encountered during execution of said each alternate execution plan.

7. The method of claim 1, wherein the set of parameters specify how to form execution plans.

8. The method of claim 7, wherein:

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

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

9. The method of claim 7, wherein:

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

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

10. The method of claim 1, 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.

11. The method of claim 1, 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.

12. A computer-readable medium carrying 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 medium carrying 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 medium carrying 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 medium carrying 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 medium carrying 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.

17. A computer-readable medium carrying 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 medium carrying 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 medium carrying 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 medium carrying 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.

21. A computer-readable medium carrying 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 medium carrying 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.