QUERY PLAN ANALYSIS OF ALTERNATIVE PLANS USING ROBUSTNESS MAPPING

• US 20100198811A1
• Filed: 02/02/2009
• Published: 08/05/2010
• Est. Priority Date: 02/02/2009
• Status: Active Grant

• Current Assignee: Micro Focus LLC (Open Text Corporation)
• Original Assignee: Hewlett Packard Enterprise Development LP (Hewlett-Packard Enterprise Company)
• Inventors: Graefe, Goetz, Kuno, Harumi, Wiener, Janet L.
• Granted Patent: US 9,128,984 B2
• Time in Patent Office: Days
• US Class Current: 707/718
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/24549 Run-time optimisation

Abstract

Computer-implemented and associated operating methods evaluate robustness of a query plan by measuring performance with regard to a range of runtime conditions and producing a map of relative performance of the given query plan when compared with alternative plans for a range of conditions. The computer-implemented system comprises logic that evaluates the selected query plan in comparison to multiple alternative query plans in a predetermined range of runtime conditions that include data characteristics. The logic produces a set of performance measurements and analyzes the measured performance to map performance of the selected query plan in comparison to performance of one or more alternative query plans.

Claims

1. A computer-implemented system comprising:

logic that evaluates a selected query plan in comparison to a plurality of alternative query plans in a predetermined range of runtime conditions comprising data characteristics, the logic producing a set of performance measurements and analyzing the measured performance to map performance of the selected query plan in comparison to performance of at least one alternative query plan.

2. The system according to claim 1 further comprising:

the logic that identifies landmarks indicative of selected query plan performance that is degraded greater than a selected amount in comparison to an alternative query plan performance.

3. The system according to claim 1 further comprising:

the logic that generates a marked performance map wherein the marking is indicative of performance of the selected query plan in comparison to alternatives under predetermined runtime conditions.

4. The system according to claim 1 further comprising:

the logic that generates a marked performance map wherein the marking is indicative of regions wherein the selected query plan degrades a predetermined amount when compared to a best alternative.

5. The system according to claim 1 further comprising:

a query to be evaluated;

a selected query plan;

a set of runtime resource conditions under which the query is to be evaluated; and

the logic generating a plurality of alternative query plans for the query to be evaluated under the runtime resource conditions in a set of alternative query plans.

6. The system according to claim 5 further comprising:

the logic that executes the query to be evaluated using a plurality of alternative query plans with a plurality of resource conditions forming a query plan/resource condition coordinate set, the logic comparing performance of the selected query plan to performance of a best performing query plan of the plurality of query plans for each coordinate set and generating a performance map indicative of comparison of the selected query plan to a best performing of the alternative query plans.

7. The system according to claim 1 further comprising:

an interface for receiving a query, a target query plan, and a set of runtime resource conditions to be evaluated; and

the logic that generates and collects alternative query plans for the query comprising;

iterating through the runtime conditions and, for sets of runtime conditions, iterating through the target query plan and alternative query plans comprising running the query under the specified conditions while collecting the alternative query plans generated by a query optimizer; and

measuring and collecting performance results of the query runs.

8. The system according to claim 7 further comprising:

the logic for selected sets of conditions comparing performance of the target query plan to performance of a determined best alternative query plan for the selected conditions.

9. The system according to claim 1 further comprising:

an article of manufacture comprising;

a controller-usable medium having a computer readable program code embodied in a controller for evaluating a selected query plan in comparison to a plurality of alternative query plans, the computer readable program code further comprising;

code causing the controller to evaluate the selected query plan in comparison to the plurality of alternative query plans in a predetermined range of runtime conditions comprising data characteristics;

code causing the controller to produce a set of performance measurements and analyze the measured performance; and

code causing the controller to map performance of the selected query plan in comparison to performance of at least one alternative query plan.

10. A computer-implemented system comprising:

logic that evaluates database performance of a selected query plan and at least one alternative query plan, the logic creating a set of performance maps comparing performance of a selected database system operating according to the selected query plan and operating according to the at least one alternative query plan under actual conditions, the logic measuring and mapping performance of the selected query plan relative to the at least one alternative query plan in a predetermined range of runtime conditions comprising data characteristics.

11. The system according to claim 10 further comprising:

a target query plan to be evaluated;

a set of runtime resource conditions under which the query is to be evaluated;

a set of data characteristics; and

the logic executing a query using the target query plan and using the at least one alternative query plan, and measuring a plurality of performance characteristics for selected runtime resource conditions at a plurality of data characteristics, the logic creating a set comprising the query plan, conditions, and performance measurements.

12. The system according to claim 11 further comprising:

the logic identifying regions in at least one performance map for which performance using the target query plan degrades more than a selected amount in comparison to performance using the at least one alternative query plan, the logic collecting the identified regions, identifying coordinates in the arrangement of the identified regions as landmarks, and creating a performance map of query plan performance that includes landmark coordinates in terms of cardinality, resource conditions, and comparative performance of the target query plan.

13. The system according to claim 10 wherein:

an article of manufacture comprising;

a controller-usable medium having a computer readable program code embodied in a controller for evaluating database performance of a selected query plan and at least one alternative query plan, the computer readable program code further comprising;

code causing the controller to create a set of performance maps comparing performance of a selected database system operating according to the selected query plan and operating according to the at least one alternative query plan under actual conditions; and

code causing the controller to measure and map performance of the selected query plan relative to the at least one alternative query plan in a predetermined range of runtime conditions comprising data characteristics.

14. A computer-implemented method for analyzing database queries comprising:

evaluating a selected query plan in comparison to a plurality of alternative query plans in a predetermined range of runtime conditions comprising data characteristics;

producing a set of performance measurements; and

analyzing the measured performance to map performance of the selected query plan in comparison to performance of at least one alternative query plan.

15. A method according to claim 14 further comprising:

evaluating database performance of a selected query plan and at least one alternative query plan;

creating a set of performance maps comparing performance of a selected database system operating according to the selected query plan and operating according to the at least one alternative query plan under actual conditions; and

measuring and mapping performance of the selected query plan relative to the at least one alternative query plan in a predetermined range of runtime conditions comprising data characteristics.