Actively managing resource bottlenecks in a database system

• US 20100125565A1
• Filed: 11/18/2008
• Published: 05/20/2010
• Est. Priority Date: 11/18/2008
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Burger, Louis M., Julien, Thomas, Brown, Douglas P.
• Granted Patent: US 8,423,534 B2
• Time in Patent Office: Days
• US Class Current: 707/713
• CPC Class Codes: G06F 16/24524 Access plan code generation and invalidation; Reuse of access plans
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/24542 Plan optimisation

Abstract

A computer-implemented method, apparatus and article of manufacture for optimizing a database query. Resource usage of one or more resources of the computer system are monitored. A condition code representative of the monitored resource usage is stored. A cost model is modified using the stored condition code. A query execution plan is generated for the database query using the modified cost model. The query execution plan is then executed in order to retrieve data from a database stored on the computer system, wherein the retrieved data is presented to a user for subsequent use and analysis.

Claims

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

monitoring, in the computer system, resource usage of one or more resources of the computer system;

storing, in the computer system, a condition code representative of the monitored resource usage;

modifying, in the computer system, a cost model using the stored condition code;

generating, in the computer system, a query execution plan for the database query using the modified cost model; and

executing, in the computer system, the query execution plan in order to retrieve data from a database stored on the computer system, wherein the retrieved data is presented to a user for subsequent use and analysis.

2. The method of claim 1, wherein the condition code is associated with a specified workload.

3. The method of claim 1, wherein the condition code is stored into a queue.

4. The method of claim 3, wherein the condition code has an expiration time indicating when it is to be deleted from the queue.

5. The method of claim 3, wherein the condition code is timestamped prior to being stored into the queue and the condition code most recently timestamped is acted upon.

6. The method of claim 1, wherein the cost model is modified to discourage use of one or more of the resources of the computer system by dynamically changing configurable portions of the cost model.

7. The method of claim 1, wherein a resource bottleneck condition in the computer system results in costs associated with the resource being increased relative to costs of other resources.

8. The method of claim 7, wherein the generating step performs adaptive selection of the query execution plan to alleviate the resource bottleneck condition in the computer system.

9. The method of claim 7, wherein generating step further comprises optimizing the query execution plan to manage the resource bottleneck condition in the computer system.

10. The method of claim 1, wherein one or more candidate query execution plans are generated for the query, a cost for each of the candidate query execution plans is computed using the modified cost model, and a candidate query execution plan having a lowest cost is selected for execution.

11. The method of claim 10, wherein the candidate query execution plan having the lowest cost with an acceptable response time is selected for execution.

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

a computer system;

means, performed by the computer system, for monitoring resource usage of one or more resources of the computer system;

means, performed by the computer system, for storing a condition code representative of the monitored resource usage;

means, performed by the computer system, for modifying a cost model using the stored condition code;

means, performed by the computer system, for generating a query execution plan for the database query using the modified cost model; and

means, performed by the computer system, for executing the query execution plan in order to retrieve data from a database stored on the computer system, wherein the retrieved data is presented to a user for subsequent use and analysis.

13. The apparatus of claim 12, wherein the condition code is associated with a specified workload.

14. The apparatus of claim 12, wherein the condition code is stored into a queue.

15. The apparatus of claim 14, wherein the condition code has an expiration time indicating when it is to be deleted from the queue.

16. The apparatus of claim 14, wherein the condition code is timestamped prior to being stored into the queue and the condition code most recently timestamped is acted upon.

17. The apparatus of claim 12, wherein the cost model is modified to discourage use of one or more of the resources of the computer system by dynamically changing configurable portions of the cost model.

18. The apparatus of claim 12, wherein a resource bottleneck condition in the computer system results in costs associated with the resource being increased relative to costs of other resources.

19. The apparatus of claim 18, wherein the means for generating performs adaptive selection of the query execution plan to alleviate the resource bottleneck condition in the computer system.

20. The apparatus of claim 18, wherein the means for generating further comprises means for optimizing the query execution plan to manage the resource bottleneck condition in the computer system.

21. The apparatus of claim 12, wherein one or more candidate query execution plans are generated for the query, a cost for each of the candidate query execution plans is computed using the modified cost model, and a candidate query execution plan having a lowest cost is selected for execution.

22. The apparatus of claim 21, wherein the candidate query execution plan having the lowest cost with an acceptable response time is selected for execution.

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

monitoring, in the computer system, resource usage of one or more resources of the computer system;

storing, in the computer system, a condition code representative of the monitored resource usage;

modifying, in the computer system, a cost model using the stored condition code;

generating, in the computer system, a query execution plan for the database query using the modified cost model; and

executing, in the computer system, the query execution plan in order to retrieve data from a database stored on the computer system, wherein the retrieved data is presented to a user for subsequent use and analysis.

24. The article of claim 23, wherein the condition code is associated with a specified workload.

25. The article of claim 23, wherein the condition code is stored into a queue.

26. The article of claim 25, wherein the condition code has an expiration time indicating when it is to be deleted from the queue.

27. The article of claim 25, wherein the condition code is timestamped prior to being stored into the queue and the condition code most recently timestamped is acted upon.

28. The article of claim 23, wherein the cost model is modified to discourage use of one or more of the resources of the computer system by dynamically changing configurable portions of the cost model.

29. The article of claim 23, wherein a resource bottleneck condition in the computer system results in costs associated with the resource being increased relative to costs of other resources.

30. The article of claim 29, wherein the generating step performs adaptive selection of the query execution plan to alleviate the resource bottleneck condition in the computer system.

31. The article of claim 29, wherein generating step further comprises optimizing the query execution plan to manage the resource bottleneck condition in the computer system.

32. The article of claim 23, wherein one or more candidate query execution plans are generated for the query, a cost for each of the candidate query execution plans is computed using the modified cost model, and a candidate query execution plan having a lowest cost is selected for execution.

33. The article of claim 32, wherein the candidate query execution plan having the lowest cost with an acceptable response time is selected for execution.