TECHNIQUES FOR SCORING AND COMPARING QUERY EXECUTION PLANS

• US 20090030888A1
• Filed: 07/25/2007
• Published: 01/29/2009
• 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
• Granted Patent: US 7,941,425 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• 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;

assigning predefined weights to each of the parameters that are identified within each of the query execution plans;

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

2. The system 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 system of claim 2 further comprising, reporting a configurable number of the query identifiers and their score differences to an administrator.

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

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

6. The system of claim 4, identifying the parameter types as attributes of:

query join operations, update operations, sort operations, cache operations, confidence factors, and parallel processing operations.

7. The system 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.

8. A machine-implemented method, comprising:

acquiring a first query execution plan and a second query execution plan;

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; 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.

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

parsing each step of each plan for predefined parameter types; and

assigning a predefined weighted value to each identified parameter type.

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

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

12. The method of claim 8 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.

13. The method of claim 8, wherein acquiring further includes recognizing the first query execution plan as originating from a first database optimizer and the second query execution plan as originating from a second database optimizer.

14. The method of claim 13, 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 two database optimizers produce different query execution plans for.

15. 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, wherein 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, and wherein the query execution step scoring service is to supply a step score for each step provided by the query plan scoring service, and wherein 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.

16. The system of claim 15, wherein the query execution step scoring service is to parse each step for predefined parameter types and assign predefined weighted values for each identified parameter type when producing the step scores for a particular step of a particular query execution plan.

17. The system of claim 15, 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.

18. The system of claim 15 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.

19. The system of claim 15, 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.

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