Testing Efficiency and Stability of a Database Query Engine

• US 20100257154A1
• Filed: 04/01/2009
• Published: 10/07/2010
• Est. Priority Date: 04/01/2009
• Status: Active Grant

• Current Assignee: Sybase Incorporated (SAP SE)
• Original Assignee: Sybase Incorporated (SAP SE)
• Inventors: PENDAP, Santosh Dattu, Chowdhuri, Sudipto Rai
• Granted Patent: US 8,892,544 B2
• Time in Patent Office: Days
• US Class Current: 707/718
• CPC Class Codes: G06F 16/24542 Plan optimisation

Abstract

Systems, methods and computer program products for testing the stability and efficiency of a database query engine are described herein. In an embodiment, all possible query execution plans considered by a query optimizer are retained in the form of abstract plans (APs). Each AP is then subsequently supplied with a query to test for stack traces and incorrect results that may occur during the execution of the query. An embodiment includes determining an optimizer efficiency index (OEI) by comparing each AP with a query execution plan chosen by the query optimizer and classifying the APs into various performance buckets based on the degree of improvement or degradation in performance with respect to the query execution plan chosen by the query optimizer. In another embodiment to determine the optimizer efficiency index, APs are ranked based on estimated cost and classified into a plurality of categories based on query optimizer estimates and actual execution behavior.

Claims

1. A method of testing a query engine in a database management system (DBMS), comprising:

(1) retaining all query execution plans considered by a query optimizer;

(2) executing a query using each of said query execution plans;

(3) determining performance of said query execution plans based on said execution;

(4) classifying said query execution plans based on said performance; and

(5) determining efficiency of said query optimizer by computing an optimizer efficiency index (OEI) based on said classification.

2. The method of claim 1, further comprising:

(6) retaining said query execution plans as abstract plans (APs).

3. The method of claim 1, further comprising:

(6) checking for incorrect results and stack traces in said execution of step (2).

4. The method of claim 1, wherein step (3) comprises:

comparing said performance of said query execution plans with a default query execution plan chosen by said query optimizer.

5. The method of claim 4, wherein step (3) further comprises:

classifying said query execution plans into a plurality of performance categories based on performance relative to said default query execution plan.

6. The method of claim 5, wherein said classifying step comprises:

classifying a query execution plan into a positive performance category if said query execution plan performs better than said default query execution plan; and

classifying said query execution plan into a negative performance category if said query execution plan performs worse than said default query execution plan.

7. The method of claim 1, further comprising,

ranking said query execution plans based on estimated cost;

and

classifying said query execution plans into a plurality of categories based on actual execution behavior.

8. The method of claim 7, wherein said actual execution behavior is based on a execution time associated with said query execution plans, resource consumption of said query execution plans, and logical I/O and physical I/O associated with said query execution plans.

9. The method of claim 7, wherein said estimated cost is based on a combination of logical I/O and physical I/O associated with said query execution plans.

10. The method of claim 1, wherein step (1) comprises:

(6) retaining said query execution plans by storing to one or more trace files as a plurality of abstract plans.

11. A system for testing a query engine in a database management system (DBMS), comprising:

a query optimizer configured to retain all query execution plans considered by said query optimizer;

an execution engine configured to execute a query using said query execution plans; and

a testing engine configured to determine performance of said query execution plans based on said execution.

12. The system of claim 11, wherein said testing engine classifies said query execution plans based on said performance.

13. The system of claim 12, wherein said testing engine determines efficiency of said query optimizer based on a plurality of classified query execution plans.

14. The system of claim 11, wherein said testing engine checks for incorrect results generated during said execution of said query.

15. The system of claim 11, wherein said testing engine compares said performance of said query execution plans with a default query execution plan chosen by said query optimizer.

16. The system of claim 15, wherein said testing engine classifies said query execution plans into a plurality of performance categories based on performance relative to said default query execution plan.

17. The system of claim 11, wherein said testing engine ranks said query execution plans based on based on estimated cost and classifies said query execution plans into a plurality of categories based on said estimated cost and actual execution behavior.

18. The system of claim 17, wherein said estimated cost is based on execution time associated with said query execution plans, resource consumption of said query execution plans, and logical I/O and physical I/O associated with said query execution plans.

19. The system of claim 17, wherein said estimated cost is based on a combination of logical I/O and physical I/O associated with said query execution plans.

20. A computer program product having control logic stored therein, said control logic enabling a processor to test a query engine in a database management system, said control logic comprising:

first computer readable program code means for enabling a processor to retain a plurality of query execution plans;

second computer readable program code means for enabling a processor to execute a query using said query execution plans;

third computer readable program code means for enabling a processor to determine performance of said query execution plans;

fourth computer readable program code means for enabling a processor to classify said query execution plans based on said performance; and

fifth computer readable program code means for enabling a processor to determine at least an efficiency of said query optimizer by computing an optimizer efficiency index (OEI) based on said classification.