Techniques for scoring and comparing query execution plans

• US 7,941,425 B2
• Filed: 07/25/2007
• Issued: 05/10/2011
• Est. Priority Date: 07/25/2007
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Sahu, Birendra Kumar, Kvm, Deva Raju
• Primary Examiner(s): Trujillo; James
• Assistant Examiner(s): SOMERS, MARC S
• Application Number:
  US11/782,824
• Publication Number:
  US 20090030888A1
• Time in Patent Office: 1,385 Days
• Field of Search: 707/705, 707/713, 707/714, 707/718, 707/719
• US Class Current: 707/718
• CPC Class Codes: G06F 16/24542 Plan optimisation

Abstract

Techniques for scoring and comparing query execution plans are provided. Predefined parameter types are identified in query execution plans and predefined weighted values are assigned to any identified parameters within the query execution plans. The weights are summed on a per processing step bases and the sum of the processing steps represents a total score for a particular query execution plan. The total scores or individual step scores from different query execution plans can then be compared or evaluated against one another for optimization and problem detection analysis.

Claims

1. A machine-implemented method, comprising:

acquiring predefined parameter types for comparing multiple query execution plans against one another, each query execution plan including a number of parameters associated with the parameter types, each parameter identified and acquired for comparison from each query execution plan and each query execution plan developed by a different database optimizer;

assigning predefined weights to each of the parameters that are identified within each of the query execution plans, the weights assigned based on the parameter type for each parameter;

computing a total score for each of the query execution plans in response to the weights associated with each query execution plan to select a particular query execution plan for a particular database optimizer.

2. The method of claim 1 further comprising:

populating a data structure with query identifiers for each query execution plan and score differences associated with differences between the total scores for each query execution plan, wherein each entry in the data structure includes a single query identifier along with its corresponding score difference; and

sorting the entries within the data structure in response to the score differences.

3. The method of claim 2 further comprising, reporting a configurable number of the query identifiers and their score differences to an administrator.

4. The method of claim 1, wherein acquiring further includes accessing a database to acquire the parameter types.

5. The method of claim 4, wherein assigning further includes acquiring values that represent each of the weights.

6. The method of claim 1, wherein computing further includes summing the weights associated with each query execution plan together to produce the total score for that particular query execution plan.

7. A machine-implemented method, comprising:

acquiring a first query execution plan and a second query execution plan, the first query execution plan developed from a first database optimizer and the second query execution plan developed by a second database optimizer, the first and second database optimizers are different versions of a same database optimizer;

iterating each step of the first query execution plan and the second query execution plan to acquire independent scores for each step of each plan, the scores weighted based on parameter type for parameters associated with each query execution plan; and

adding each score for each step of each plan together to obtain a first total score for the first query execution plan and a second total score for the second query execution plan.

8. The method of claim 7, wherein iterating further includes:

determining when a particular step is associated with two or more parallel steps;

computing sub scores for each of the two or more parallel steps; and

taking a maximum of the sub scores as the independent scores for the particular step.

9. The method of claim 7 further comprising, reporting the first total score and the second total score for subsequent evaluation.

10. The method of claim 7 further comprising, presenting each of the independent scores associated with each step for each plan side by side for step comparison of the first query execution plan and its steps vis-à

-vis the second query execution plan and its steps along with the first and second total scores.

11. The method of claim 7, wherein acquiring further includes recognizing the first query execution plan and the second query execution plan as being associated with a same query job that each of the first and second database optimizers produce different query execution plans for.

12. A machine-implemented system comprising:

a query execution step scoring service implemented in a machine-accessible medium and to process on a machine; and

a query plan scoring service implemented in a machine-accessible medium and to process on the machine, the query plan scoring service is to decompose query execution plans into independent processing steps and pass each step to the query execution step scoring service, each query execution plan developed by a different database optimizer, and the query execution step scoring service is to supply a step score for each step provided by the query plan scoring service, each score a sum of weights based on particular parameter types assigned in each of the execution plans, and the query plan scoring service is to add the step scores for each query execution plan to acquire a total score for each query execution plan to select a particular query execution plan and a particular database optimizer.

13. The system of claim 12, wherein query plan scoring service is to determine when a particular step of a particular query execution plan includes parallel steps and is to call the query execution step scoring service for each parallel step and add each step score received together to obtain an overall step score for that particular step that has the parallel steps.

14. The system of claim 12 further comprising, a sorting service implemented in a machine-accessible medium and to process on the machine, wherein the sorting service is to sort each query execution plan and its total score in a data structure in response to values associated with the total scores.

15. The system of claim 12, wherein the sorting service is to automatically report a top configurable number of query execution plans and their total scores from the sorted data structure to an administrator for subsequent evaluation.

16. The system of claim 15, wherein the top configurable number is supplied as a processing parameter from the administrator to the sorting service.