Evaluation of database query plan robustness landmarks using operator maps or query maps

• US 9,177,023 B2
• Filed: 02/02/2009
• Issued: 11/03/2015
• Est. Priority Date: 02/02/2009
• 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: Graefe, Goetz, Kuno, Harumi, Wiener, Janet L.
• Primary Examiner(s): Trujillo, James
• Assistant Examiner(s): MUELLER, KURT A
• Application Number:
  US12/364,063
• Publication Number:
  US 20100198810A1
• Time in Patent Office: 2,465 Days
• Field of Search: 707/765
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24542 Plan optimisation

Abstract

Computer-implemented systems and associated operating methods create and use an operator map to identify and evaluate database query plan robustness landmarks. The computer-implemented system comprises logic that creates a map evaluating performance of an implementation of a database engine operator during execution under actual operating conditions. The logic measures and maps performance for a selected range of runtime conditions including resource availability and data characteristics.

Claims

1. A computer-implemented system comprising:

a processor;

a computer-readable data storage medium to store computer-readable program code that is executable by the processor to implement logic,

wherein the logic creates a performance map evaluating performance of an implementation of a database engine operator or query plan during execution under actual conditions, the logic measuring and mapping performance for a selected range of runtime conditions comprising data characteristics,

wherein the logic analyzes the maps to identify landmarks in relation to the database engine operator or the query plan in which performance degrades in a predetermined manner.

2. The system according to claim 1 further comprising:

a set of operator implementations or query plans to be evaluated;

a set of runtime resource conditions to be evaluated;

a set of data characteristics; and

the logic invoking operators or query plans and measuring a plurality of performance characteristics for selected operator implementations or query plans and selected runtime resource conditions at a plurality of data characteristics, the logic creating a set of performance measurements for the selected operator implementations or query plans and selected runtime conditions.

3. The system according to claim 2 further comprising:

the logic identifying regions in at least one performance map for which performance of an operator or query plan degrades more than a selected amount, the logic collecting the identified regions, identifying coordinates in the arrangement of the identified regions as the landmarks, and creating at least one performance map that include the identified regions and coordinates.

4. The system according to claim 3 further comprising:

the logic that accesses the at least one performance map to identify regions under which performance of a selected operator implementation degrades in a manner different from a predetermined proper manner, the logic analyzing the maps to identify landmarks in relation to the query plan wherein query performance degrades in a predetermined manner.

5. The system according to claim 3 further comprising:

a query plan;

a set of system configuration and contention information;

a set of information quantifying risk of encountering error in cardinality estimation during usage of database operators; and

the logic accessing, for nodes in the query plan, expected conditions from the set of system configuration and contention information, the logic calculating data characteristics for the expected conditions and risk.

6. The system according to claim 5 further comprising:

the logic that looks up landmarks from the at least one performance map in a range of conditions and data characteristics in the set of system configuration and contention information, and creates a landmark set comprising landmarks and probability of complying with the conditions.

7. The system according to claim 1 wherein:

the logic creates the map evaluating performance of an implementation of the database engine operator and analyzes the map to identify the landmarks in relation to the query plan based on evaluation of database operators in actual performance measurements acquired over multiple runtime conditions.

8. The system according to claim 1 further comprising:

the logic that creates the map evaluating performance of an implementation of the database engine operator and analyzes the map to identify the landmarks in relation to the query plan based on performance tests of individual operators under data characteristics and predefined resource availability conditions, evaluation of performance across a selected variety of conditions considering performance as a continuous function across the variety of 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 database query plan robustness landmarks, the computer readable program code further comprising;

code causing the controller to create the performance map evaluating performance of an implementation of the database engine operator during execution under actual conditions; and

code causing the controller to measure and map performance for a selected range of runtime conditions comprising data characteristics.

10. A computer-implemented system comprising:

a processor;

a computer-readable data storage medium to store computer-readable program code that is executable by the processor to implement logic,

wherein the logic accesses at least one performance map created by acquisition during execution of an implementation of a database engine operator under actual conditions, the logic measuring and mapping performance for a selected range of runtime conditions to identify regions under which performance of a selected operator implementation degrades in a manner different from a predetermined proper manner, the logic analyzing the maps to identify landmarks in relation to a selected query plan wherein query performance degrades in a predetermined manner.

11. The system according to claim 10 further comprising:

a query plan;

a set of system configuration and contention information;

a set of information quantifying risk of encountering error in cardinality estimation during usage of database operators; and

the logic accessing, for nodes in the query plan, expected conditions from the set of system configuration and contention information, the logic calculating data characteristics for the expected conditions and risk.

12. The system according to claim 11 further comprising:

the logic that looks up landmarks from the at least one performance map in a range of conditions and data characteristics specified in the set of system configuration and contention information, and creates a landmark set comprising landmarks and probability of complying with the conditions.

13. The system according to claim 10 further comprising:

an article of manufacture comprising;

a controller-usable medium having a computer readable program code embodied in a controller for evaluating database query plan robustness landmarks, the computer readable program code further comprising;

code causing the controller to access at least one performance map created by acquisition during execution of an implementation of a database engine operator under actual conditions;

code causing the controller to measure and map performance for a selected range of runtime conditions to identify regions under which performance of a selected operator implementation degrades in a manner different from a predetermined proper manner; and

code causing the controller to analyze the maps to identify landmarks in relation to a selected query plan wherein query performance degrades in a predetermined manner.

14. A computer-executed method of processing data comprising:

evaluating database query plan robustness landmarks, by a processor, comprising;

creating a performance map evaluating performance of an implementation of a database engine operator during execution under actual conditions, by the processor;

measuring and mapping performance for a selected range of runtime conditions comprising data characteristics,

analyzing the performance map to identify landmarks in relation to the database engine operator in which performance degrades in a predetermined manner, by the processor.

15. The method according to claim 14 further comprising:

evaluating database query plan robustness landmarks further comprising;

accessing at least one performance map created by acquisition during execution of an implementation of a database engine operator under actual conditions; and

measuring and mapping performance for a selected range of runtime conditions to identify regions under which performance of a selected operator implementation degrades in a manner different from a predetermined proper manner.