Dynamic run-time optimization using automated system regulation for a parallel query optimizer

• US 20090327216A1
• Filed: 08/22/2008
• Published: 12/31/2009
• Est. Priority Date: 06/30/2008
• Status: Abandoned Application

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Brown, Douglas P., Julien, Thomas P., Richards, Anita, Burger, Louis M.
• Application Number:
  US12/229,397
• Publication Number:
  US 20090327216A1
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24549 Run-time optimisation

Abstract

A computer-implemented apparatus, method, and article of manufacture for optimizing a database query. System conditions and operating environment events that occur during operation of the computer system are monitored. An n-dimensional optimizer matrix is used to identify at least one rule resulting from the monitored system conditions and operating environment events. An optimal query execution plan is selected from among a plurality of query execution plans generated for the query in response to the identified rule.

Claims

1. A computer-implemented method for optimizing a database query in a computer system, comprising:

monitoring system conditions and operating environment events that occur during operation of the computer system;

using an n-dimensional optimizer matrix to identify at least one rule resulting from the monitored system conditions and operating environment events; and

selecting an optimal query execution plan from among a plurality of query execution plans generated for the query in response to the identified rule.

2. The method of claim 1, wherein the system condition is a system state.

3. The method of claim 1, wherein the operating environment event is an access method or a join method performed by the query execution plan.

4. The method of claim 1, wherein the rule is used by an optimizer in the selection of the optimal query execution plan.

5. The method of claim 1, wherein the rule comprises an enforcement priority rule.

6. The method of claim 1, wherein the rule provides actual cost information for performing each of the query execution plans.

7. The method of claim 6, further comprising computing a cost value for each of the query execution plans using the actual cost information, wherein the selecting step selects the optimal query execution plan based on the computed cost value.

8. The method of claim 6, wherein the actual cost information comprises a weight.

9. The method of claim 6, wherein the selecting step further comprises:

analyzing the query to identify its associated predicates;

analyzing the associated predicates to identify their access paths;

generating one or more query execution plans for each of the predicates and their associated access paths;

computing a total cost for each of the query execution plans using estimated cost information adjusted by actual cost information;

selecting the optimal query execution plan based on the computed total cost;

executing the optimal query execution plan; and

collecting the actual cost information as the optimal query execution plan is executed.

10. The method of claim 1, wherein the system conditions and operating events are logged.

11. The method of claim 1, wherein the optimal query execution plan is logged.

12. A computer-implemented apparatus for optimizing a database query in a computer system, comprising:

means for monitoring system conditions and operating environment events that occur during operation of the computer system;

means for using an n-dimensional optimizer matrix to identify at least one rule resulting from the monitored system conditions and operating environment events; and

means for selecting an optimal query execution plan from among a plurality of query execution plans generated for the query in response to the identified rule.

13. The apparatus of claim 12, wherein the system condition is a system state.

14. The apparatus of claim 12, wherein the operating environment event is an access method or a join method performed by the query execution plan.

15. The apparatus of claim 12, wherein the rule is used by an optimizer in the selection of the optimal query execution plan.

16. The apparatus of claim 12, wherein the rule comprises an enforcement priority rule.

17. The apparatus of claim 12, wherein the rule provides actual cost information for performing each of the query execution plans.

18. The apparatus of claim 17, further comprising means for computing a cost value for each of the query execution plans using the actual cost information, wherein the means for selecting selects the optimal query execution plan based on the computed cost value.

19. The apparatus of claim 17, wherein the actual cost information comprises a weight.

20. The apparatus of claim 17, wherein the means for selecting further comprises:

means for analyzing the query to identify its associated predicates;

means for analyzing the associated predicates to identify their access paths;

means for generating one or more query execution plans for each of the predicates and their associated access paths;

means for computing a total cost for each of the query execution plans using estimated cost information adjusted by actual cost information;

means for selecting the optimal query execution plan based on the computed total cost;

means for executing the optimal query execution plan; and

means for collecting the actual cost information as the optimal query execution plan is executed.

21. The apparatus of claim 12, wherein the system conditions and operating events are logged.

22. The apparatus of claim 12, wherein the optimal query execution plan is logged.

23. An article of manufacture comprising one or more storage devices tangibly embodying instructions that, when executed by one or more computer systems, result in the computer systems performing a method for optimizing a database query, the method comprising:

monitoring system conditions and operating environment events that occur during operation of the computer system;

using an n-dimensional optimizer matrix to identify at least one rule resulting from the monitored system conditions and operating environment events; and

selecting an optimal query execution plan from among a plurality of query execution plans generated for the query in response to the identified rule.

24. The article of claim 23, wherein the system condition is a system state.

25. The article of claim 23, wherein the operating environment event is an access method or a join method performed by the query execution plan.

26. The article of claim 23, wherein the rule is used by an optimizer in the selection of the optimal query execution plan.

27. The article of claim 23, wherein the rule comprises an enforcement priority rule.

28. The article of claim 23, wherein the rule provides actual cost information for performing each of the query execution plans.

29. The article of claim 28, further comprising computing a cost value for each of the query execution plans using the actual cost information, wherein the selecting step selects the optimal query execution plan based on the computed cost value.

30. The article of claim 28, wherein the actual cost information comprises a weight.

31. The article of claim 28, wherein the selecting step further comprises:

analyzing the query to identify its associated predicates;

analyzing the associated predicates to identify their access paths;

generating one or more query execution plans for each of the predicates and their associated access paths;

computing a total cost for each of the query execution plans using estimated cost information adjusted by actual cost information;

selecting the optimal query execution plan based on the computed total cost;

executing the optimal query execution plan; and

collecting the actual cost information as the optimal query execution plan is executed.

32. The article of claim 23, wherein the system conditions and operating events are logged.

33. The article of claim 23, wherein the optimal query execution plan is logged.