Actively managing resource bottlenecks in a database system

Patent metadata

Field	Value
Publication number	US-9213741-B2
Application number	US-201313852278-A
Country	US
Kind code	B2
Filing date	Mar 28, 2013
Priority date	Nov 18, 2008
Publication date	Dec 15, 2015
Grant date	Dec 15, 2015

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.

First claim

We claim:

1. A computer-implemented method for optimizing a database query of a database in a computer system, comprising:
using a cost model to generate at least one database query execution plan of the database;
monitoring, in the computer system, current resource usage of one or more resources of the computer system required to execute the at least one database query execution plan, as a measure of current level of usage of the one or more resources;
storing, in the computer system, a condition code representative of the monitored current resource usage based on the measure of the current level of the usage of the one or more resources;
modifying, in the computer system, the cost model using the stored condition code determined based on the measure of the current level of the usage of the one or more resources required to the at least one query execution plan, by at least modifying at least one cost associated with at least one of the one or more resources in order to obtain a modified cost model;
generating, in the computer system, a query execution plan for at least one database query using the modified cost model as modified based on the measure of the current usage of the one or more resources required to the at least one query execution plan; and
executing, in the computer system, the query execution plan.

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 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 the generating 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 associated with a lowest cost is selected for execution.

11. An apparatus that includes one or more processors configured to:
monitor resource usage of one or more computing resources of at least one computing system during execution of one or more database queries of a database by the at least one computing system;
obtain, based on the monitoring of the resource usage, monitored resource usage data as a measure of current level of usage of the one or more resources required to execute at least one database query execution plan of the database;
determine, based on the monitored resource usage data, at least one cost model for generating query execution plans for execution by the at least one computing system, wherein the cost model uses the monitored resource usage data determined based on the measure of the current level of the usage of the one or more resources required to execute the at least one database query execution plan;
subsequently, monitor the usage of the more computing resources to generate a modified monitored resource usage data; and
determine based on the modified resource usage data, determined based on the measure of the current level of the usage of the one or more resources required to execute the at least one database query execution plan, at least one modified cost model for generating query execution plans for execution by the at least one computing system.

12. The apparatus of claim 11 , 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.

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

14. The apparatus of claim 11 , wherein the generating performs adaptive selection of the query execution plan to alleviate the resource bottleneck condition in the computer system.

15. A non-transitory computer readable storage medium storing at least executable computer code that when executed by a computer causes the computer to:
monitor resource usage of one or more computing resources of at least one computing system during execution of one or more database queries of a database by the at least one computing system;
obtain, based on the monitoring of the resource usage, monitored resource usage data as a measure of current level of usage of the one or more resources required to execute at least one database query execution plan of the database;
determine, based on the monitored resource usage data, at least one cost model for generating query execution plans for execution by the at least one computing system, wherein the cost model uses the monitored resource usage data determined based on the measure of the current level of the usage of the one or more resources required to execute the at least one database query execution plan;
subsequently, monitor the usage of the more computing resources to generate a modified monitored resource usage data; and
determine based on the modified resource usage data, determined based on the measure of the current level of the usage of the one or more resources required to execute the at least one database query execution plan, at least one modified cost model for generating query execution plans for execution by the at least one computing system.

16. The non-transitory computer readable storage medium of claim 15 , wherein the condition code is associated with a specified workload.

17. The non-transitory computer readable storage medium of claim 15 , wherein the condition code is stored into a queue.

18. The non-transitory computer readable storage medium of claim 17 , wherein the condition code has an expiration time indicating when it is to be deleted from the queue.

19. The non-transitory computer readable storage medium of claim 17 , wherein the condition code is timestamped prior to being stored into the queue and the condition code most recently timestamped is acted upon.

20. The non-transitory computer readable storage medium of claim 15 , 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.

Assignees

• Teradata Us Inc

Inventors

• Burger Louis
• Brown Douglas P
• Julien Thomas Patrick

Classifications

• G06F17/30442

  Physics · mapped topic

• G06F17/30463 Primary

  Physics · mapped topic

• G06F17/30433

  Physics · mapped topic

• G06F16/2453

  Query optimisation · CPC title

• G06F16/24524 Primary

  Access plan code generation and invalidation; Reuse of access plans · CPC title