Patterned based query optimization
View Patent ↗Methods, articles of manufacture, and systems for improving the efficiency of executing queries are provided. Rather than automatically running an optimizer to determine an access plan for each issued query, an access plan may be selected from a series of access plans used for prior executions of the same or a similar query. The series of access plans used for prior queries may define a pattern that may be used to accurately predict the behavior (e.g., execution time) of a future query. For some embodiments, a query group may be established to identify a set of similar queries, for which the optimizer is likely to determine the same access plans. In response to determining an issued query fits within an established query group, an access plan associated with the query group may be selected for executing the query, thus avoiding the processing overhead of wastefully running the optimizer only to determine a previously used access plan.
1. A computer-implemented method of optimizing execution of a query, comprising:
receiving the query;
selecting, from a set of one or more pre-existing access plans used for prior executions of one of the query and a similar query, an access plan for use in executing the query; and
executing the query according to the selected access plan.
2. The method of claim 1 , wherein the selecting comprises comparing one or more execution times, each corresponding to a prior execution of the query or a similar query using one of the set of access plans.
3. The method of claim 2 , wherein the selecting comprises selecting an access plan having the shortest corresponding execution time.
4. The method of claim 2 , wherein the selecting comprises selecting an access plan having a corresponding execution time less than or equal to a predetermined execution time.
5. The method of claim 2 , wherein the set of one or more access plans and corresponding execution times are contained in a record and the method further comprises:
monitoring an execution time of the query; and
updating the record with the selected access plan and the monitored execution time.
6. A computer-implemented method of optimizing execution of a query, comprising:
receiving the query;
determining if the query fits within an established group of queries; and
in response to determining the query fits within an established group of queries,
selecting, from a group of ore-existing access plans used for prior executions of queries within the established group, an access plan for use in executing the query, and
executing the query according to the selected access plan.
7. The method of claim 6 , wherein determining if the query fits within an established group of queries comprises searching one or more records, each associated with a different established group of queries.
8. The method of claim 7 , further comprising:
running an optimizer to generate an access plan for the query;
executing the query according to the generated access plan;
monitoring an execution time of the query; and
storing the generated access plan and monitored execution time in a record associated with the newly established group of queries.
9. The method of claim 6 , wherein queries within the established group share one or more common query criteria.
10. The method of claim 9 , wherein queries within the established group share one or more common SQL WHERE clauses.
11. The method of claim 6 , wherein queries within the established group target one or more common tables.
12. The method of claim 6 , further comprising establishing one or more groups of queries based an information in a statement cache.
13. The method of claim 6 , further comprising establishing one or more groups of queries based on static query statements embedded in an application.
14. The method of claim 6 , further comprising, in response to determining the query does not fit within an established group of queries, establishing a new group of queries based on the query.
15. A tangible computer readable medium containing a program which, when executed, performs operations for optimizing execution of a query comprising:
receiving the query;
selecting, from a set of one or more pre-existing access plans used for prior executions of one of the query and a similar query, an access plan for use in executing the query; and
executing the query according to the selected access plan.
16. The computer readable medium of claim 15 , wherein the selecting comprises comparing one or more execution times, each corresponding to a prior execution of the query or a similar query using one of the set of access plans.
17. The computer readable medium of claim 16 , wherein the set of one or more access plans and corresponding execution times are contained in a record and the operations further comprises:
monitoring an execution time of the query; and
updating the record with the selected access plan and the monitored execution time.
18. The computer readable medium of claim 16 , wherein the operations further comprise providing a user with a graphical user interface for selecting one or more optimization modes.
19. The computer readable medium of claim 18 , wherein at least one of the optimization modes comprises a safe optimization mode and, if the user has selected the safe optimization mode, the selecting comprises selecting an access plan with a corresponding execution time less than or equal to a predetermined execution time.
20. A database system, comprising:
a processor, a memory and a storage device;
a query optimizer residing in the memory and executable by the processor;
one or more records on the storage device, each containing a set of pre-existing access plans used in prior executions of one of the query and a similar query; and
a query execution component configured to receive a query and execute the query against a database according to a pre-existing access plan selected from one of the records or an access plan generated by the query optimizer, wherein the query execution component executes the access plan generated by the optimizer only after determining that the pre-existing access plans contained in the one or more of the records fail to satisfy predetermined criteria.
21. The database system of claim 20 , wherein the query execution component is configured to locate a record corresponding to the query and determine whether to execute the query against the database according to an access plan selected from one of the records or an access plan generated by the query optimizer based on information within the record.
22. The database system of claim 21 , wherein the query execution component is configured to execute the query against the database according to an access plan generated by the optimizer in response to determining, from the located record, the number of prior executions is below a threshold value.
23. The database system of claim 21 , wherein the query execution component is configured to execute the query against the database according to an access plan generated by the optimizer in response to determining none of the access plans in the located record have a corresponding execution time below a predetermined threshold value.