Graphical Representation of Query Optimizer Search Space In A Database Management System

• US 20100161589A1
• Filed: 12/19/2008
• Published: 06/24/2010
• Est. Priority Date: 12/19/2008
• Status: Active Grant

• Current Assignee: iAnywhere Solutions Incorporated (SAP SE)
• Original Assignee: iAnywhere Solutions Incorporated (SAP SE)
• Inventors: NICA, Anisoara, Brotherston, Daniel Scott, Hillis, David William
• Granted Patent: US 8,145,621 B2
• Time in Patent Office: Days
• US Class Current: 707/713
• CPC Class Codes: G06F 16/242 Query formulation
• CPC Class Codes: G06F 16/2453 Query optimisation

Abstract

A system, method, and computer program product are provided for generating a graphical representation of a query optimization process. The method comprises the steps of parsing a search space log, presenting one or more evaluated access plans on an axis of a timeline, identifying a best access plan on the timeline, and outputting a graphical representation of the timeline. An additional system, method, and computer program product are provided for recording a query optimization process of a query optimizer.

Claims

1. A method for generating a graphical representation of a query optimization process, the method comprising:

parsing a search space log, the log comprising;

an order in which one or more evaluated access plans were evaluated,

a property for an object of one of the one or more evaluated access plans, and

a best access plan selected from the one or more evaluated access plans;

presenting the one or more evaluated access plans on an axis of a timeline, the timeline corresponding to the order;

identifying the best access plan on the timeline; and

outputting a graphical representation of the timeline.

2. The method of claim 1, wherein the property for the object comprises an estimated total cost of the object.

3. The method of claim 2, wherein the log further comprises an estimated total cost of an access plan equal to the sum of the estimated total cost of objects of the access plan.

4. The method of claim 3, wherein the estimated total cost of the access plan is shown on an additional axis of the timeline.

5. The method of claim 1, further comprising:

analyzing the graphical representation of the timeline; and

refining the query optimization process.

6. The method of claim 1, further comprising:

comparing the graphical representation of the timeline to a second graphical representation of a second timeline, the second timeline corresponding to a second search space log.

7. A system for generating a graphical representation of a query optimization process, comprising:

a parsing module to parse a search space log, the log comprising;

an order in which one or more evaluated access plans were evaluated,

a property for an object of one of the one or more evaluated access plans, and

a best access plan selected from the one or more evaluated access plans;

a presenting module to present the one or more evaluated access plans on an axis of a timeline, the timeline corresponding to the order;

an identifying module to identify the best access plan on the timeline; and

an outputting module to output a graphical representation of the timeline.

8. The system of claim 7, wherein the property for the object comprises an estimated total cost of the object.

9. The system of claim 8, wherein the log further comprises an estimated total cost of an access plan equal to the sum of the estimated total cost of objects of the access plan.

10. The system of claim 9, wherein the estimated total cost of the access plan is shown on an additional axis of the timeline.

11. The system of claim 7, further comprising:

an analyzing module to analyze the graphical representation of the timeline; and

a refining module to refine the query optimization process.

12. The system of claim 7, further comprising:

a comparing module to compare the graphical representation of the timeline to a second graphical representation of a second timeline, the second timeline corresponding to a second search space log.

13. A computer program product comprising a computer-usable medium having computer program logic recorded thereon for enabling a processor to generate a graphical representation of a query optimization process, the computer program logic comprising:

parsing means for enabling a processor to parse a search space log, the log comprising;

an order in which one or more evaluated access plans were evaluated,

a property for an object of one of the one or more evaluated access plans, and

a best access plan selected from the one or more evaluated access plans;

presenting means for enabling a processor to present the one or more evaluated access plans on an axis of a timeline, the timeline corresponding to the order;

identifying means for enabling a processor to identify the best access plan on the timeline; and

outputting means for enabling a processor to output a graphical representation of the timeline.

14. The computer program logic of claim 13, wherein the property for the object comprises an estimated total cost of the object.

15. The computer program logic of claim 14, wherein the log further comprises an estimated total cost of an access plan equal to the sum of the estimated total cost of objects of the access plan.

16. The computer program logic of claim 15, wherein the estimated total cost of the access plan is shown on an additional axis of the timeline.

17. The computer program logic of claim 13, further comprising:

analyzing means for enabling a processor to analyze the graphical representation of the timeline; and

refining means for enabling a processor to refine the query optimization process.

18. The computer program logic of claim 13, further comprising:

comparing means for enabling a processor to compare the graphical representation of the timeline to a second graphical representation of a second timeline, the second timeline corresponding to a second search space log.

19. A method for recording a query optimization process of a query optimizer, the method comprising:

generating an access plan, the access plan comprising an object;

evaluating the access plan;

logging an object of the access plan;

logging a property for the object;

logging an order in which the access plan was evaluated;

logging a best access plan;

determining whether to evaluate an additional access plan; and

logging a result of the determining whether to evaluate the additional access plan.

20. A system for recording a query optimization process of a query optimizer, comprising:

a generating module to generate an access plan, the access plan comprising an object;

an evaluating module to evaluate the access plan;

a first logging module to log an object of the access plan;

a second logging module to log a property for the object;

a third logging module to log an order in which the access plan was evaluated;

a fourth logging module to log a best access plan;

a determining module to determine whether to evaluate an additional access plan; and

a fifth logging module to log a result of the determining whether to evaluate the additional access plan.

21. A computer program product comprising a computer-usable medium having computer program logic recorded thereon for enabling a processor to record a query optimization process of a query optimizer, the computer program logic comprising:

generating means for enabling a processor to generate an access plan, the access plan comprising an object;

evaluating means for enabling a processor to evaluate the access plan;

first logging means for enabling a processor to log an object of the access plan;

second logging means for enabling a processor to log a property for the object;

third logging means for enabling a processor to log an order in which the access plan was evaluated;

fourth logging means for enabling a processor to log a best access plan;

determining means for enabling a processor to determine whether to evaluate an additional access plan; and

fifth logging means for enabling a processor to log a result of the determining whether to evaluate the additional access plan.