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 Wed, 09 Jun 2010 01:03:13 GMT

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

Bryan Pendleton commented on DERBY-4587:

Yes, that's precisely what I meant. With that change in place, it seems
like we should be able to add something like the diff below, and quickly
get a fairly substantial suite of tests which call the new XML generation tools.

The diff below patches into a common subroutine in the existing XplainStatisticsTest
test suite, and makes it so that, each time we capture a set of XPLAIN data for
a query in that suite, we then immediately export that data as XML.

Of course, this doesn't actually verify the correctness of the XML, but it does
drive the new XML export code, and so it provides a basis for us to build
the next level of tests with.

Index: XplainStatisticsTest.java
--- XplainStatisticsTest.java	(revision 949434)
+++ XplainStatisticsTest.java	(working copy)
@@ -490,6 +490,20 @@
         throws SQLException
         s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)");
+	ResultSet rs = s.executeQuery(
+                    "select stmt_id from xpltest.sysxplain_statements");
+	while ( rs.hasNext() )
+	{
+	    String stmt_id = rs.getString(1);
+	    AccessDatabase access =
+		new AccessDatabase(getConnection(), "XPLTEST", stmt_id);
+	    access.initializeDataArray();
+	    access.createXMLFragment();
+	    CreateXMLFile xml_file = new CreateXMLFile();
+	    xml_file.writeTheXMLFile(access.getData(), stmt_id + ".xml",
+				stmt_id + ".xsl");
+	}
     private void verifyXplainUnset(Statement s)
         throws SQLException

> 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: 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.diff, Derby_Query_Plan_Screen_Shot.jpg, PostgreSQL
license.jpg, Read_Me.txt, Source.rar
> 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