Derby-4587-Add tools for improved analysis and understanding of query plans and execution statistics
Design in Detail
Quite frequently, the users of Derby have troubles in comprehending the steps followed by the optimizer when it translates a query into a query plan, and details such as time to execute, resource utilization at each step of the query plan. There are low-level features in Derby which capture this information and record it, such as logQueryPlan, and the XPLAIN tables, so this project will lead to a development of a new tool which can process the query plan and execution statistics information and present it in a more comprehensible fashion i.e. a graphical interpretation of the query plan.
Please see the suggesting Process View of the Design at http://nirmalfdo.blogspot.com/2010/05/suggesting-process-view-of-design.html
Tool-I is a small Java program using standard JDBC which can read the query execution data for a SQL statement from the Derby XPLAIN tables and emit the data in XML tagged format. For each step in a query, we will retrieve the query execution data as XML data and combine these XML fragments to create a single large Raw XML document which will also contain necessary XML document related properties.
Tool-II will format the XML-formatted data into visual information in a browser, using an XSLT style sheet.
XSLT Style Sheet
First I will do a research on Derby to recognize all the possible instances that can be occur in a query such as sort, hash-join etc. Next I planned to write the template, as it iteratively looks for the instance occurred at that particular step and displays the image + details (execution time, resource usage), for all the steps that were followed when executing the query. Here, image and details can be obtained through the XML file emitted by the query plan.