Performing actions based on monitoring execution of a query

• US 7,958,159 B1
• Filed: 12/19/2005
• Issued: 06/07/2011
• Est. Priority Date: 12/19/2005
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Tran, Hoa Thu, Kim, Choung H.
• Primary Examiner(s): Vo; Tim T.
• Assistant Examiner(s): MENG, JAU SHYA
• Application Number:
  US11/311,747
• Time in Patent Office: 1,996 Days
• Field of Search: None
• US Class Current: 707/802
• CPC Class Codes: G06F 16/24564 Applying rules; Deductive queries

Abstract

In a database system, a database request is received, and database software dispatches steps associated with the database request to plural access modules of a database system for execution. Statistics are collected regarding execution of each step by a respective access module in response to receiving an indication of completion by the respective access module. It is determined whether statistics relating to execution of the database request violate one or more predefined rules, and in response to determining that the statistics relating to execution of the database request violate one or more predefined thresholds, one or more actions are performed by the database software.

Claims

1. A method for use in a database system having plural access modules, comprising:

receiving a database request;

dispatching, by database software executed on one or more processors, steps associated with the database request to one or plural access modules of the database system for execution;

collecting statistics, by a synchronous monitor executed on the one or more processors, regarding execution of each step by a respective access module, wherein the synchronous monitor is triggered to collect the statistics in response to receiving an indication of completion of each step by the respective access module, wherein the synchronous monitor does not collect the statistics regarding execution of each step until the synchronous monitor is triggered in response to receiving the indication of completion of each corresponding step, wherein the statistics collected by the synchronous monitor include plural statistics selected from among CPU time consumed to perform each corresponding step, a count of input/output cycles involved in each corresponding step, and a number of rows of a table generated in execution of each corresponding step;

collecting statistics, by an asynchronous monitor executed on the one or more processors, regarding execution of the steps by the one or plural access modules prior to completion of the execution of the steps by the one or plural access modules;

determining whether statistics collected by the synchronous monitor or asynchronous monitor relating to execution of the database request violate one or more predefined rules; and

in response to determining that the statistics relating to execution of the database request violate one or more predefined thresholds, performing one or more actions by the database software.

2. The method of claim 1, wherein collecting the statistics collected by the asynchronous monitor comprises collecting at least some of:

CPU time consumed by the access modules;

a count of I/O cycles;

number of rows accessed;

spool usage;

number of access modules used;

CPU usage skew between the access modules;

I/O count skew between the access modules; and

an amount of time that a database request has been blocked by another database request.

3. The method of claim 1, wherein violation of the one or more predefined rules indicates an exception condition in the database system, and wherein performing the one or more actions comprises performing at least one of logging the exception condition and continuing with execution of the database request;

changing a priority of the database request;

stopping or aborting the database request;

executing a preselected software program to perform a task; and

sending an alert.

4. A method for use in a database system having plural access modules, comprising:

receiving a database request;

dispatching, by database software executed on one or more processors, steps associated with the database request to one or plural access modules of the database system for execution;

collecting statistics, by a synchronous monitor executed on the one or more processors, regarding execution of each step by a respective access module, wherein the synchronous monitor is triggered to collect the statistics in response to receiving an indication of completion of each step by the respective access module;

collecting statistics, by an asynchronous monitor executed on the one or more processors, regarding execution of the steps by the one or plural access modules prior to completion of the execution of the steps by the one or plural access modules;

determining whether statistics collected by the synchronous monitor or asynchronous monitor relating to execution of the database request violate one or more predefined rules;

in response to determining that the statistics relating to execution of the database request violate one or more predefined thresholds, performing one or more actions by the database software;

storing workload definitions, each workload definition associated with a corresponding set of the one or more predefined rules; and

classifying the received database request to assign the received database request to a selected one of the workload definitions,

wherein determining whether statistics collected by the synchronous monitor or asynchronous monitor relating to execution of the database request violate the one or more predefined rules comprises determining whether statistics collected by the synchronous monitor or asynchronous monitor relating to execution of the database request violate the one or more predefined rules of the selected workload definition.

5. The method of claim 4, wherein classifying the received database request comprises comparing attributes of the database request with content of the workload definitions.

6. The method of claim 5, wherein comparing the attributes of the database request with content of the workload definitions comprises comparing the attributes including some of a user identifier, an application identifier, an estimated response time, an estimated answer set size, a type of query, and a date and time of the query.

7. The method of claim 4, wherein each workload definition is associated with a corresponding set of exception processing rules, and wherein performing the one or more actions by the database software is based on the exception processing rules of the selected workload definition.

8. An article comprising at least one computer-readable storage medium containing instructions that when executed cause a database system to:

receive a database request;

execute steps associated with the database request;

invoke a first monitor and a second monitor to collect statistics associated with execution of the database request,

wherein the first monitor collects statistics in response to an indication of completion of each step;

wherein the second monitor collects statistics during execution of a step without having to wait for the indication of completion;

store plural workload definitions each associated with a corresponding set of the one or more predefined rules;

classify the received database request to a selected one of the workload definitions; and

determine whether the collected statistics violate the one or more predefined rules of the selected workload definition.

9. The article of claim 8, wherein the instructions when executed cause the database system to further perform one or more actions in response to determining that the collected statistics violate the one or more predefined rules.

10. The article of claim 8, wherein the database system comprises a parallel database system having a plurality of access modules, and wherein invoking the first monitor and second monitor causes collection of statistics associated with execution of database request by the plurality of access modules.

11. The article of claim 8, wherein the first monitor does not collect statistics regarding execution of each step of the database request until the first monitor is triggered in response to the indication of completion of each corresponding step of the database request, wherein the statistics collected by the first monitor include plural statistics selected from among CPU time consumed to perform each corresponding step, a count of input/output cycles involved in each corresponding step, and a number of rows of a table generated in execution of each corresponding step.

12. A database system comprising:

one or more processors;

a parser executable on the one or more processors to receive a database request;

a dispatcher executable on the one or more processors to dispatch steps of the database request for execution;

a first monitor executable on the one or more processors to synchronously collect statistics associated with execution of the steps in response to indications of completion of corresponding steps, wherein the first monitor does not collect statistics regarding execution of each step of the database request until the first monitor is triggered in response to the indication of completion of each corresponding step of the database request, wherein the statistics collected by the first monitor include plural statistics selected from among CPU time consumed to perform each corresponding step, a count of input/output cycles involved in each corresponding step, and a number of rows of a table generated in execution of each corresponding step;

a second monitor executable on the one or more processors to asynchronously collect statistics associated with execution of the steps during execution of the steps without waiting for indications of completion of corresponding steps; and

a handler executable on the one or more processors to perform one or more actions in response to an exception indication from the first monitor or second monitor indicating that one or more predefined rules have been violated.

13. The database system of claim 12, wherein the first monitor is part of the dispatcher, the first monitor to receive an indication of completion of each step and to collect statistics associated with execution of the step in response to the indication of completion of the step.

14. The database system of claim 12, wherein the second monitor includes a timer to cause the second monitor to periodically collect statistics associated with execution of each step.

15. The database system of claim 12, wherein the first monitor is configured to check for a first group of exception conditions based on collected statistics by the first monitor, and wherein the second monitor is configured to check for a second, different group of exception conditions based on collected statistics by the second monitor.

16. The database system of claim 12, further comprising plural access modules and plural storage modules to store data distributed across the storage modules, the first monitor and second monitor to collect statistics associated with execution of the steps by the plural access modules.

17. The database system of claim 12, further comprising:

a first statistics collector invoked by the first monitor to collect statistics; and

a second statistics collector invoked by the second monitor to collect statistics.

18. A database system comprising:

one or more processors;

a parser executable on the one or more processors to receive a database request;

a dispatcher executable on the one or more processors to dispatch steps of the database request for execution;

a first monitor executable on the one or more processors to synchronously collect statistics associated with execution of the steps in response to indications of completion of corresponding steps;

a second monitor executable on the one or more processors to asynchronously collect statistics associated with execution of the steps during execution of the steps without waiting for indications of completion of corresponding steps;

a handler executable on the one or more processors to perform one or more actions in response to an exception indication from the first monitor or second monitor indicating that one or more predefined rules have been violated;

a storage to store workload definitions, each workload definition associated with a respective set of the one or more predefined rules; and

a module to classify the received database request and assign the received database request to a selected one of the workload definitions,

wherein the first or second monitor is configured to determine whether the collected statistics violate the one or more of the predefined rules of the selected workload definition.

19. The database system of claim 18, wherein the dispatcher is configured to select one of the first and second monitors to collect statistics for the received database request based on the selected workload definition.