db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "C.S. Nirmal J. Fernando (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
Date Tue, 22 Jun 2010 16:16:54 GMT

     [ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

C.S. Nirmal J. Fernando updated DERBY-4587:
-------------------------------------------

    Attachment: DERBY-4587-tool-5.diff

Hi All,

This patch (DERBY-4587-tool-5.diff) is providing the functionality of emitting a more detailed
"XML file" using the new tool. As an example, following is a part extracted after generating
a XML file, using this patch:

<plan>
	<details>
      <image>projection.jpg</image>
      <node>PROJECTION</node>
      <id>29d8c03e-0128-e492-0a47-00000035b7e8</id>
      <depth>0</depth> 
      <parent_id>null</parent_id>
      <input_rows> null</input_rows>
      <returned_rows> # of Rows Returned= 4</returned_rows>
      <no_opens> # of Opens= 1</no_opens>
      <visited_pages> null</visited_pages>
      <scan_qualifiers> null</scan_qualifiers>
      <next_qualifiers> null</next_qualifiers>
	</details>
	<details>
      <arrow>arrow.jpg</arrow>
	   <image>lohashjoin.jpg</image>
      <node>LOHASHJOIN</node>
      <id>8a184042-0128-e492-0a47-00000035b7e8</id>
      <depth>1</depth>
      <parent_id>29d8c03e-0128-e492-0a47-00000035b7e8</parent_id>
      <input_rows> null</input_rows>
      <returned_rows> # of Rows Returned= 4</returned_rows>
      <no_opens> # of Opens= 1</no_opens>
      <visited_pages> null</visited_pages>
      <scan_qualifiers> null</scan_qualifiers>
      <next_qualifiers> null</next_qualifiers>
	</details>
.................................................................................
...........................................


You can start the new tool like this: 

C:\OtherNirmal\GSoC\Code\jars\sane>java org.apache.derby.tools.ExportQueryPlanAsXML jdbc:derby:nirmal
username password MYSCHEMA b9810038-0128-e492-0a47-00000035b7e8 name_of_the_xml_file 

MYSCHEMA - schema you specified when taking statistics 
b9810038-0128-e492-0a47-00000035b7e8 - is the STMT_ID in SYSXPLAIN_STATEMENTS table 

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: 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,
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.


Mime
View raw message