Testing efficiency and stability of a database query engine

• US 8,892,544 B2
• Filed: 04/01/2009
• Issued: 11/18/2014
• 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
• Primary Examiner(s): Bhatia, Ajay
• Assistant Examiner(s): MUELLER, KURT A
• Application Number:
  US12/416,645
• Publication Number:
  US 20100257154A1
• Time in Patent Office: 2,057 Days
• Field of Search: 707/718
• 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 in a database management system (DBMS), comprising:

receiving a query;

generating a plurality of query execution plans for the query, wherein estimated costs corresponding to each of the plurality of execution plans are determined by a query optimizer prior to (i) an execution of the query using the plurality of query execution plans and (ii) a selection of a query execution plan by the query optimizer;

executing the query using each of the plurality of query execution plans;

determining performance of each of the query execution plans based on the execution of the query using the query execution plans;

classifying each of the query execution plans based on the performance and the corresponding estimated costs; and

computing an optimizer efficiency index based on the classifying of the query execution plans, wherein the efficiency index is utilized to determine efficiency of the query optimizer.

2. The method of claim 1, further comprising:

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

3. The method of claim 1, further comprising:

checking for incorrect results and stack traces during execution of said query using said query execution plans.

4. The method of claim 1, wherein said determining further comprises:

comparing said performance of said query execution plans with performance of said query execution plan selected by said query optimizer.

5. The method of claim 4, wherein said determining further comprises:

classifying said query execution plans into a plurality of performance categories based on said comparing.

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

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

classifying said compared query execution plan into a negative performance category if said compared query execution plan performs worse than said selected 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, further comprising:

retaining said query execution plans by storing said query execution plans to one or more trace files as abstract plans.

11. A computer program product including a non-transitory computer-readable storage medium having instructions stored thereon that, in response to execution by a computing device, causes the computing device to perform operations comprising:

receiving a query;

generating a plurality of query execution plans for the query, wherein estimated costs corresponding to each of the plurality of execution plans are determined by a query optimizer prior to (i) an execution of the query using the plurality of query execution plans and (ii) a selection of a query execution plan by the query optimizer;

executing the query using each of the plurality of query execution plans;

determining performance of each of the query execution plans based on the execution of the query using the query execution plans;

classifying each of the query execution plans based on the performance and the corresponding estimated costs; and

computing an optimizer efficiency index based on the classifying of the query execution plans, wherein the efficiency index is utilized to determine efficiency of the query optimizer.