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

• US 8,775,413 B2
• Filed: 08/22/2008
• Issued: 07/08/2014
• Est. Priority Date: 06/30/2008
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Brown, Douglas P., Richards, Anita, Julien, Thomas P., Burger, Louis M.
• Primary Examiner(s): PEACH, POLINA G
• Application Number:
  US12/229,383
• Publication Number:
  US 20090327242A1
• Time in Patent Office: 2,146 Days
• Field of Search: None
• US Class Current: 707/718
• 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 one or more query execution plans for the database query in an optimizer, wherein each of the query execution plans is comprised of a plurality of steps;

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

selecting an optimal query execution plan in the optimizer from among the query execution plans, based on the computed total cost;

storing the selected query execution plan in an in-line query capture database, wherein the in-line query capture database is used for parallel real-time logging, monitoring and feedback, and is used to maintain information about query execution plans in real-time, by using a dictionary to verify the query execution plans and authorization to perform the query execution plan, by using an index analysis to optimize access path selection, and by storing optimized data in a workload cache;

scheduling the selected query execution plan for execution, based on service level goals, performance parameters for a priority scheduler, and an assignment of resources, wherein;

the selected query execution plan is assigned to one or more workloads and to one or more priority classes based on service level goals, such that the workloads with similar performance requirements are assigned to the same priority classes,

the priority scheduler selects queries from the workloads and priority classes for execution,

the performance parameters are used by the priority scheduler to adjust weights that modify how the resources are assigned or how the workloads are executed,

one or more real-time feedback mechanisms monitor system conditions, detect short-term and long-term deviations from the service level goals, and adjust the priority scheduler'"'"'s weights to modify how the resources are assigned or how the workloads are executed, and

at least one of the real-time feedback mechanisms monitors the workgroups'"'"' performance against the workloads'"'"' rules to determine whether to delay incoming requests, depending on the workloads and the priority classes to which the incoming requests are assigned;

executing at least some of the steps of the selected query execution plan, at run-time for the selected query execution plan, in order to retrieve data from a database stored on the computer system;

monitoring the execution of the steps of the selected query execution plan, in order to generate the actual cost information;

storing the actual cost information with the selected query execution plan in the in-line query capture database; and

re-optimizing at least some remaining unexecuted steps of the selected query execution plan in the optimizer, in real-time during the run-time of the selected query execution plan,

using the actual cost information stored with the selected query execution plan in the in-line query capture database, and then continuing with execution of the remaining unexecuted steps of the selected query execution plan.

2. The method of claim 1, wherein the monitoring step further comprises examining results from the execution of the steps of the selected query execution plan.

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

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

5. The method of claim 1, further comprising storing the selected query execution plan and the actual cost information in a database query log.

6. A computer-implemented apparatus for optimizing a database query, comprising:

a computer system, executed by a processor, configured for;

generating one or more query execution plans for the database query in an optimizer, wherein each of the query execution plans is comprised of a plurality of steps;

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

selecting an optimal query execution plan in the optimizer from among the query execution plans, based on the computed total cost;

storing the selected query execution plan in an in-line query capture database, wherein the in-line query capture database is used for parallel real-time logging, monitoring and feedback, and is used to maintain information about query execution plans in real-time, by using a dictionary to verify the query execution plans and authorization to perform the query execution plan, by using an index analysis to optimize access path selection, and by storing optimized data in a workload cache;

scheduling the selected query execution plan for execution, based on service level goals, performance parameters for a priority scheduler, and an assignment of resources, wherein;

the selected query execution plan is assigned to one or more workloads and to one or more priority classes based on service level goals, such that the workloads with similar performance requirements are assigned to the same priority classes,

the priority scheduler selects queries from the workloads and priority classes for execution,

the performance parameters are used by the priority scheduler to adjust weights that modify how the resources are assigned or how the workloads are executed,

one or more feedback mechanisms monitor system conditions, detect short-term and long-term deviations from the service level goals, and adjust the priority scheduler'"'"'s weights to modify how the resources are assigned or how the workloads are executed, and

at least one of the feedback mechanisms monitors the workgroups'"'"' performance against the workloads'"'"' rules to determine whether to delay incoming requests, depending on the workloads and the priority classes to which the incoming requests are assigned;

executing at least some of the steps of the selected query execution plan, at run-time for the selected query execution plan, in order to retrieve data from a database stored on the computer system;

monitoring the execution of the steps of the selected query execution plan, in order to generate the actual cost information;

storing the actual cost information with the selected query execution plan in the in-line query capture database; and

re-optimizing at least some remaining unexecuted steps of the selected query execution plan in the optimizer, in real-time during the run-time of the selected query execution plan, using the actual cost information stored with the selected query execution plan in the in-line query capture database, and then continuing with execution of the remaining unexecuted steps of the selected query execution plan.

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

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

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

10. The apparatus of claim 6, further comprising storing the selected query execution plan and the actual cost information in a database query log.

11. An article of manufacture comprising a non-transitory computer-readable storage medium with an executable program stored thereon, wherein the program instructs a computer system to perform the following steps:

generating one or more query execution plans for the database query in an optimizer, wherein each of the query execution plans is comprised of a plurality of steps;

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

selecting an optimal query execution plan in the optimizer from among the query execution plans, based on the computed total cost;

storing the selected query execution plan in an in-line query capture database, wherein the in-line query capture database is used for parallel real-time logging, monitoring and feedback, and is used to maintain information about query execution plans in real-time, by using a dictionary to verify the query execution plans and authorization to perform the query execution plan, by using an index analysis to optimize access path selection, and by storing optimized data in a workload cache;

scheduling the selected query execution plan for execution, based on service level goals, performance parameters for a priority scheduler, and an assignment of resources, wherein;

the selected query execution plan is assigned to one or more workloads and to one or more priority classes based on service level goals, such that the workloads with similar performance requirements are assigned to the same priority classes,

the priority scheduler selects queries from the workloads and priority classes for execution,

the performance parameters are used by the priority scheduler to adjust weights that modify how the resources are assigned or how the workloads are executed,

one or more feedback mechanisms monitor system conditions, detect short-term and long-term deviations from the service level goals, and adjust the priority scheduler'"'"'s weights to modify how the resources are assigned or how the workloads are executed, and

at least one of the feedback mechanisms monitors the workgroups'"'"' performance against the workloads'"'"' rules to determine whether to delay incoming requests, depending on the workloads and the priority classes to which the incoming requests are assigned;

executing at least some of the steps of the selected query execution plan, at run-time for the selected query execution plan, in order to retrieve data from a database stored on the computer system;

monitoring the execution of the steps of the selected query execution plan, in order to generate the actual cost information;

storing the actual cost information with the selected query execution plan in the in-line query capture database; and

re-optimizing at least some remaining unexecuted steps of the selected query execution plan in the optimizer, in real-time during the run-time of the selected query execution plan, using the actual cost information stored with the selected query execution plan in the in-line query capture database, and then continuing with execution of the remaining unexecuted steps of the selected query execution plan.

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

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

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

15. The article of claim 11, further comprising storing the selected query execution plan and the actual cost information in a database query log.