Parallel, in-line, query capture database for real-time logging, monitoring and opitmizer feedback

• US 20090327242A1
• Filed: 08/22/2008
• Published: 12/31/2009
• Est. Priority Date: 06/30/2008
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Burger, Louis M., Richards, Anita, Brown, Douglas P., Julien, Thomas P.
• Granted Patent: US 8,775,413 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/24549 Run-time optimisation

Abstract

A computer-implemented method, apparatus and article of manufacture for optimizing a database query. A query execution plan is generated for the database query, which may be stored in an in-line query capture database. Execution of the query execution plan may be monitored in order to generate performance information, which also may be stored in the in-line query capture database. The data stored in in-line query capture database may be used during the initial generation of query execution plans, as well as for the re-optimization of a query execution plan, during the execution of the query execution plan.

Claims

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

generating a query execution plan for the database query;

storing the query execution plan in an in-line query capture database;

executing the query execution plan in order to retrieve data from a database stored on the computer system;

monitoring the execution of the query execution plan in order to generate performance information; and

storing the performance information in the in-line query capture database.

2. The method of claim 1, further comprising optimizing the query execution plan, during the execution of the query execution plan, using the in-line query capture database.

3. The method of claim 2, wherein the generating step comprises generating an optimal query execution plan and the optimizing step comprises re-optimizing the query execution plan during the execution of the optimal query execution plan.

4. The method of claim 3, wherein the query execution plan is comprised of a plurality of steps and the optimizing step further comprises:

executing at least some of the steps of the query execution plan;

monitoring the execution of the steps of the query execution plan in order to generate the performance information;

storing the performance information in the in-line query capture database; and

re-optimizing at least some remaining unexecuted steps of the query execution plan using the performance information stored in the in-line query capture database.

5. The method of claim 4, wherein the monitoring step further comprises examining results from the execution of the steps of the query execution plan.

6. The method of claim 5, wherein the re-optimizing step further comprises re-optimizing at least some remaining unexecuted steps of the query execution plan based on the examined results.

7. The method of claim 1, wherein the performance information comprises actual cost information related to the execution of the plan, such as the actual cost information of processing identified operators and predicates using identified access paths to access relations referenced in the query.

8. The method of claim 1, further comprising storing the query execution plan and the performance information in a database query log.

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

means for generating a query execution plan for the database query;

means for storing the query execution plan in an in-line query capture database;

means for executing the query execution plan in order to retrieve data from a database stored on the computer system;

means for monitoring the execution of the query execution plan in order to generate performance information; and

means for storing the performance information in the in-line query capture database.

10. The apparatus of claim 9, further comprising means for optimizing the query execution plan, during the execution of the query execution plan, using the in-line query capture database.

11. The apparatus of claim 10, wherein the means for generating comprises means for generating an optimal query execution plan and the means for optimizing comprises means for re-optimizing the query execution plan during the execution of the optimal query execution plan.

12. The apparatus of claim 11, wherein the query execution plan is comprised of a plurality of steps and the means for optimizing further comprises:

means for executing at least some of the steps of the query execution plan;

means for monitoring the execution of the steps of the query execution plan in order to generate the performance information;

means for storing the performance information in the in-line query capture database; and

means for re-optimizing at least some remaining unexecuted steps of the query execution plan using the performance information stored in the in-line query capture database.

13. The apparatus of claim 12, wherein the means for monitoring further comprises means for examining results from the execution of the steps of the query execution plan.

14. The apparatus of claim 13, wherein the means for re-optimizing further comprises means for re-optimizing at least some remaining unexecuted steps of the query execution plan based on the examined results.

15. The apparatus of claim 9, wherein the performance information comprises actual cost information related to the execution of the plan, such as the actual cost information of processing identified operators and predicates using identified access paths to access relations referenced in the query.

16. The apparatus of claim 9, further comprising storing the query execution plan and the performance information in a database query log.

17. 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:

generating a query execution plan for the database query;

storing the query execution plan in an in-line query capture database;

executing the query execution plan in order to retrieve data from a database stored on the computer system;

monitoring the execution of the query execution plan in order to generate performance information; and

storing the performance information in the in-line query capture database.

18. The article of claim 17, further comprising optimizing the query execution plan, during the execution of the query execution plan, using the in-line query capture database.

19. The article of claim 18, wherein the generating step comprises generating an optimal query execution plan and the optimizing step comprises re-optimizing the query execution plan during the execution of the optimal query execution plan.

20. The article of claim 19, wherein the query execution plan is comprised of a plurality of steps and the optimizing step further comprises:

executing at least some of the steps of the query execution plan;

monitoring the execution of the steps of the query execution plan in order to generate the performance information;

storing the performance information in the in-line query capture database; and

re-optimizing at least some remaining unexecuted steps of the query execution plan using the performance information stored in the in-line query capture database.

21. The article of claim 20, wherein the monitoring step further comprises examining results from the execution of the steps of the query execution plan.

22. The article of claim 21, wherein the re-optimizing step further comprises re-optimizing at least some remaining unexecuted steps of the query execution plan based on the examined results.

23. The article of claim 17, wherein the performance information comprises actual cost information related to the execution of the plan, such as the actual cost information of processing identified operators and predicates using identified access paths to access relations referenced in the query.

24. The article of claim 17, further comprising storing the query execution plan and the performance information in a database query log.