SYSTEMS AND METHODS FOR HOLDING A QUERY

• US 20120203762A1
• Filed: 02/04/2011
• Published: 08/09/2012
• 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: Mahasintunan, Anan, Omanski, Zbigniew, Brown, Patrick M., Narain, Arvind, KAKARLAMUDI, SUBBARAO, Tharaknath, Capirala
• Granted Patent: US 8,930,344 B2
• Time in Patent Office: Days
• US Class Current: 707/718
• CPC Class Codes: G06F 16/24542 Plan optimisation

Abstract

One example discloses 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 instructions can 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 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.

2. The DBMS of claim 1, wherein:

the estimated resource execution cost comprises a plurality of estimated cost fields;

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.

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

a query optimizer to generate the query plan for the query;

wherein the compiler is further to;

hold the query if a first estimated cost field of the estimated resource execution cost exceeds the fixed cost threshold or if 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 if the query has been held.

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

execute the query plan on a database stored in data storage if 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.

5. 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.

6. 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.

7. The DBMS of claim 6, 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.

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

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

10. 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.

11. The method of claim 10, further comprising:

holding the received query; and

modifying the query plan to form a modified query plan.

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

13. 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;

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, thereby preventing 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; and

write an entry into the hold queue of the query repository if 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 if the query plan has not been held;

determine whether to cancel and hold the query received at the query input, thereby preventing 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 if 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.

14. The DBMS of claim 13, 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, thereby preventing the modified query plan of the entry in the modified queue from executing based on results of the another estimated cost comparison.

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

16. A 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 holding the query prevents the query from being executed.

17. The computer readable medium of claim 16, 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.

18. The computer readable medium of claim 16, further comprising 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.

19. The computer readable medium of claim 18, 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 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.