db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
Date Tue, 06 Jul 2010 00:34:50 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12885378#action_12885378

Bryan Pendleton commented on DERBY-4587:

It seems to me that the basic XML exporting functionality is
coming along quite well!

I agree with you that doing more work on the visualization is
important, and I think you are already starting to look at that.

I'd really like to concentrate on getting a more complete 
regression test in place. My overall feeling is that we are
getting close enough to having the XML plan exporter operational
that we can start to think about committing it.

But in order to commit it, we need to have enough of a regression
test harness in place, that we can be confident that others
can work with the code and run the regression tests and know
that the XML plan exporter is being adequately tested.

So I'd like to see us concentrate on how we might extend the
current XplainStatisticsTest inter-action, to implement a
thorough regression test setup.

>From a code point of view, I'd like to see some sort of
simple assertions in the XML portion of the tests, along the
lines of this pseudocode:

   1) Set up the XPLAIN tables and run some sort of SQL to capture data
   2) Verify the XPLAIN table contents
   --- steps (1) and (2) are in the current tests already
   3) Export the XPLAIN data to XML using PlanExporter
   --- you have got this working in the latest patch already
   4) Verify the XML contents

Step (4) is somewhat involved, and I think it probably involves
writing a few common subroutines that we can use to perform
simple assertions against the generated XML:

 - does the XML contain this tag?
 - how many occurrences of this tag are there?
 - check the contents of this tag against this string

I've been thinking that we could perhaps have some utility routines
that used XPath expressions to match portions and contents of
the XML document, and use those to build up regression test assertions.

So the test code would then contain lines like:

   assertEquals(1, XmlTagCount("//rowCount");
   assertEquals("TABLESCAN", XmlTagContents("//node[2]/resultNodeType"));

What do you think? Would some sort of simple XPath-based utility
code be enough to support some solid regression tests?

> Add tools for improved analysis and understanding of query plans and execution statistics
> -----------------------------------------------------------------------------------------
>                 Key: DERBY-4587
>                 URL: https://issues.apache.org/jira/browse/DERBY-4587
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL, Tools
>            Reporter: Bryan Pendleton
>            Assignee: C.S. Nirmal J. Fernando
>         Attachments: basic_html-2.2.jpg, basic_html-2.3.jpg, basic_html-2.jpg, basic_html-3.jpg,
basic_html-4.1.jpg, basic_html-4.2.jpg, Derby Query Plan Screen Shot 2.jpg, DERBY-4587-tool-2.diff,
DERBY-4587-tool-3.diff, DERBY-4587-tool-4.diff, DERBY-4587-tool-5.diff, DERBY-4587-tool-6.diff,
DERBY-4587-tool-7-b.diff, DERBY-4587-tool-7.diff, DERBY-4587-tool-8.diff, DERBY-4587-tool-test1.diff,
DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, Derby_Query_Plan_Screen_Shot.jpg, PostgreSQL
license.jpg, Read_Me.txt, screenshot-1.jpg, screenshot-2.jpg, screenshot-3.jpg, Simple HTML
View (Pure XSL).jpg, Source.rar, test.xml, test4.xsl, vanilla_html.xsl, vanilla_html.xsl,
vanilla_html.xsl, vanilla_html.xsl, xml_doc_screenshot.jpg, xml_doc_screenshot.jpg
> I think it would be great to see some work in the area of tools for helping
> with the analysis of complex query execution. Quite frequently, users of
> Derby have trouble comprehending (a) how their query is being translated
> into a query plan by the optimizer, and (b) what the execution-time resource
> usage of the various parts of the query is.
> There are low-level features in Derby which capture this information and
> record it, such as logQueryPlan, and the XPLAIN tables, but there is a lot
> of opportunity for designing higher-level tools which can process the query
> plan and execution statistics information and present it in a more
> comprehensible fashion. 

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message