Systems and methods for holding a query

• US 8,930,344 B2
• Filed: 02/04/2011
• Issued: 01/06/2015
• Est. Priority Date: 02/04/2011
• Status: Active Grant

• Current Assignee: Hewlett Packard Enterprise Development LP (Hewlett-Packard Enterprise Company)
• Original Assignee: Hewlett-Packard Development Company, L.P. (HP Inc.)
• Inventors: Kakarlamudi, Subbarao, Mahasintunan, Anan, Omanski, Zbigniew, Brown, Patrick M., Tharaknath, Capirala, Narain, Arvind
• Primary Examiner(s): KUDDUS, DANIEL A
• Application Number:
  US13/021,051
• Publication Number:
  US 20120203762A1
• Time in Patent Office: 1,432 Days
• Field of Search: 707/718, 707/719, 707/999.005
• US Class Current: 707/713
• CPC Class Codes: G06F 16/24542 Plan optimisation

Abstract

A database management system that can comprise a memory for storing computer executable instructions and a processing unit for accessing the memory and executing the computer executable instructions. The computer executable instruction scan comprise a compiler to hold a query received at a query input, thereby preventing the query from executing based on a cost comparison between an estimated resource execution cost of a query plan for the query and cost rules.

Claims

1. A database management system (DBMS) comprising:

a memory for storing computer executable instructions; and

a processing unit for accessing the memory and executing the computer executable instructions, the computer executable instructions comprising;

a query optimizer to;

generate a query plan for a query received at a query input; and

determine an estimated resource execution cost of the query plan, the estimated resource execution cost comprises a plurality of estimated cost fields;

a compiler to hold the query the holding prevents the query from executing based on a determination that the estimated resource execution cost of the query plan exceeds at least one of a plurality of cost rules, wherein the estimated resource execution cost is based on at least one of a number of join operations and a number of cross product operations in the query plan for executing the query;

wherein the cost rules comprise;

a fixed cost threshold; and

a variable cost threshold; and

the estimated cost comparison is based on a comparison between the plurality of estimated cost fields and the fixed cost threshold and the variable cost threshold of the cost rules; and

wherein the query held at the compiler is prevented from executing for a given time period, the time period including one of an off-peak period, a low priority period, and an indefinite period.

2. The DBMS of claim 1, wherein the compiler further comprises:

wherein the compiler is further to;

hold the query based on a determination that a first estimated cost field of the estimated resource execution cost exceeds the fixed cost threshold or based on a determination that a second estimated cost field of the estimated resource execution cost exceeds the variable cost threshold by a predetermined amount; and

write the query plan to a query repository of the DBMS based on a determination that the query has been held by the compiler.

3. The DBMS of claim 2, wherein the computer executable instructions further comprise a query executor to:

execute the query plan on a database stored in data storage based on a determination that the query is not held by the compiler; and

cancel and hold execution of the query based on a cost comparison between an actual resource execution cost for the query plan and the cost rules.

4. The DBMS of claim 1, wherein the compiler is further to provide a notification to a requestor of the query that the query has been held.

5. The DBMS of claim 1, wherein the computer executable instructions further comprise a cost control to notify a user that the query has been held, wherein the cost control comprises a query plan editor to edit the query plan to provide a modified query plan to the compiler.

6. The DBMS of claim 5, the compiler is further to perform another estimated cost comparison between an estimated resource execution cost of the modified query plan and the cost rules.

7. The DBMS of claim 6, wherein the compiler is further to prevent the modified query plan from executing based on results of the another estimated cost comparison.

8. The DBMS of claim 7, wherein the compiler is further to provide a notification to a requestor of the query that the query has been prevented from being completed.

9. The DBMS of claim 1, wherein a query requestor submits the query received at the query input such that the query requestor requests that the query be executed.

10. The DBMS of claim 1, wherein the query optimizer is to determine the estimated resource execution cost of the query plan prior to execution of the query.

11. The DBMS of claim 1, wherein the number of join operations includes at least one of a sort-merge join, a hash join, and a nested loop join.

12. The DBMS of claim 1, wherein the estimated resource execution cost of the query plan is further based on at least one of an index scan and a sequential scan in the query plan for the query.

13. A method for managing a database comprising:

generating a query plan for a received query;

estimating a resource execution cost of the query plan; and

determining whether to hold the received query based on the estimated resource execution cost of the query plan, where holding the received query prevents execution of the query for a given time period, the time period including one of an off-peak period, a low priority period, and an indefinite period;

notifying a requestor of the query that the query has been held;

editing the query plan to provide a modified query plan;

determining whether to prevent the query from executing based on an estimated resource execution cost of the modified query plan for the query.

14. The method of claim 13, further comprising:

holding the received query.

15. The method of claim 14, further comprising:

estimating a resource execution cost of the modified query plan; and

determining whether to execute the modified query plan based on the estimated resource execution cost of the modified query plan.

16. A database management system (DMBS) comprising:

a query repository comprising;

cost rules that characterize a resource execution cost limit for a query plan;

a hold queue to store an entry comprising a query plan; and

a modified queue to store an entry comprising a modified query plan;

a query input to receive a query from a query requestor, wherein the query requestor requests the query to be executed;

a compiler comprising a query optimizer to generate a query plan for the query received at the query input, the compiler is to;

determine whether to hold the query received at the query input, wherein the holding prevents the query received at the query input from executing based on a cost comparison between an estimated resource execution cost of the query plan and the cost rules of the query repository, wherein the query held at the compiler is prevented from executing for a given time period, the time period including one of an off-peak period, a low priority period, and an indefinite period; and

write an entry into the hold queue of the query repository based on a determination that the query received at the query input has been held;

a query executor comprising a monitor to monitor an actual execution resource cost of the query plan, the query executor to;

execute the query plan on a database stored in data storage based on a determination that the query plan has not been held;

determine whether to cancel and hold the query received at the query input, wherein the holding prevents the query from executing, based on a cost comparison between the actual resource execution cost of the query plan and the cost rules of the query repository; and

write an entry into the hold queue of the query repository based on a determination that the query has been held;

a cost control to;

notify a user of the cost control that the entry has been written into the hold queue of the query repository;

retrieve the entry from the hold queue of the query repository to provide a retrieved entry;

provide a query plan editor for modifying the query plan of the retrieved entry to provide a modified query plan; and

write an entry into the modified queue of the query repository that includes the modified query plan.

17. The DBMS of claim 16, wherein the compiler is further to:

perform another estimated cost comparison between an estimated resource execution cost of the modified query plan of the entry in the modified queue and the cost rules of the query repository; and

reject a query corresponding to the modified query plan of the entry in the modified queue, wherein the holding prevents the modified query plan of the entry in the modified queue from executing based on results of the another estimated cost comparison.

18. The DBMS claim 17, wherein the compiler is further to notify a query requestor of the query if the query is held or rejected.

19. A non-transitory computer readable medium having computer executable instructions comprising:

a compiler to determine whether to hold a query on a database based on an estimated resource execution cost of a query plan for the query, such that wherein holding the query prevents the query from being executed for a given time period, the time period including one of an off-peak period, a low priority period, and an indefinite period; and

a cost control to notify a requestor of the query that the query has been held, wherein the cost control comprises a query plan editor to edit the query plan to provide a modified query plan to the compiler;

wherein the compiler is further to determine whether to prevent the query from executing based on an estimated resource execution cost of the modified query plan for the query.

20. The non-transitory computer readable medium of claim 19, further comprising a query executor to determine whether to cancel and hold execution of the query based on an actual resource execution cost of the query plan for the query and cost rules.

21. The non-transitory computer readable medium of claim 19, further comprising a query executor to determine whether to cancel execution of the query based on an actual resource execution cost of the modified query plan for the query.