AUTONOMICALLY GENERATING A QUERY IMPLEMENTATION THAT MEETS A DEFINED PERFORMANCE SPECIFICATION

• US 20080215535A1
• Filed: 04/23/2008
• Published: 09/04/2008
• Est. Priority Date: 06/03/2004
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Day, Paul Reuben, Muras, Brian Robert, Ryg, Anne Marie
• Granted Patent: US 9,135,298 B2
• Time in Patent Office: Days
• US Class Current: 707/2
• CPC Class Codes: G06F 16/24524 Access plan code generation and invalidation; Reuse of access plans
• CPC Class Codes: G06F 16/24542 Plan optimisation

Abstract

A database query optimizer allows specifying a performance specification for a query. When an access plan is created, the actual performance for executing the query using the access plan is determined and compared to the performance specification for the query. If the actual performance does not satisfy the performance specification for the query, the query optimizer autonomically generates a different access plan to see if the performance improves. If an access plan that meets the performance specification is generated, the new access plan is stored in the access plan cache for the query. If no access plan can be generated that meets the performance specification for the query, the access plan that provided the best performance is selected and stored in the access plan cache for the query.

Claims

1. An apparatus comprising:

at least one processor;

a memory coupled to the at least one processor;

an access plan cache residing in the memory, the access plan cache including a plurality of entries, each entry including a performance specification for a corresponding query and an access plan for the corresponding query, the performance specification specifying a desired maximum execution time for the corresponding query; and

a query optimizer residing in the memory and executed by the at least one processor, wherein the query optimizer determines time required to execute a query using an access plan stored in the access plan cache and compares the time required to execute the query with the performance specification corresponding to the query, wherein the query optimizer generates a new access plan if the time required to execute the query using the access plan stored in the access plan cache exceeds the performance specification corresponding to the query, wherein, if the query optimizer generates a new access plan that satisfies the performance specification corresponding to the query, the query optimizer stores the new access plan in an entry in the access plan cache that corresponds to the query and updates the performance specification corresponding to the query, and wherein, if the query optimizer cannot generate a new access plan that satisfies the performance specification corresponding to the query, the query optimizer selects a new access plan that had the best performance, stores the new access plan in the entry in the access plan cache that corresponds to the query, and uses the best performance to generate a new stored performance specification corresponding to the query.

2. The apparatus of claim 1 wherein the performance specification comprises a heuristic expression.

3. An article of manufacture comprising:

(A) a query optimizer that determines time required to execute a query using an access plan stored in an access plan cache, the access plan cache including a plurality of entries, each entry including a performance specification for a corresponding query and an access plan for the corresponding query, the performance specification specifying a desired maximum execution time for the corresponding query, the query optimizer comparing the time required to execute the query with the performance specification corresponding to the query, wherein the query optimizer generates a new access plan if the time required to execute the query using the access plan stored in the access plan cache exceeds the performance specification corresponding to the query, wherein, if the query optimizer generates a new access plan that satisfies the performance specification corresponding to the query, the query optimizer stores the new access plan in an entry in the access plan cache that corresponds to the query and updates the performance specification corresponding to the query, and wherein, if the query optimizer cannot generate a new access plan that satisfies the performance specification corresponding to the query, the query optimizer selects a new access plan that had the best performance, stores the new access plan in the entry in the access plan cache that corresponds to the query, and uses the best performance to generate a new stored performance specification corresponding to the query; and

(B) recordable media bearing the query optimizer.

4. The program product of claim 3 wherein the performance specification comprises a heuristic expression.

5. An apparatus comprising:

at least one processor;

a memory coupled to the at least one processor;

an access plan cache residing in the memory, the access plan cache including a plurality of entries, each entry including a performance specification for a corresponding query and an access plan for the corresponding query, the performance specification specifying a desired maximum number of bus cycles for the corresponding query that comprises an average of bus cycles over the last N executions of the query, where N is a predetermined positive integer; and

a query optimizer residing in the memory and executed by the at least one processor, wherein the query optimizer determines number of bus cycles required to execute a query using an access plan stored in the access plan cache and compares the number of bus cycles required to execute the query with the performance specification corresponding to the query, wherein the query optimizer generates a new access plan if the number of bus cycles required to execute the query using the access plan stored in the access plan cache exceeds the performance specification corresponding to the query, wherein, if the query optimizer generates a new access plan that satisfies the performance specification corresponding to the query, the query optimizer stores the new access plan in an entry in the access plan cache that corresponds to the query and updates the performance specification corresponding to the query with an actual number of bus cycles during execution of the query, and wherein, if the query optimizer cannot generate a new access plan that satisfies the performance specification corresponding to the query, the query optimizer selects a new access plan that had the best number of bus cycles during execution of the query, stores the new access plan in the entry in the access plan cache that corresponds to the query, and stores in the access plan cache the best number of bus cycles as a new stored performance specification corresponding to the query.