[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12898782#action_12898782 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Thanks Bryan for adding those tests! They ran fine for me! > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff, > 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-9.1.diff, > DERBY-4587-tool-9.10-eol_style_native.diff, > DERBY-4587-tool-9.11-license_header_inXSL.diff, DERBY-4587-tool-9.12.diff, > DERBY-4587-tool-9.12.diff, DERBY-4587-tool-9.12.diff, > DERBY-4587-tool-9.2.diff, DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, > DERBY-4587-tool-9.5.diff, DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.8-modified.diff, > DERBY-4587-tool-9.8.diff, DERBY-4587-tool-9.9-formatting.diff, > DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12898753#action_12898753 ] Bryan Pendleton commented on DERBY-4587: I added a few simple test cases to the XplainStatisticsTest to cover some of the situations I thought might be interesting, and committed the patch to the trunk as revision 985762. > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff, > 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-9.1.diff, > DERBY-4587-tool-9.10-eol_style_native.diff, > DERBY-4587-tool-9.11-license_header_inXSL.diff, DERBY-4587-tool-9.12.diff, > DERBY-4587-tool-9.12.diff, DERBY-4587-tool-9.12.diff, > DERBY-4587-tool-9.2.diff, DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, > DERBY-4587-tool-9.5.diff, DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.8-modified.diff, > DERBY-4587-tool-9.8.diff, DERBY-4587-tool-9.9-formatting.diff, > DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12898716#action_12898716 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Hi Bryan, Thanks for reviewing the patch! Whoops, I interpreted a behavior incorrectly! I've modified the 9.12 patch, so that it will print an error message if the schema is incorrect. Regarding test cases I'll give a try soon! Thanks. > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff, > 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-9.1.diff, > DERBY-4587-tool-9.10-eol_style_native.diff, > DERBY-4587-tool-9.11-license_header_inXSL.diff, DERBY-4587-tool-9.12.diff, > DERBY-4587-tool-9.12.diff, DERBY-4587-tool-9.2.diff, > DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, DERBY-4587-tool-9.5.diff, > DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.8-modified.diff, DERBY-4587-tool-9.8.diff, > DERBY-4587-tool-9.9-formatting.diff, DERBY-4587-tool-9.diff, > DERBY-4587-tool-test1.diff, DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, > derby-logo.png, 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12898705#action_12898705 ] Bryan Pendleton commented on DERBY-4587: Hi Nirmal, thanks for the latest patch. I don't understand the verifySchemaExistance() method in the latest 9.12 patch. What is the intended method behavior if the schema name is *not* found in the getSchemas() list? Also, I think it would be good if the patch could include a new test case in XplainStatisticsTest, which tested the behavior when an incorrect schema name is provided to AccessDatabase. Similarly, could the patch include a new test case which tested the behavior when a security exception is thrown by writeTheXMLFile because it is trying to write to an invalid location? For example, we could try to write to the file "/DerbyXMLTest.xml", which should fail on all our platforms because Derby is generally not authorized to write to the root of the file system. > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff, > 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-9.1.diff, > DERBY-4587-tool-9.10-eol_style_native.diff, > DERBY-4587-tool-9.11-license_header_inXSL.diff, DERBY-4587-tool-9.12.diff, > DERBY-4587-tool-9.12.diff, DERBY-4587-tool-9.2.diff, > DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, DERBY-4587-tool-9.5.diff, > DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.8-modified.diff, DERBY-4587-tool-9.8.diff, > DERBY-4587-tool-9.9-formatting.diff, DERBY-4587-tool-9.diff, > DERBY-4587-tool-test1.diff, DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, > derby-logo.png, 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12898251#action_12898251 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Hi Kristian, No, a comment can be added as the first entry, I tested modified style sheets in my environment. Thanks. > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff, > 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-9.1.diff, > DERBY-4587-tool-9.10-eol_style_native.diff, > DERBY-4587-tool-9.11-license_header_inXSL.diff, DERBY-4587-tool-9.12.diff, > DERBY-4587-tool-9.2.diff, DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, > DERBY-4587-tool-9.5.diff, DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.8-modified.diff, > DERBY-4587-tool-9.8.diff, DERBY-4587-tool-9.9-formatting.diff, > DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12898096#action_12898096 ] Kristian Waagan commented on DERBY-4587: Thanks for catching and addressing the problem with the headers in the XSL files, Nirmal. I completely forgot about that... It's been a while since I worked with XSL/XML, I don't remember if there are rules about which element must be first in such a file - that is, is it okay to keep the license header where it is, or should it be moved to after some mandatory first tag? > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff, > 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-9.1.diff, > DERBY-4587-tool-9.10-eol_style_native.diff, > DERBY-4587-tool-9.11-license_header_inXSL.diff, DERBY-4587-tool-9.12.diff, > DERBY-4587-tool-9.2.diff, DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, > DERBY-4587-tool-9.5.diff, DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.8-modified.diff, > DERBY-4587-tool-9.8.diff, DERBY-4587-tool-9.9-formatting.diff, > DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12898057#action_12898057 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Hi Bryan, Thanks for committing. I have another patch which is addressed Kristian's comments on c) & n). So, now onwards I think we should work on separate jiras as you mentioned, I will create sub tasks for 1,3, & 4. I think with the help of Kristian the latest patch solved the 2. Thanks. > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff, > 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-9.1.diff, > DERBY-4587-tool-9.10-eol_style_native.diff, > DERBY-4587-tool-9.11-license_header_inXSL.diff, DERBY-4587-tool-9.12.diff, > DERBY-4587-tool-9.2.diff, DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, > DERBY-4587-tool-9.5.diff, DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.8-modified.diff, > DERBY-4587-tool-9.8.diff, DERBY-4587-tool-9.9-formatting.diff, > DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12898049#action_12898049 ] Bryan Pendleton commented on DERBY-4587: Committed patch 9.11 to the trunk as revision 985075. > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff, > 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-9.1.diff, > DERBY-4587-tool-9.10-eol_style_native.diff, > DERBY-4587-tool-9.11-license_header_inXSL.diff, DERBY-4587-tool-9.2.diff, > DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, DERBY-4587-tool-9.5.diff, > DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.8-modified.diff, DERBY-4587-tool-9.8.diff, > DERBY-4587-tool-9.9-formatting.diff, DERBY-4587-tool-9.diff, > DERBY-4587-tool-test1.diff, DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, > derby-logo.png, 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12898046#action_12898046 ] Bryan Pendleton commented on DERBY-4587: Your latest patch looks good to me; I will commit it. If you haven't already done so, I think you could file separate sub-task Jira entries for the following ideas that were brought up during the reviews: 1) Investigate character set and encoding issues. 2) Investigate security and PrivilegedActionException handling 3) Redesign the tool API for simplicity 4) Allow exporting of multiple query plans in a single operation These issues seem sufficiently complicated that they would be easier to track in separate Jira entries. > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff, > 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-9.1.diff, > DERBY-4587-tool-9.10-eol_style_native.diff, > DERBY-4587-tool-9.11-license_header_inXSL.diff, DERBY-4587-tool-9.2.diff, > DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, DERBY-4587-tool-9.5.diff, > DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.8-modified.diff, DERBY-4587-tool-9.8.diff, > DERBY-4587-tool-9.9-formatting.diff, DERBY-4587-tool-9.diff, > DERBY-4587-tool-test1.diff, DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, > derby-logo.png, 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[
https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12897925#action_12897925
]
Kristian Waagan commented on DERBY-4587:
Nirmal, some answers to your questions about my comments:
--- AccessDatabase
c) Well, the easiest is probably to use PreparedStatement and ps.setString(x,
s).
By specifying a system table and a comment as the schema, say
"sys.systables --", or saying " and 1 = 1" in a where clause, one can
make the tool query tables it isn't supposed to access. Nothing severe in this
case, but it's good to be aware of these issues in general. Luckily you cannot
specify multiple SQL statements in (most?) JDBC calls, because then you could
easily add delete or update statements as well.
You could also use Connection.getMetaData(), and then use one of the
DatabaseMetaData.getSchemas() methods to verify that the schema actually exists.
--- CreateXMLFile
n) Since you know only an IOException can be thrown within the
PrivilegedExceptionAction, you can add a try - catch block where you unwrap the
IOException, something like this:
try {
// PrivilegedActionException wrapping IOException thrown here somewhere
} catch (PrivilegedActionException pae) {
throw (IOException)pae.getCause(); // or, according to the old API,
pae.getException().
}
You can then remove PrivilegedActionException from the throws clause of the
method.
p) I'm not quite sure. I don't have the cycles to follow up on this one right
now, so it would be nice if someone else can give you more feedback on this. If
you don't hear anything, it's probably not a big issue.
I also noticed that CreateHTMLFile only contains a method that could have been
static. If you want to keep the class, you should make the no-arg constructor
private and the getHTML-method static. Alternatively, you could move the method
into CreateXMLFile or somewhere else. Your call :)
Regards,
> 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl,
> advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg,
> advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff,
> 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-9.1.diff,
> DERBY-4587-tool-9.10-eol_style_native.diff, DERBY-4587-tool-9.2.diff,
> DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, DERBY-4587-tool-9.5.diff,
> DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.7.diff,
> DERBY-4587-tool-9.8-modified.diff, DERBY-4587-tool-9.8.diff,
> DERBY-4587-tool-9.9-formatting.diff, DERBY-4587-tool-9.diff,
> DERBY-4587-tool-test1.diff, DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff,
> derby-logo.png, 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12897830#action_12897830 ] Rick Hillegas commented on DERBY-4587: -- Hi Nirmal, I think your plan makes sense: agree on an api and then make the changes in a separate jira. Thanks. > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff, > 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-9.1.diff, > DERBY-4587-tool-9.2.diff, DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, > DERBY-4587-tool-9.5.diff, DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12897817#action_12897817 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Hi Rick, Thanks for the replies, I'll add the STMT_ID. Yeah, you got it right! I think we should come to a conclusion about the API of the tool and it may be handle in a different jira linked to this, what do you think? Thanks. > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff, > 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-9.1.diff, > DERBY-4587-tool-9.2.diff, DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, > DERBY-4587-tool-9.5.diff, DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=1289#action_1289 ] Rick Hillegas commented on DERBY-4587: -- Hi Nirmal. Thanks for the explanation of the Java script usage. By the way, in my environment I notice that when I use the -adv switch, the output contains a pointer to the original location of the stylesheet--so I don't have to copy the style sheet into my current directory. I think that's great. If I am understanding the tool correctly, it seems to me that it operates in two modes (depending on the switches you specify): o TransformMode -- In this mode, the tool transforms the output, using the specified stylesheet. The output does not contain a pointer to a stylesheet. o PassthroughMode -- In this mode, the tool does NOT transform the output. Instead, the tool outputs the raw xml, prefixing it with a pointer to the desired stylesheet. In this mode, the transformation happens inside the browser. Thanks, -Rick > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff, > 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-9.1.diff, > DERBY-4587-tool-9.2.diff, DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, > DERBY-4587-tool-9.5.diff, DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12897770#action_12897770 ] Rick Hillegas commented on DERBY-4587: -- Hi Nirmal. The reason that I want to see a statement id is to cover the case when the output contains plans for multiple queries. In this scenario, you might want to run the same query more than once, changing the environment inbetween. I think it would be useful to have some way of matching the plans to the XPLAIN tables in case you want to drill-down. I think that the statement id could appear at the top along with the statement text. Thanks. > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff, > 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-9.1.diff, > DERBY-4587-tool-9.2.diff, DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, > DERBY-4587-tool-9.5.diff, DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12897751#action_12897751 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Rick, do you really like to see STMT_ID in the HTML? Since the query is displayed I feel like it's not useful. If that addition is useful where you like it to be displayed? Is it good to be in the header section? Thanks. > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff, > 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-9.1.diff, > DERBY-4587-tool-9.2.diff, DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, > DERBY-4587-tool-9.5.diff, DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12897695#action_12897695 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Hi Rick, I have few comments regarding your latest comment. * If the using style sheet is advanced (i.e. contains JS), currently tool follows a different way of transforming as I've described in previous comment. In this mode it can only provides a XML which is transformable through a web browser, given that the provided style sheet is present in the relative location to XML. * Current behavior of the tool is if a user provided an incorrect style sheet path, the tool will automatically use the "vanilla_html.xsl" style sheet which is attached with derbytools.jar (this is not valid for advance feature). * The "outputFileName" you refers to should be without an extension (i.e. xml or html), I think you meant the same thing. These are the only comments that I have for now, regarding the API you have suggested. I think it's more simpler, thanks for suggesting. > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff, > 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-9.1.diff, > DERBY-4587-tool-9.2.diff, DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, > DERBY-4587-tool-9.5.diff, DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12897677#action_12897677 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Hi Rick, This is for your comment on 11th at 2.15pm. I can add all the information in XPLAIN tables into the XML, but since it will involve lot of database queries including JOINS, I am not sure will it affect the performance of the tool. No I haven't developed any style-sheet for non-html output. >Are the plus signs produced by the advanced style sheet supposed to allow you >to expand and collapse elements? They don't work that way for me in Firefox, >Chrome, or >Safari. Yeah, those + signs intended to allow collapsing and expanding behavior, but you have to use advance option (-adv) of the tool, if you want to use that style sheet (i.e. advancedViewXSL.xsl or advancedViewXSL2.xsl) and in this case you will not get a html output instead you can only get the XML output. Opening that XML in your browser will provide you the JS features (i.e. browser will do all the transformation). We had to do like this when we're using JS in our style sheet, since Xalan is not supporting transformation of style sheets with JS to HTML. Thanks. > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff, > 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-9.1.diff, > DERBY-4587-tool-9.2.diff, DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, > DERBY-4587-tool-9.5.diff, DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12897401#action_12897401 ] Rick Hillegas commented on DERBY-4587: -- I would like to propose the following command line api for PlanExporter. Partly I am doing this because this api seems simpler to me. My other motivation is to verify that I am not missing any of the functionality provided by the tool. Thanks. java org.apache.derby.tools.PlanExporter dbURL schemaName outputFileName [ -xsl styleSheet ] [ -stmt statementID ] dbURL :== An url appropriate for connecting via DriverManager.getConnection() schemaName :== A case-sensitive schema name outputFileName :== Absolute or relative name of file to which output will be written styleSheet :== Absolute or relative path name to an xsl style sheet for transforming the output. statementID :== A 36 character statement id string from schemaName.SYSXPLAIN_STATEMENTS.STMT_ID If a stylesheet is specified, then PlanExporter will use that stylesheet to transform the output. Otherwise, no stylesheet will be applied. This means that, by default, raw xml output is produced. In order to get html output, you must specify a Derby-supplied html stylesheet or one of your own. Of course, you can also specify a stylesheet which does not produce html output. If a statement id is specified, then the output file will contain only that statement's plan. Otherwise, the output file will contain plans for all of the statements in schemaName.SYSXPLAIN_STATEMENTS. > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff, > 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-9.1.diff, > DERBY-4587-tool-9.2.diff, DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, > DERBY-4587-tool-9.5.diff, DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12897364#action_12897364 ] Rick Hillegas commented on DERBY-4587: -- Thanks again for this great tool, Nirmal. I have some more comments. I started experimenting with customized style sheets. I had to poke around the code to find the style sheets. I have logged DERBY-4768 to encourage us to publish the template style sheets. Once I found the style sheets, it appeared to me that PlanExporter only presents a subset of the information in the XPLAIN tables. I would like to see more information: a) I think the xml output should include all of the relevant fields/nodes represented in the XPLAIN tables. The xsl designer and other display tools can suppress and format the xml output as desired. b) The html output should be readable but it does not have to be complete. I would also appreciate seeing the following fields in the html output: i) SYSXPLAIN_STATEMENTS.STMT_ID ii) SYSXPLAIN_STATEMENTS.XPLAIN_TIME A couple questions: 1) Are there any style sheets for non-html output? 2) Are the plus signs produced by the advanced style sheet supposed to allow you to expand and collapse elements? They don't work that way for me in Firefox, Chrome, or Safari. > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff, > 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-9.1.diff, > DERBY-4587-tool-9.2.diff, DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, > DERBY-4587-tool-9.5.diff, DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12897256#action_12897256 ] Rick Hillegas commented on DERBY-4587: -- It would be great if the tool were flexible enough to handle a couple usage patterns. For instance: a) You get one plan in your output file if you specify a statement id. b) You get all plans in your output file if you ask for them. > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff, > 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-9.1.diff, > DERBY-4587-tool-9.2.diff, DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, > DERBY-4587-tool-9.5.diff, DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12897111#action_12897111 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Thanks Rick for the verification I might need to submit a documentation patch removing the requirement of xalan. > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff, > 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-9.1.diff, > DERBY-4587-tool-9.2.diff, DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, > DERBY-4587-tool-9.5.diff, DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12897017#action_12897017 ] Kristian Waagan commented on DERBY-4587: I'll answer Nirmal's replies to my comments in a separate post (after having looked into them), just want to comment on what Rick said. Rick, you're hoping all plans go into one file. I see this as both an advantage and a disadvantage. With the current state of the tool, I think it would be confusing to have like 5 - 40 queries in one file. So, here are some options I see (feel free to add your own!): a) Everything in one file, use horizontal separators and maybe add some navigational aids (i.e. table of contents, anchor at the top) b) Keep each query in its own file. c) As above, but additionally create a separate file for navigation with (relative) links to the individual plans. d) Add a(nother) tool to split up the single large output file. Now, personally I would like the only requirement for the tool to be a web browser (graphical or console based) - continuing along the lines above may move us in the direction of a standalone tool with it's own GUI... Another issue with selecting all available plans is security (leaking information). How is access control currently handled by the XPLAIN functionality? For instance, will internal procedure queries [1] be traced as well? Can access to those plans be limited? Does it make sense to do so? [1] I.e., if the user does 'call myBusinessLogicProcedure', will queries issued from inside myBusinessLogicProcedure be traced and accessible to the user issuing the call? This isn't directly related to the PlanExporter as such, more the XPLAIN functionality I guess. > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff, > 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-9.1.diff, > DERBY-4587-tool-9.2.diff, DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, > DERBY-4587-tool-9.5.diff, DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12897013#action_12897013 ] Rick Hillegas commented on DERBY-4587: -- Hi Nirmal, I have run PlanExporter successfully with just derby.jar and derbytools.jar in the classpath. The query being analyzed was a simple one: select * from countries where country like 'B%' > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff, > 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-9.1.diff, > DERBY-4587-tool-9.2.diff, DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, > DERBY-4587-tool-9.5.diff, DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12896991#action_12896991 ] Rick Hillegas commented on DERBY-4587: -- Thanks for the quick response, Nirmal. Concerning (2): The XPLAIN machinery fits a common pattern of log monitoring: you start recording, you run some experiments, you end recording, you analyze the results. In this pattern, it is common to want to analyze all of the experiments which were recorded. I'm not following your question about naming the generated files. I was hoping that all of the recorded plans would go into a single output file. Thanks. > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff, > 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-9.1.diff, > DERBY-4587-tool-9.2.diff, DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, > DERBY-4587-tool-9.5.diff, DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12896982#action_12896982 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Hi Rick, Thanks for your feedback. 1) I'll modify the arguments, in my next patch. 2) Don't you think that a user will dislike when plans for all the tracked statements get generated each and every time he executed the tool? It may take considerable amount of time given that there are lots of tracked queries. Also we may get into trouble when naming the generated files. What do you think? 3) Pardon me about the comment, as I said my above reply to Kristian I'll modify it to "Apache Derby Query Explainer (DERBY-4587)" in my next patch. PS: I suspect that the tool does not need Xalan.jar in the CLASSPATH, both to pass tests and generate query plans. Instead it uses the inbuilt Xalan classes in java. Can you please verify this, if you got some time? Thanks. > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff, > 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-9.1.diff, > DERBY-4587-tool-9.2.diff, DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, > DERBY-4587-tool-9.5.diff, DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12896946#action_12896946 ] Rick Hillegas commented on DERBY-4587: -- Thanks for this excellent contribution to Derby, Nirmal. Here are a couple initial comments: o The PlanExporter tool takes a connection URL argument as well as special username and password arguments. In contrast, our other tools only take a connection URL argument (see the api for dblook and SignatureChecker). For those tools, the username and password are part of the connection URL. Separating out the username and password arguments raises the awkward possibility that they may conflict with the corresponding attributes in the connection URL. In addition, for embedded databases which don't need authentication, it seems awkward to have to put dummy username/password arguments on the command line. In order to avoid these awkwardnesses and to promote a common api across our tools, I would rather see PlanExporter behave like dblook and SignatureChecker. o It would be nice to be able to run the tool without having to specify a statement id. In this mode, I could imagine that plans would be produced for all tracked statements. Our other tools do not require the user to query the system tables before use and I think it would be good to reproduce that friendly experience. o The xml output contains a header comment, giving credit to the author. In general, Derby follows the Apache way by not singling out individuals for special credit either in the code or in production artifacts. People who want to understand the contribution history of a component should be able to recover that information from JIRA. The header comment should be removed. > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff, > 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-9.1.diff, > DERBY-4587-tool-9.2.diff, DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, > DERBY-4587-tool-9.5.diff, DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12896920#action_12896920 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Hi Kathey & Rick, Thanks for looking into the tool, hope to get your feedback. > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff, > 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-9.1.diff, > DERBY-4587-tool-9.2.diff, DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, > DERBY-4587-tool-9.5.diff, DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[
https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12896914#action_12896914
]
C.S. Nirmal J. Fernando commented on DERBY-4587:
Hi Kristian,
Thanks for looking into the code and give me your feedback, I find
them very useful, I'll attach a patch soon.
Please see my comments below.
a) Pardon me for not adding the license header. Thanks for
looking into that.
--- AccessDatabase
b) If it is the case I'll change to dbUrl.indexOf("://").
c) It's better if you can help me a bit on this, I'm not that familiar
of using meta data calls.
d) Whoops, I forgot to removed those automatic generated @author
tags.
e) I'll modify this.
f) Yep, it's better not to shutdown, instead we can only close the connection.
--- PlanExporter
g) I'll make it to private. Tool runs with the permissions given to
derbytools.jar.
--- TreeNode
h) I'll make it to package-private.
i) I'll make it implicit. No need to explicitly inherit.
j) Removed it.
k) I might had a reason earlier, but you're right no need of a String now.
--- CreateHTMLFile
l) I'll add this.
m) thanks for the idea.
--- CreateXMLFile
n) Actually I can't think of a way of doing that. Can you please explain a bit.
o) I think I should change it to "Apache Derby Query Explainer (DERBY-4587)". :)
p) I'm afraid not, will using the default be a problem?
Thank you very much!
> 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl,
> advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg,
> advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff,
> 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-9.1.diff,
> DERBY-4587-tool-9.2.diff, DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff,
> DERBY-4587-tool-9.5.diff, DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff,
> DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff,
> DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png,
> 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[
https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12896873#action_12896873
]
Kristian Waagan commented on DERBY-4587:
Hi,
As part of some other work, I plan to take the PlanExporter for a spin.
I think such a tool has the potential to significantly improve the user
friendliness
of Derby.
First, I have not followed development closely, and since I'm short on time at
the moment, I don't have the time to investigate too much. However, I'm posting
my comments in case they can be useful.
Below are some comments originating from looking at the code. Some of them are
nits, use your own judgement.
a) The classes have no ASF license header.
I will address this under DERBY-4764 together with some other files.
--- AccessDatabase
b) Would it be better to use dbUrl.indexOf("://") to reduce the chance of
mistakenly using the client driver instead of the embedded driver?
Getting // can easily happen during scripting, for instance.
c) The code is suspectible to some forms of SQL injection attacks.
A quick look doesn't reveal anything severe, but in general it is wise to
use either prepared statements or to validate the user input (i.e.,
verify through meta data calls that the specified schema is indeed an
existing schema.
d) @author tags aren't used in the Derby code base
e) Seems to be the private final variables could be static as well (also,
many people prefer constants to be in upper case).
f) Is it correct to shut down the database?
I'd say not, and would be more comfortable either simply not doing it or
have an option for it.
--- PlanExporter
g) deleteFile is a potential security hole, allowing users to
delete Derby files at will (I don't know which permissions the tool is
running with by default). At the very least it should be made private,
since it is used only within that single class.
--- TreeNode
h) Class should be package-private.
i) All Java objects implicitly inherit java.lang.Object. Is there a reason why
it has been made explicit?
j) Use of new String() is discouraged.
k) Is thre a reason why depth is a String?
--- CreateHTMLF l) Missing class JavaDoc (short description of what it does).
m) For convenience, it may be better to convert the HTML file name to upper-
case and do endsWith(".HTML"). That way, you support mixed case as well.
--- CreateXMLFile
n) You may want to unwrap the PrivilegedActionException, casting it to
IOException (since that's the only checked exception that can occur).
It makes the API slightly cleaner.
o) I'm not sure what the community's take on the contents of the variable
"comment" is.
p) Has using a specific character encoding for the output file been discussed?
String.getBytes() uses the default encoding on the platform.
I'm going to take the tool for a spin as well, but decided to post my comments
so far right away. As you mentioned, the number of comments under this issue is
getting very large, it might be better to track follow-up work in a separate
Jira.
I'm excited to see if I can understand why one of the Derby tests fails with a
patch of mine by looking at the output of the PlanExporter. I just have to
hook it into the test itself, and then there's the problem that the Derby
JUnit framework (or rather SupportFilesSetup) deletes anything written to the
support files directories...
Thanks :)
> 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl,
> advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg,
> advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff,
> 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-9.1.diff,
> DERBY-4587-tool-9.2.diff, DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff,
> DERBY-4587-tool-9.5.diff, DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff,
> DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff,
> DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png,
> Derby_Query_Plan_Screen_Shot.jpg, PostgreSQL license.jpg, Read_Me.txt,
> screensh
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12896668#action_12896668 ] Kim Haase commented on DERBY-4587: -- Kathey, you could try applying the latest patch to DERBY-4758 -- you'll be sure to notice if there's missing or unclear information, and your feedback would be very valuable. > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff, > 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-9.1.diff, > DERBY-4587-tool-9.2.diff, DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, > DERBY-4587-tool-9.5.diff, DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12896661#action_12896661 ] Kathey Marsden commented on DERBY-4587: --- I am interested in giving this a try, but have not been following along during development. What is a good documentation entry point? > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff, > 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-9.1.diff, > DERBY-4587-tool-9.2.diff, DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, > DERBY-4587-tool-9.5.diff, DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12896025#action_12896025 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Hi Bryan, It seems that Hudson build is back to normal http://hudson.zones.apache.org/hudson/job/Derby-trunk/468/console > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff, > 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-9.1.diff, > DERBY-4587-tool-9.2.diff, DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, > DERBY-4587-tool-9.5.diff, DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12895936#action_12895936 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Thank Bryan, for committing I am hoping that Hudson build will pass now :) (We passed 100 comments in this jira :D) > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff, > 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-9.1.diff, > DERBY-4587-tool-9.2.diff, DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, > DERBY-4587-tool-9.5.diff, DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12895932#action_12895932 ] Bryan Pendleton commented on DERBY-4587: Committed the javadoc fixes to the trunk as revision 982856. > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-javadoc_fix.diff, > 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-9.1.diff, > DERBY-4587-tool-9.2.diff, DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, > DERBY-4587-tool-9.5.diff, DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12895929#action_12895929 ] Bryan Pendleton commented on DERBY-4587: Nirmal, can you provide a patch that is relative to the trunk, and just includes the javadoc fixes? The other changes in the 9.7 patch are already committed to the trunk. > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-9.1.diff, DERBY-4587-tool-9.2.diff, > DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, DERBY-4587-tool-9.5.diff, > DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12895926#action_12895926 ] Bryan Pendleton commented on DERBY-4587: Thanks Nirmal! I'm backed up with DERBY-4757 right now, then I'll take a look. > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-9.1.diff, DERBY-4587-tool-9.2.diff, > DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, DERBY-4587-tool-9.5.diff, > DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12895925#action_12895925 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Hi, Myrna, Thanks for your suggestion, I think the way you told is appropriate in this case and worked fine. Bryan, I've overwritten the patch 9.7 and attached it, I think this should solve the Hudson build failure, let's see. Thanks. > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-9.1.diff, DERBY-4587-tool-9.2.diff, > DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, DERBY-4587-tool-9.5.diff, > DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.7.diff, > DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12895910#action_12895910 ] Myrna van Lunteren commented on DERBY-4587: --- Perhaps add something like *planexporter*/*.xsl to tools/javadoc/derbydocs_exclusions.ant? Or perhaps you can just do **/*.xsl (only other .xsl is tools/ant/xsl/sysinfo_junitreport.xsl). disclaimer: I'm not certain of the syntax nor have I done any testing. > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-9.1.diff, DERBY-4587-tool-9.2.diff, > DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, DERBY-4587-tool-9.5.diff, > DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.diff, > DERBY-4587-tool-test1.diff, DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, > derby-logo.png, 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12895909#action_12895909 ] Bryan Pendleton commented on DERBY-4587: I'm not totally sure, but it looks like the Ant task can take some nested elements that tell it what to process. Maybe try something like: I'm not totally sure whether this is right; we probably need an element inside the to specify "org/apache/derby/**", and I'm not sure what should be the "dir=" part of Maybe you can try some things and see if you can get the "testingdocs" target to work? The target is in the top-level build.xml, I think. > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-9.1.diff, DERBY-4587-tool-9.2.diff, > DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, DERBY-4587-tool-9.5.diff, > DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.diff, > DERBY-4587-tool-test1.diff, DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, > derby-logo.png, 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12895908#action_12895908 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Hi Bryan, If the earlier errors were not the reason, is it something to do with this warning: [javadoc] /home/hudson/hudson-slave/workspace/Derby-trunk/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XplainStatisticsTest.java:621: warning - @param argument "s:" is not a parameter name. I have accidentally put a "colon" there after s. The final log at the end says following: javadoc: BUILD SUCCESSFUL Total time: 2 minutes 11 seconds [WARNINGS] Parsing warnings in console log... [WARNINGS] Using set difference to compute new warnings [WARNINGS] Found 1 new annotations (0 high, 1 normal, 0 low) [WARNINGS] Found 1 annotations (0 high, 1 normal, 0 low) [WARNINGS] Setting build status to FAILURE since total number of annotations exceeds the threshold 0 Sending e-mails to: [email protected] [email protected] Finished: FAILURE I'm not quite sure what is this annotation refers to? You have any idea? > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-9.1.diff, DERBY-4587-tool-9.2.diff, > DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, DERBY-4587-tool-9.5.diff, > DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.diff, > DERBY-4587-tool-test1.diff, DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, > derby-logo.png, 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12895904#action_12895904 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Hi Bryan, I think it's something to do with those three style sheets we've embedded. [javadoc] javadoc: error - Illegal package name: "/home/hudson/hudson-slave/workspace/Derby-trunk/trunk/java/tools/org/apache/derby/impl/tools/planexporter/resources/advancedViewXSL.xsl" [javadoc] javadoc: error - Illegal package name: "/home/hudson/hudson-slave/workspace/Derby-trunk/trunk/java/tools/org/apache/derby/impl/tools/planexporter/resources/advancedViewXSL2.xsl" [javadoc] javadoc: error - Illegal package name: "/home/hudson/hudson-slave/workspace/Derby-trunk/trunk/java/tools/org/apache/derby/impl/tools/planexporter/resources/vanilla_html.xsl" Is there a way to skip those style sheets? > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-9.1.diff, DERBY-4587-tool-9.2.diff, > DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, DERBY-4587-tool-9.5.diff, > DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.diff, > DERBY-4587-tool-test1.diff, DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, > derby-logo.png, 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12895896#action_12895896 ] Bryan Pendleton commented on DERBY-4587: Hi Nirmal, The automated Hudson build appears to be having problems with this patch. If you look at this screen: http://hudson.zones.apache.org/hudson/job/Derby-trunk/467/changes and then click on "Console output", you should be able to see the error messages from the build. Can you have a look and see if you can figure out what is wrong with the build? > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-9.1.diff, DERBY-4587-tool-9.2.diff, > DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, DERBY-4587-tool-9.5.diff, > DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.diff, > DERBY-4587-tool-test1.diff, DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, > derby-logo.png, 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12895705#action_12895705 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Hi Bryan, Thanks for committing this! Yeah, I think we should keep this issue open for sometime till we get some feedback on how it works. Thanks Bryan for all the help you provided, and for your wonderful thought about this kind of a tool. PS: Bryan don't you think that we should commit the documentation patch too? Thanks > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-9.1.diff, DERBY-4587-tool-9.2.diff, > DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, DERBY-4587-tool-9.5.diff, > DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.diff, > DERBY-4587-tool-test1.diff, DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, > derby-logo.png, 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12895697#action_12895697 ] Bryan Pendleton commented on DERBY-4587: I've committed the 9.7 patch to the Derby trunk as revision 982637. I haven't marked this issue resolved just yet, as I thought we would leave it open for a short while in case any issues arise during early use of the tool. Thanks for the contribution of this tool to the Derby community, Nirmal! I am hopeful that this tool provides an important basis for continued improvement in the query visualization tools for Derby. > 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: AdavancedXSL-mouseover.jpg, advancedViewXSL.xsl, > advancedViewXSL.xsl, advancedViewXSL2.xsl, advancedXSL-1.jpg, > advancedXSL-2.jpg, advancedXSL-3.jpg, 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-9.1.diff, DERBY-4587-tool-9.2.diff, > DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, DERBY-4587-tool-9.5.diff, > DERBY-4587-tool-9.6.diff, DERBY-4587-tool-9.7.diff, DERBY-4587-tool-9.diff, > DERBY-4587-tool-test1.diff, DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, > derby-logo.png, 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12892622#action_12892622 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Hi Bryan, Nice to hear that !! These days I'm into developing more advanced XSL style sheets using CSS and JS. I'll update the JIRA soon, with my findings! > 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-9.1.diff, DERBY-4587-tool-9.2.diff, > DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, DERBY-4587-tool-9.5.diff, > DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12892597#action_12892597 ] Bryan Pendleton commented on DERBY-4587: With the latest patch, I am able to build successfully with my normal build environment. When I run the XplainStatisticsTest with xalan.jar in my classpath, it appears to run the XML-variations of the tests. And when I run the test without xalan.jar in my classpath, it appears to skip the XML-variations of the tests, but runs the base tests successfully. So, at least in my environment, the latest patch appears to have resolved the build problems! > 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-9.1.diff, DERBY-4587-tool-9.2.diff, > DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, DERBY-4587-tool-9.5.diff, > DERBY-4587-tool-9.diff, DERBY-4587-tool-test1.diff, > DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, derby-logo.png, > 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12890229#action_12890229 ] Knut Anders Hatlen commented on DERBY-4587: --- Will XplainStatisticsTest run on non-Sun JVMs now that imports com.sun.org.apache.xpath.internal.domapi.XPathEvaluatorImpl? > 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-9.1.diff, DERBY-4587-tool-9.2.diff, > DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, DERBY-4587-tool-9.diff, > DERBY-4587-tool-test1.diff, DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, > derby-logo.png, 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12890172#action_12890172 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Hi Bryan, Whoops, yeah I should put "PlanExporter" instead "planexporter". You're right Bryan, I'm developing in Windows Vista, I'll update it in my environment too, thanks for pointing it out. > 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-9.1.diff, DERBY-4587-tool-9.2.diff, > DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, DERBY-4587-tool-9.diff, > DERBY-4587-tool-test1.diff, DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, > derby-logo.png, 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12890168#action_12890168 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Thanks Bryan, I'm glad that you were able to set up your environment. Anyway I'm looking into an old version of xalan, let's see whether it contains the required functionalities. Thanks. > 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-9.1.diff, DERBY-4587-tool-9.2.diff, > DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, DERBY-4587-tool-9.diff, > DERBY-4587-tool-test1.diff, DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, > derby-logo.png, 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12890167#action_12890167 ] Bryan Pendleton commented on DERBY-4587: To get derbytools.jar to build, I had to change tools/jar/tools.properties so it says derby.module.planexporter=org.apache.derby.tools.PlanExporter Nirmal, do you develop on Windows? I think maybe on Windows the file names are not case sensitive, but on my Ubuntu system the file name case has to match exactly. > 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-9.1.diff, DERBY-4587-tool-9.2.diff, > DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, DERBY-4587-tool-9.diff, > DERBY-4587-tool-test1.diff, DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, > derby-logo.png, 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12890140#action_12890140 ] Bryan Pendleton commented on DERBY-4587: I successfully built after I did the following: 1) Edited my ant.properties to remove j14lib and j15lib, leaving only jdk16 2) Removed my GCJ installation(s) from my machine, so that JDK 1.6 was the only JDK available to the build This confirms Nirmal's observation that the build is successful in a pure JDK 1.6 environment > 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-9.1.diff, DERBY-4587-tool-9.2.diff, > DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, DERBY-4587-tool-9.diff, > DERBY-4587-tool-test1.diff, DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, > derby-logo.png, 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12890125#action_12890125 ] Bryan Pendleton commented on DERBY-4587: Yes, you're right, it is there in my jdk 1.6 environment. I wonder if the problem is due to: Setting property java15compile.classpath to /usr/lib/jvm/java-1.5.0-gcj-4.4/jre/lib/rt.jar It seems that the build chose to use my GCJ version of 1.5, rather than my Sun version. I tried forcing j15lib to point to my jdk 1.6 JRE, but then I get JDBC interface compilation errors due to the differences between JDBC 3 and JDBC 4. The Derby build has become a tad complex of late, and I'm having a bit of confusion here, but I'll continue to investigate... > 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-9.1.diff, DERBY-4587-tool-9.2.diff, > DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, DERBY-4587-tool-9.diff, > DERBY-4587-tool-test1.diff, DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, > derby-logo.png, 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12889891#action_12889891 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Hi Bryan, If you look at your Jdk1.6 folder isn't it containing the file you mentioned above (jdk1.6.0\com\sun\org\apache\xpath\internal\domapi\XPathEvaluatorImpl.java)? In my jdk1.6 folder it's there. I wonder why it failed in your environment. > 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-9.1.diff, DERBY-4587-tool-9.2.diff, > DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, DERBY-4587-tool-9.diff, > DERBY-4587-tool-test1.diff, DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, > derby-logo.png, 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12889885#action_12889885 ] Bryan Pendleton commented on DERBY-4587: Hi Nirmal, I saw your note on derby-dev. Thanks for the investigation. I agree that if we can use an older version of xalan in our tests, that seems like a good solution. As one more data point, I tried removing the jdk14 and jdk15 definitions from my ant.properties, and now I get: /home/bpendleton/src/derby/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XplainStatisticsTest.java:63: package com.sun.org.apache.xpath.internal.domapi does not exist [javac] import com.sun.org.apache.xpath.internal.domapi.XPathEvaluatorImpl; So even with a pure jdk 1.6 configuration I am still having some build problems. > 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-9.1.diff, DERBY-4587-tool-9.2.diff, > DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, DERBY-4587-tool-9.diff, > DERBY-4587-tool-test1.diff, DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, > derby-logo.png, 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12889682#action_12889682 ] Bryan Pendleton commented on DERBY-4587: It doesn't seem to matter what I set my CLASSPATH to. My 'ant all' step always has this as its classpath for compiling the test code: '/home/bpendleton/src/derby/trunk/classes: /home/bpendleton/tools/j2sdk1.4.2_18/jre/lib/charsets.jar: /home/bpendleton/tools/j2sdk1.4.2_18/jre/lib/jce.jar: /home/bpendleton/tools/j2sdk1.4.2_18/jre/lib/jsse.jar: /home/bpendleton/tools/j2sdk1.4.2_18/jre/lib/plugin.jar: /home/bpendleton/tools/j2sdk1.4.2_18/jre/lib/rt.jar: /home/bpendleton/tools/j2sdk1.4.2_18/jre/lib/sunrsasign.jar: /home/bpendleton/src/derby/trunk/tools/java/junit.jar' I tried to hunt through the build.xml files a bit, and I could see that it looks like the xalan entry in the classpath is supposed to come from the extrapath.properties file, but I guess that file isn't getting read in my build environment for some reason? > 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-9.1.diff, DERBY-4587-tool-9.2.diff, > DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, DERBY-4587-tool-9.diff, > DERBY-4587-tool-test1.diff, DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, > derby-logo.png, 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12889638#action_12889638 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Hi Bryan, Seems like you're missing the Xalan.jar in your classpath. Can you please check whether you got that in your classpath. Meanwhile I'll look into that more deeply. Thanks. > 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-9.1.diff, DERBY-4587-tool-9.2.diff, > DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, DERBY-4587-tool-9.diff, > DERBY-4587-tool-test1.diff, DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, > derby-logo.png, 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12889636#action_12889636 ] Bryan Pendleton commented on DERBY-4587: Hi Nirmal, I'm having trouble getting the latest patch to build. I think I'm not getting xalan.jar into the classpath properly. Are there any special build instructions for this patch? What do you have your CLASSPATH set to when you build the patch? The errors I get are, e.g., [javac] /home/bpendleton/src/derby/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XplainStatisticsTest.java:2039: cannot find symbol [javac] symbol : variable XPathResult [javac] location: class org.apache.derbyTesting.functionTests.tests.lang.XplainStatisticsTest [javac] XPathResult.UNORDERED_NODE_ITERATOR_TYPE)); > 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-9.1.diff, DERBY-4587-tool-9.2.diff, > DERBY-4587-tool-9.3.diff, DERBY-4587-tool-9.4.diff, DERBY-4587-tool-9.diff, > DERBY-4587-tool-test1.diff, DERBY-4587-tool-test2.diff, DERBY-4587-tool.diff, > derby-logo.png, 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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12888654#action_12888654 ] Bryan Pendleton commented on DERBY-4587: Yes, at the outermost level of PlanExporter I think it is appropriate to catch (Exception); we don't need to name each specific type of exception there unless they would have different handling. In the test code, we generally don't catch exceptions at all. We generally just declare our test methods as throws Exception and let the exception be thrown out to JUnit, which will catch it and report it. The one exception (!) to this rule is for a test which is deliberately testing the throwing of an exception, in which case the test catches the exception (and, in fact, should call fail() if the exception is not thrown) So yes, it's fine to have the test methods declare that they throw exceptions, and the test code should only catch the exceptions that the test case is deliberately provoking with its testing. I thought we had written something about this in the Derby wiki, but I went searching under http://wiki.apache.org/db-derby/IntroToJUnit and I couldn't find it. Does anybody know if we wrote a wiki page describing these coding conventions for JUnit tests? If not, it would be great to have one. > 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-9.1.diff, DERBY-4587-tool-9.2.diff, > DERBY-4587-tool-9.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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12888389#action_12888389 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Bryan, What about try/catch blocks used in XplainStatisticsTest? The reason behind catching the exceptions there is if I throw an exception, many methods should be updated as they throws those exceptions. If it's ok, I'll throw exceptions instead of catching them at "XplainStatisticsTest" Thanks. > 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-9.1.diff, DERBY-4587-tool-9.2.diff, > DERBY-4587-tool-9.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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[
https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12888375#action_12888375
]
C.S. Nirmal J. Fernando commented on DERBY-4587:
Hi Bryan,
Thanks for reviewing it.
Is it ok to add only
catch(Exception e){ e.printStackTrace()},
at the very outermost main() method
rather than catching each exception by its
name i.e.
catch(SQLException e){ e.printStackTrace()},
etc.?
Thanks.
> 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-9.1.diff, DERBY-4587-tool-9.2.diff,
> DERBY-4587-tool-9.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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[
https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12888361#action_12888361
]
Bryan Pendleton commented on DERBY-4587:
Hi Nirmal,
I have a concern about the error-handling technique used in the patch.
In a number of places, particularly in AccessDatabase.java, but also in
some of the other classes, the code follows this pattern:
try {
... do something ...
} catch (SomeException e) {
e.printStackTrace();
}
I don't think this is the best pattern to use.
Instead, I think we should (a) remove the try/catch code from the method,
and (b) change the method's declaration to say "throws SomeException"
I suspect that, in general, the only try / catch block in the entire
PlanExporter
code should probably be at the very outermost main() method.
One exception to this is in the shutdown() method, where we do expect
to routinely catch and ignore the shutdown exception.
thanks,
bryan
> 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-9.1.diff, DERBY-4587-tool-9.2.diff,
> DERBY-4587-tool-9.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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12887252#action_12887252 ] Bryan Pendleton commented on DERBY-4587: Hi Nirmal, I don't understand the two new "assertEqual" methods in JDBC.java. How are they different from the assertEquals() methods already present in junit.framework.Test? > 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-9.1.diff, DERBY-4587-tool-9.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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12885776#action_12885776 ] Bryan Pendleton commented on DERBY-4587: Yes, we should try to use xalan for our XPath execution support. I'm pretty sure that we have other regression tests (the XML tests) that use XPath in them, for example XMLTypeAndOpsTest does some XPath-testing I believe. You can find a little bit of information about xalan and Derby here: http://db.apache.org/derby/docs/10.6/devguide/cdevstandardsxml.html Our utility test methods should definitely be put in a common superclass so that we can share and re-use them over time as we develop more tests. Ideally, it would be nice if our tests could dynamically detect whether the xalan classes were available, and, if they aren't, then we just skip the XPath part of the tests. I think that the XML code in the Derby engine has some logic for doing this that we can examine to see how it works. > 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12885584#action_12885584 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Hi Bryan, I think this way would be enough to support solid regression tests. Is it possible to use the XML libraries such as xalan.jar to execute XPath queries? or do I need to implement those XMLTagCount(..) etc. methods? If I'm not mistaken, I have to create assertEquals(...) in JDBC.java, isn't it? Thanks. > 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[
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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12885043#action_12885043 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Whoops! I forgot to remove the "id", sorry about that. > 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12884286#action_12884286 ] Bryan Pendleton commented on DERBY-4587: Similarly to scans, there is also data in SYSXPLAIN_SORT_PROPS in certain cases, and it would be great if we could get that data into the XML document. For example, GROUPBY nodes usually have associated SORT_PROPS rows. For a simple example of a statement which has both SCAN_PROPS and SORT_PROPS data in it, see testGroupBySortProps() in XplainStatisticsTest.java > 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.jpg, basic_html-3.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-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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12883170#action_12883170 ] Bryan Pendleton commented on DERBY-4587: I think we could go either way. My first reaction was to desire the data only for scan-type nodes, but I could see an argument for having the tags always present, but be empty for non-scan nodes. In general, my experience with XML is that optional data is implemented using optional tags, so I think most users would expect to see the tags appear only for scan-related nodes. > 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.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.diff, DERBY-4587-tool-test1.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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12883013#action_12883013 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Hi Bryan, You want to appear those values, in all nodes or only in "scan" related nodes? Thanks > 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.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.diff, DERBY-4587-tool-test1.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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12882969#action_12882969 ] Bryan Pendleton commented on DERBY-4587: Good, I see all four nodes now. Thanks! For result sets of "scan" table, like TABLESCAN or HASHSCAN, it would be useful to include the information from the SYSXPLAIN_SCAN_PROPS row. In particular, it would be nice for the SCAN nodes to identify what table was being scanned; I think that information is in the SCAN_OBJECT_NAME column of the corresponding SYSXPLAIN_SCAN_PROPS row. At the top of this page, there is a sample query that shows a little bit about how to join between SYSXPLAIN_RESULTSETS and SYSXPLAIN_SCAN_PROPS: http://db.apache.org/derby/docs/10.6/ref/rrefsysxplain_scan_props.html > 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.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.diff, DERBY-4587-tool-test1.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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12882967#action_12882967 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Hi Bryan, There're 4 nodes, and all 4 boxes are displaying, only thing is it's too long, so in the screen shot latter part is not visible. I'll attach another screen shot. > 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.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.diff, > DERBY-4587-tool-test1.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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12882966#action_12882966 ] Bryan Pendleton commented on DERBY-4587: In basic_html-2, are there 4 nodes or 3? I see 3 "boxes" in the screen shot, but it looks like there are 4 node names listed (PROJECTION,LOHASHJOIN, TABLESCAN,HASHSCAN). Is there a missing box for "HASHSCAN" node?) > 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.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.diff, > DERBY-4587-tool-test1.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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12882858#action_12882858 ] Bryan Pendleton commented on DERBY-4587: Hi Nirmal I'm having several small problems working with the patch: 1) In my build environment, String.contains() is not available, since I'm building with JDK 1.4, not JDK 1.5. Perhaps we could change the contains() calls in AccessDatabase.java to use indexOf() instead? 2) It appears that the new code is not getting added to derbytools.jar. Perhaps a build file adjustment is needed to enroll the new classes in derbytools.jar? 3) When I run XplainStatisticsTest, I get AccessDenied exceptions trying to write the XML files. I'm not sure exactly where the test is trying to write these test files, but it seems the location is not acceptable to the security policy that is getting used by the tests. When I run the test using the 'classes' directory in my classpath, problems (2) and (3) do not arise. I took a quick look at the generated XML files in the output, and they are starting to look very good! Can we include the STATEMENT_TEXT data into the generated XML output, so that we can see the actual statement that is getting run, as part of the XML file? > 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-5.diff, DERBY-4587-tool-6.diff, DERBY-4587-tool-test1.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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12882606#action_12882606 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Forgot to mention that I ran the junit test for "org.apache.derbyTesting.functionTests.tests.lang.XplainStatisticsTest.java" without errors. > 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-5.diff, DERBY-4587-tool-6.diff, DERBY-4587-tool-test1.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, 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12882429#action_12882429 ] Bryan Pendleton commented on DERBY-4587: The new XML document screenshot looks very good. > 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-5.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, > 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12881511#action_12881511 ] Bryan Pendleton commented on DERBY-4587: Yes, this seems to be making great progress! The XSL stylesheet is quite clean, and the XML source document seems much more full-featured and complete. I think that the and tags shouldn't be in the base XML document, but rather should be added by the presentation layer (XSL or JavaScript libraries, etc.) And I'd like to see 4 rather than # of Rows Returned= 4 and the same for tag. > 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-5.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 > > > 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12880513#action_12880513 ] Bryan Pendleton commented on DERBY-4587: Hi Nirmal, I think the screen shots are helpful and clear, thanks for attaching them! Regarding the XSL file itself, it seems to be coming along well. I'm a little uncertain about whether we should be embedding the dynamic HTML JavaScript functionality directly into the XSL. Rather than building our own JavaScript for making the page be interactive, could we build upon some of the existing JavaScript libraries that are already out there? I'm wondering whether the XSL style sheet should concentrate on simply formatting the XML query plan data into a simple and clear HTML document for basic display in a browser, and then separately we could provide a small JavaScript file which uses a library such as JQuery to provide dynamic browser behaviors. I think the overall approach should be to build a collection of small, simple, and focused tools, which a motivated Derby user can combine in a variety of ways: - export tool extracts Derby XPLAIN data and formats it into clear XML, including all the relevant data from the XPLAIN tables for that query - XSL stylesheet formats the XML into simple vanilla HTML, which can be viewed in a browser but is not anything fancy. Probably, this HTML is mostly just dictionary lists (DL tags) nested within each other to reflect the nested structure of the query plan, with the query plan data provided in the list contents as tag and data items to encode the name=value type information from the query nodes - CSS and JS resources can add dynamic behaviors to the HTML page to improve comprehension. For example, your expand/collapse treeview, but there are many other possibilities out there we could consider So if the XSL stylesheet simply formatted the query plan data into something like node typeHASHSCAN no_opens4 returned_rows4 visited_pages1 scan_qualifiersNone I think that would be very solid, and then we could work on the formatting and dynamic behaviors separately. > 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, screenshot-1.jpg, screenshot-2.jpg, > screenshot-3.jpg, Source.rar, test.xml, test4.xsl > > > 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12879045#action_12879045 ] Rick Hillegas commented on DERBY-4587: -- Thanks, Nirmal. These look good. In particular, I like seeing the ability to collapse and expand nodes. I was a little puzzled by the lack of a top level level, root node. Ultimately, I would expect that at run-time, the tuples would flow out of a single root node at the top. This visualization is an improvement over what you can do with xml documents in Firefox today: 1) The visualization looks cleaner--it doesn't have the distracting xml tags mixed in with it 2) Probably this approach will work in a broader range of browsers besides Firefox. I was wondering about how hard a different visualization would be: Instead of a structure which looks like a folder tree, how about something which looks more like a left-deep tree? That's what Derby query plans are today. Extra credit if you could expand and collapse a single node without forcing everthing to the left to expand and collapse at the same time. Thanks! -Rick > 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, screenshot-1.jpg, screenshot-2.jpg, Source.rar, > test.xml, test4.xsl > > > 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[
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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[
https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12876689#action_12876689
]
C.S. Nirmal J. Fernando commented on DERBY-4587:
Ya, Bryan, it would be fine.
I think you meant adding something like:
/**
*
* @param aConn
* @param aSchema
* @param aQuery
*/
public AccessDatabase(Connection aConn, String aSchema, String aQuery) {
conn = aConn;
schema = aSchema;
query = aQuery;
}
while keeping my original constructor.
> 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12876551#action_12876551 ] Bryan Pendleton commented on DERBY-4587: I've been thinking a little bit about testing. I was wondering whether we could change the AccessDatabase class a little bit so that there is an alternate constructor which allows a Connection to be passed in. Then, it would be possible to extend the XplainStatisticsTest so that it invokes the AccessDatabase class to emit the XML format of the various captured query plans in each of the test cases, as we ran them. What would you think about adding an alternate AccessDatabase constructor which took a Connection rather than a jdbcUrl? > 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12873472#action_12873472 ] Bryan Pendleton commented on DERBY-4587: Hi Nirmal, It seems that the current XML document built by the v3 patch is capturing all of the result set nodes, but it is "flattening" them into a simple list, which loses some of the information that is present in the query plan. I think that we will want to build a richer data structure in the AccessDatabase class, in order to capture the the natural tree structure of a query plan. Entries in a query plan are structured into parent-child relationships, (there's a nice writeup on the basic concepts here: http://en.wikipedia.org/wiki/Query_optimizer) and we need to be able to capture that structure into the natural tree structure of the resulting XML document. For example, consider this simple query: create table t1 (c1 int, c2 varchar(100)); create table t2 (a int, b int); select t1.c1, t1,c2 from t1 inner join t2 on c1 = a; This query is executed using a 3-level query tree, which can be seen by looking at the RS_ID and PARENT_RS_ID columns in SYSXPLAN_RESULTSETS: ij> select rs_id, op_identifier, parent_rs_id from s2.sysxplain_resultsets; RS_ID |OP_IDENTIFIER |PARENT_RS_ID 2589c06b-0128-ea8c-c9a5-00e76260|PROJECTION|NULL addac06e-0128-ea8c-c9a5-00e76260|HASHJOIN |2589c06b-0128-ea8c-c9a5-0e76260 862e0071-0128-ea8c-c9a5-00e76260|TABLESCAN |addac06e-0128-ea8c-c9a5-00e76260 5e838074-0128-ea8c-c9a5-00e76260|HASHSCAN |addac06e-0128-ea8c-c9a5-00e76260 The PROJECTION node is the "root" of the tree, and it has 1 child, the HASHJOIN node, which in turn has two children, the TABLESCAN and HASHSCAN nodes. As a tree, it looks something like: PROJECTION | HASHJOIN | +--+--+ || TABLESCAN HASHSCAN When we format this query into an XML document, the tree structure needs to be preserved, so that we end up with an XML document which expresses the tree structure using nesting of XML nodes, so that we get something like: PROJECTION HASHJOIN TABLESCAN HASHSCAN I think that the tool will need a more sophisticated method for analyzing the contents of the SYSXPLAIN tables in order to deduce this query plan structure from the PARENT_RS_ID values. One idea is to use some of Java's built-in tree-structured collections classes, such as a java.util.TreeMap, to retrieve the data from the SYSXPLAIN tables, then after the data has been retrieved, traverse the TreeMap in order to emit the XML document with the structure expressed as XML node containment. Does this make sense? > 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.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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12873354#action_12873354 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Hi Bryan, Thanks. 1) I'll look into this. Thanks for pointing it out. 2) I'm sorry about that, I've submitted a new patch. 3) I checked for a query like "select t.* from (tt left outer join t on tt.j = t.x)" it worked fine [0]. NOTE: If some XPLAIN table returns 0 rows, this will not emit that XML tag. 4) Did you mean to catch them inside the ExportQueryPlanAsXML.main method? or just throw the error everywhere? 5) Whoops, I thought to do that, but somehow forgot it. I've updated it in the new patch. 6) I'm afraid I hadn't. But while reading your suggestion, a question popped up in my mind, whether we can/Is it better to, invoke a new tool after closing an ij connection, inside a same regression test? (Just thought) Thanks for reviewing the patch this quickly. [0] The XML file generated for the query "select t.* from (tt left outer join t on tt.j = t.x)": PROJECTION 1 4 LOHASHJOIN 1 4 − TABLESCAN 1 4 1 None HASHSCAN 4 4 1 None Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false > 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.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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12873334#action_12873334 ] Bryan Pendleton commented on DERBY-4587: I successfully applied and built the test, and ran the XML generation tool (once). Great work! This is very exciting! Some first comments: 1) I had some trouble running the tool, until I realized that the tool was always loading the ClientDriver, and I was trying to create an embedded connection. I think we want to support both client-style URLs and embedded-style URLs, so we need a way to detect which connection URL is provided, and load the appropriate driver. 2) I had to apply the patch from the 'java' directory; it is more conventional to create patches to be applied from the 'trunk' directory. 3) I just tried the generation tool with a very simple query in my XPLAIN tables, and it seemed to work. If I get a chance, I'll try to run it against some other XPLAIN output. 4) I'm not sure that it is helpful for the AccessDatabase class to be catching the SQLException errors; it might be better to declare those methods as throwing the exceptions and let the exceptions propagate out. 5) I found the code in createXMLFragment() a bit hard to read; perhaps it would be a bit more legible if the long lines were broken up into multiple shorter lines? 6) Have you had a chance to think about how we might go about building a regression test suite for the new tool? One possibility would be to extend the current XplainStatisticsTest so that, in addition to generating XPLAIN data for various query plans, that test also calls your new tool to format the data as XML, then checks the XML documents for correctness. > 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.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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12868008#action_12868008 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Hi All, I think it's time to get finalize the design or the process view of the implementation. We highly appreciate comments on this design from you. I have created a small wiki page mentioning the design we planned. The link to the wiki page is http://wiki.apache.org/db-derby/Nirmal/Derby-4587-%20Design%20Details Thanks in advance. > 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_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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12859866#action_12859866 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Hi Knut, Thanks for the suggestion, it worked fine. > 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: Bryan Pendleton > Attachments: Derby Query Plan Screen Shot 2.jpg, > 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12859829#action_12859829 ] Knut Anders Hatlen commented on DERBY-4587: --- Hi Nirmal, The extra blank space comes from the way the CHAR function converts integers to strings. See the paragraph with the title "Integer to character syntax" in the link I posted above. If you want to get rid of the blanks, I think you can use the TRIM function: TRIM(CHAR(no_opens)) http://db.apache.org/derby/docs/10.5/ref/rreftrimfunc.html > 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: Bryan Pendleton > Attachments: Derby Query Plan Screen Shot 2.jpg, > 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12859351#action_12859351 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Thanks Knut. That gave me the expected result. But I wonder why it left blank space between the value and the closing tag (This won't be an affect in this case, just curious though). Thanks Bryan for the reply but we cannot convert numeric type to varchar. Any way Knut's suggestion worked fine so we can use , SELECT ''|| ''|| CHAR (op_identifier) ||''|| ''||CHAR(no_opens)||''|| ... repeat for the other columsn from sysxplain_resultsets... '' FROM xxx.sysxplain_resultsets WHERE ... this way to produce small XML fragments, and then stitch them to an .xml file. (of course we need to add XML related lines up front) > 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: Bryan Pendleton > Attachments: Derby Query Plan Screen Shot 2.jpg, > 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12859255#action_12859255 ] Knut Anders Hatlen commented on DERBY-4587: --- I don't think you can cast a numeric type to a character type. But there is a CHAR function (http://db.apache.org/derby/docs/10.5/ref/rrefbuiltchar.html) that converts various data types to CHAR. So for example: ... || '' || CHAR(no_opens) || '' || ... > 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: Bryan Pendleton > Attachments: Derby Query Plan Screen Shot 2.jpg, > 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12859162#action_12859162 ] Bryan Pendleton commented on DERBY-4587: Can you use CAST (column AS VARCHAR(20)) to convert the numeric datatypes to strings? > 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: Bryan Pendleton > Attachments: Derby Query Plan Screen Shot 2.jpg, > 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12858968#action_12858968 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Hi Bryan, Thanks for the reply. I think this approach looks fine. I checked for some queries and successful in VARCHAR, but not for INTEGER, DOUBLE etc. May be '||' is support only for VARCHAR, frankly I do not know about using '||', if you can guide me to a resource to read on '||', that would be nice. If the case is that, is there a way that we can convert other data types to varchar? Thanks. > 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: Bryan Pendleton > Attachments: Derby Query Plan Screen Shot 2.jpg, > 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.
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12858301#action_12858301 ] Bryan Pendleton commented on DERBY-4587: I think that we should avoid changing the schema of the XPLAIN tables. For the time being, can we pursue an implementation where the XML generation is done in the client side of the tool, perhaps something like: SELECT ''|| ''||op_identifier||''|| ''||no_opens||''|| ... repeat for the other columsn from sysxplain_resultsets... '' FROM xxx.sysxplain_resultsets WHERE ... Would an approach like this work to generate the XML format of the query plan data? > 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: Bryan Pendleton > Attachments: Derby Query Plan Screen Shot 2.jpg, > 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. - If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
Re: [jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
http://www.castor.org/xml-framework.html to the rescue! 2010/4/18 C.S. Nirmal J. Fernando (JIRA) : > >[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12858291#action_12858291] > > C.S. Nirmal J. Fernando commented on DERBY-4587: > > > Hi Bryan, > > I had a look on using XML operations in Derby more thoroughly and found out following important points: > > * In order to retrieve XML data using XMLSERIALIZE, we should first have a table column which is in XML data type. > > So is it possible to change SYSXPLAIN tables such that they store XML data of each row inserted, in a XML data type column? > > * When inserting XML data we have to insert some set of XML keywords, such as Document, Preserve Whitespace. > > eg: insert into t(a) values (XMLPARSE(DOCUMENT 'Derby' PRESERVE WHITESPACE)); > > * When using XMLSERIALIZE we should use 'as' sql key word, > >eg: select XMLSERIALIZE(a as CLOB) from t; //we can use varchar as well > > * Execution of XMLSERIALIZE statement will result a small XML fragment. > >eg: Derby > > I think this will be an easy way, if we can somehow add a XML data type columns to SYSXPLAIN tables. > > May be we can store these small xml fragments in another table with XML data type columns and retrieve the large XML document after the execution of the query. (Note: We may have to write those rows retrieved into a .xml file) > > Thanks. > > > > > >> 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: Bryan Pendleton >> Attachments: Derby Query Plan Screen Shot 2.jpg, 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. > - > If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa > - > For more information on JIRA, see: http://www.atlassian.com/software/jira > > >
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12858291#action_12858291 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Hi Bryan, I had a look on using XML operations in Derby more thoroughly and found out following important points: * In order to retrieve XML data using XMLSERIALIZE, we should first have a table column which is in XML data type. So is it possible to change SYSXPLAIN tables such that they store XML data of each row inserted, in a XML data type column? * When inserting XML data we have to insert some set of XML keywords, such as Document, Preserve Whitespace. eg: insert into t(a) values (XMLPARSE(DOCUMENT 'Derby' PRESERVE WHITESPACE)); * When using XMLSERIALIZE we should use 'as' sql key word, eg: select XMLSERIALIZE(a as CLOB) from t; //we can use varchar as well * Execution of XMLSERIALIZE statement will result a small XML fragment. eg: Derby I think this will be an easy way, if we can somehow add a XML data type columns to SYSXPLAIN tables. May be we can store these small xml fragments in another table with XML data type columns and retrieve the large XML document after the execution of the query. (Note: We may have to write those rows retrieved into a .xml file) Thanks. > 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: Bryan Pendleton > Attachments: Derby Query Plan Screen Shot 2.jpg, > 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. - If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12857821#action_12857821 ] Bryan Pendleton commented on DERBY-4587: Hi! I think you are interpreting the XPLAIN table information accurately. You can find additional information about the tables and columns of the XPLAIN tables here: http://db.apache.org/derby/docs/dev/tuning/ctun_xplain_tables.html http://db.apache.org/derby/docs/dev/ref/rrefsysxplain_statements.html http://db.apache.org/derby/docs/dev/ref/rrefsysxplain_statement_timings.html http://db.apache.org/derby/docs/dev/ref/rrefsysxplain_resultsets.html http://db.apache.org/derby/docs/dev/ref/rrefsysxplain_resultset_timings.html http://db.apache.org/derby/docs/dev/ref/rrefsysxplain_scan_props.html http://db.apache.org/derby/docs/dev/ref/rrefsysxplain_sort_props.html > the query which tries to retrieve XPLAIN tables itself, is also stored in > the XPLAIN tables again Yes, this is expected behavior. To stop collecting XPLAIN information, call the SET_XPLAIN_SHCEMA system procedure again, with an empty argument: http://db.apache.org/derby/docs/dev/ref/rref_syscs_set_xplain_schema.html > 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: Bryan Pendleton > Attachments: Derby Query Plan Screen Shot 2.jpg, > 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. - If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12857506#action_12857506 ] A.S.Thiwanka Somasiri commented on DERBY-4587: -- Hi Bryan, I just tried to figure out how the "sysxplain_resultsets" table behaves.I sent these SQL statements and checked the output. 1. ij>select * from cities where country='Sri Lanka' order by city_name; 2. ij> select stmt_text, xplain_time from MY_STATS.sysxplain_statements order by xplain_time; 3. ij> select st.stmt_text, rs.op_identifier > from my_stats.sysxplain_statements st > join my_stats.sysxplain_resultsets rs > on st.stmt_id = rs.stmt_id; Then I sent the 3rd query again to the database and the output was : STMT_TEXT |OP_IDENTIFIER --- select * from cities where country='Sri Lanka' order by city_name |SORT select * from cities where country='Sri Lanka' order by city_name |TABLESCAN select stmt_text, xplain_time from MY_STATS.sysxplain_statements order by xplain_time |SORT select stmt_text, xplain_time from MY_STATS.sysxplain_statements order by xplain_time |TABLESCAN select st.stmt_text, rs.op_identifier from my_stats.sysxplain_statements st join my_stats.sysxplain_resultsets rs on st.stmt& |PROJECTION select st.stmt_text, rs.op_identifier from my_stats.sysxplain_statements st join my_stats.sysxplain_resultsets rs on st.stmt& |HASHJOIN select st.stmt_text, rs.op_identifier from my_stats.sysxplain_statements st join my_stats.sysxplain_resultsets rs on st.stmt& |TABLESCAN select st.stmt_text, rs.op_identifier from my_stats.sysxplain_statements st join my_stats.sysxplain_resultsets rs on st.stmt& |HASHSCAN 8 rows selected ___END OF THE OUTPUT___ (The third query was sent twice to convince you that the query which tries to retrieve XPLAIN tables itself, is also stored in the XPLAIN tables again) This clearly figures out the what type of result sets are generated when running a SQL statement.Here the OP_IDENTIFIER indicates the result set type. Let's take the first query "select * from cities where country='Sri Lanka' order by city_name;". This has the result sets TABLESCAN and SORT. Since the first result set performs a scan on the table, the SCAN_RS_ID column in SYSXPLAIN_RESULTSETS can identify the particular row in SYSXPLAIN_SCAN statistics related to the scan behavior. Similarly, SORT statistics can also be retrieved. Hope your comments on this. Thanks. > 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: Bryan Pendleton > Attachments: Derby Query Plan Screen Shot 2.jpg, > 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 featu
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12857484#action_12857484 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Hi Bryan, Here I quote from Derby developer's guide: XML data types and operators Derby supports the XML data type and a set of operators that work with the XML data type, but does not provide JDBC support for the XML data type. The XML data type and operators are based on a small subset of the SQL/XML specification. The XML data type and operators are defined only in the SQL layer. There is no JDBC-side support for XML data types. It is not possible to bind directly into an XML value or to retrieve an XML value directly from a result set. Instead, you must bind and retrieve the XML data as Java strings or character streams by explicitly specifying the appropriate XML operator as part of the SQL statements: • Use the XMLPARSE operator for binding data into XML values. • Use the XMLSERIALIZE operator to retrieve XML values from a result set. Additionally, there is no JDBC metadata support for the XML data type. The XML data type is not allowed in any of the clauses or operations that are described in the section on expressions on LONG data types in Derby and standards. For the XML operators to work properly, Derby requires that a JAXP parser, such as Apache Xerces, and Apache Xalan are included in the Java classpath. If either the parser or Xalan are missing from the classpath, Derby disallows any XML-related operations. Classpath and version issues Most Java Virtual Machines (JVMs) that are version 1.4 or later have a JAXP parser embedded in the JVM. If you are using one of these JVMs, you may not need to add any classes to your classpath. Some exceptions exist: • In most version 1.4.2 JVMs, the version of Xalan that comes with the JVM is not new enough, so you must override the version of Xalan in the JVM with a newer version by using the Endorsed Standards Override Mechanism described at http://java.sun.com/j2se/1.4.2/docs/guide/standards/. To use this mechanism, download and install a binary distribution of Xalan from Apache and set the system property java.endorsed.dirs to point to the Xalan installation directory. • After JVM version 1.4, Sun renamed the JAXP packages. Derby cannot find these renamed packages. If you are using a Sun JVM later than version 1.4, download and install a binary distribution of Xalan from Apache and place the xalan.jar file in your classpath. The xalan.jar file automatically puts into the classpath the other required jars that are in the same directory. === As you can see this needed XALAN.jar inserted into the CLASSPATH. >Each time we ran a statement such as SELECT XMLSERIALIZE(c) FROM xpl >we'd get a little "XML fragment", is that right? Or would get get an entire >XML document at that point? I think it's a XML fragment not a whole document. Will look for further details. I'll try to come up with a pseudo code in coming days, I think it's better if we can first come to a conclusion on using XMLSERIALIZE considering its usability, I'll read up more on this regard. Thanks > 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: Bryan Pendleton > Attachments: Derby Query Plan Screen Shot 2.jpg, > 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. - If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12857326#action_12857326 ] Bryan Pendleton commented on DERBY-4587: Thanks Nirmal! It seems like there is general consensus that this technique can be used to build a useful tool. I had originally anticipated that the data would be fetched from the XPLAIN tables as simple Java data types, then emitted as a single XML document by the tool, but I think your suggestion to use XMLSERIALIZE is intriguing. Each time we ran a statement such as SELECT XMLSERIALIZE(c) FROM xpl we'd get a little "XML fragment", is that right? Or would get get an entire XML document at that point? What would be needed to "stitch together" these XML fragments, or collection of smaller XML documents, into a single larger XML document representing the entire query plan in its XML-formatted form? That is, in pseudocode-outline, what might that process look like in the tool code? > 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: Bryan Pendleton > Attachments: Derby Query Plan Screen Shot 2.jpg, > 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. - If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12857316#action_12857316 ] C.S. Nirmal J. Fernando commented on DERBY-4587: Hi Bryan, *Derby XPLAIN tables are clearly described in http://db.apache.org/derby/docs/dev/tuning/ctun_xplain_tables.html and I had gone through it weeks ago. *And as we discussed way before and as I included in my GSoC proposal project can be sub divided into two, which I planned to implement in the two phases of GSoC-2010. These are the sub parts: 1) Build a tool (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. This involves specifying the XML schema for the data, writing the program to produce the data, and building tests for it. 2) Build a tool to format the XML-formatted data into visual information in a browser, using an XSLT stylesheet. This involves conceptualizing the visual display, designing and writing the XSLT stylesheet, and building tests for it. *In the phase 1 I have to build a small tool to get values of XPLAIN tables and emit XML data, but because none of the JDBC-side support for SQL/XML is implemented in Derby, it is not possible to bind directly into an XML value or to retrieve an XML value directly from a result set using JDBC. Instead, we must bind and retrieve the XML data as Java strings or character streams by explicitly specifying the appropriate XML operators, XMLPARSE and XMLSERIALIZE, as part of our SQL queries. To retrieve XML values from a Derby database using JDBC, we can use the XMLSERIALIZE operator in the SQL query. eg: SELECT XMLSERIALIZE (stmt_text as CLOB) FROM myschema.sysxplain_statements for more information we can refer http://db.apache.org/derby/docs/10.5/ref/rrefsqljtypexml.html **I did not put any comments here other than this after the GSoC application deadline is simply because I thought it will be unfair to other applicants since this is a some kind of a competition. I hope to contribute for this project in this summer and also for the evolution of Derby in future. Thanks > 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: Bryan Pendleton > Attachments: Derby Query Plan Screen Shot 2.jpg, > 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. - If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12857244#action_12857244 ] A.S.Thiwanka Somasiri commented on DERBY-4587: -- Hi Bryan, As I found,here are the tables that have all the statistics information for our need. 1. SYSXPLAIN_STATEMENTS 2. SYSXPLAIN_STATEMENT_TIMINGS 3. SYSXPLAIN_RESULTSETS 4. SYSXPLAIN_RESULTSET_TIMINGS 5. SYSXPLAIN_SCAN_PROPS 6. SYSXPLAIN_SORT_PROPS Earlier we had an issue on "how to filter the result set nodes(or else the overall tree structure) to show in a graphical view". The SYSXPLAIN_RESULTSETS table captures the information about each result set which is a part of the statement. So we can grab the information about the result set nodes through this table and the timing related statistics through the SYSXPLAIN_RESULTSET_TIMINGS table and so on. For example if a statement(query) is used to access a database, the result set information are stored in the SYSXPLAIN_RESULTSETS table.Single statement may have more than one result set node. In such a case we can join the table 1 and table 3 to get the whole set of result sets for the statement executed. Like this we have to traverse through all these tables to get the values that we need to view the execution plan in the browser window. We can do all these activities through a Java Program and save them through setter methods to a certain Java Object, which has attributes for all the statistics in the above tables.These statistics are same as statistics information in the logQueryPlan. Additionally, in the Java Program,we should have a method to iterate through the result set nodes that we grab from the SYSXPLAIN_RESULTSETS table.Then only we can generate the XML format which maps with each result set node.(The XML should be separated, so that it describes each result set node.)Then we can use XSLT to convert XML to XHTML,etc. The other important milestone is to generate the XML from the above mentioned Java Object which owns the statistics information.I have a suggestion to use open source XStream for this task.I am not 100% percent sure whether it is possible.At least we can try it out. Thank you. > 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: Bryan Pendleton > Attachments: Derby Query Plan Screen Shot 2.jpg, > 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. - If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
