Dynamically Selecting Alternative Query Access Plans

• US 20070282794A1
• Filed: 08/16/2007
• Published: 12/06/2007
• Est. Priority Date: 06/24/2004
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Majd, Mahdad, Santosuosso, John, Ruhlow, Randy, Barsness, Eric
• Granted Patent: US 7,925,648 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24532 of parallel queries
• CPC Class Codes: G06F 16/24549 Run-time optimisation
• CPC Class Codes: G06F 16/2471 Distributed queries

Abstract

A query access plan for executing a database query is dynamically selected from among multiple alternative query access plans based on the availability of computer resources allocated for running the query. Preferably, a first query access plan is generated based on resources then available, and if it is possible to provide additional resources, one or more alternative plans are generated based on the additional resources. If an alternative plan is significantly better than the original plan based on a comparison of query related parameters, the alternative is chosen for executing the query. Additional resources for running the query may be provided from logically partitioned and/or grid environments.

Claims

1. A method of providing fee-based processing in a processor system, whereby fees are based on additional computer resources being added for enabling provision of alternative query access plans, the processor system including at least one processor;

a memory coupled to the at least one processor, a query optimizer residing in the memory; and

, a manager residing in the memory and executable by the at least one processor, said method comprising the steps of;

generating with said query optimizer a first query access plan for executing a query, said first query access plan being generated with said query optimizer based on a first set of computer resources to be used for executing said query;

evaluating said first query access plan by determining whether additional computer resources are available to execute said query, said additional computer resources being computer resources in addition to said first set of computer resources;

dynamically selecting an alternative query access plan for executing said query responsive to determining that said additional computer resources are available to execute said first query and responsive to determining that at least a parameter value of the alternative query access plan exceeds a corresponding parameter value of the first query access plan, whereby the alternative query access plan is improved over the first query access plan, said alternative query access plan being based on an alternative set of computer resources which includes said additional computer resources to be used for executing the query; and

dynamically adding the computer resources for running the dynamically selected alternative query access plan, wherein the manager dynamically adds the computer resources based on determining the cost for usage for the additional computer resources.

2. A method for determining a query access plan for executing a query, comprising the computer-implemented steps of:

automatically generating with a query optimizer a first query access plan for executing said query, said first query access plan being generated with said query optimizer based on a first set of computer resources to be used for executing said query;

subsequently determining that a second set of computer resources is available to execute said query, said second set including at least some computer resources not included in said first set;

responsive to said step of subsequently determining that a second set of computer resources is available to execute said query, automatically generating with said query optimizer a second query access plan for executing said query, said second query access plan being different from said first query access plan, said second query access plan being generated with said query optimizer based on said second set of computer resources to be used for executing said query.

3. The method of claim 2, further comprising the steps of:

comparing at least one execution parameter of said first query access plan with a corresponding at least one execution parameter of said second query access plan;

if said at least one execution parameter of said second query access plan is significantly better than said at least one execution parameter of said first query access plan, then selecting said second query access plan for executing said query using said second set of resources; and

if said at least one execution parameter of said second query access plan is not significantly better than said at least one execution parameter of said first query access plan, then selecting said first query access plan for executing said query using said first set of resources.

4. The method of claim 3, further comprising if said at least one execution parameter of said second query access plan is significantly better than said at least one execution parameter of said first query access plan, then dynamically adding computer resources for executing said second query access plan.

5. The method of claim 4, wherein the additional computer resources are provided by one or more additional partitions in a logically partitioned environment.

6. The method of claim 4, wherein the additional computer resources are provided by a networked computer grid environment.

7. The method of claim 4, wherein the additional computer resources are added based on determining the cost for usage.

8. The method of claim 4, further comprising gathering statistics of computing resources including additional computer resources if available for determining the second query access plan.

9. The method of claim 3, wherein said step of comparing at least one execution parameter of said first query access plan with a corresponding at least one execution parameter of said second query access plan comprises comparing a relative cost for usage for said first and second sets of computer resources.