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] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
Date Sun, 30 May 2010 01:17:36 GMT

    [ 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)":

<!--
 Designed & coded by C.S.Nirmal J. Fernando, of University of Moratuwa, Sri Lanka, to
prototype Apache Derby Query Explainer
-->

<plan>

<details>
<node>PROJECTION</node>
<no_opens>1</no_opens>
<returned_rows>4</returned_rows>
</details>

<details>
<node>LOHASHJOIN</node>
<no_opens>1</no_opens>
<returned_rows>4</returned_rows>
</details>
−
<details>
<node>TABLESCAN</node>
<no_opens>1</no_opens>
<returned_rows>4</returned_rows>
<visited_pages>1</visited_pages>
<scan_qualifiers>None</scan_qualifiers>
</details>

<details>
<node>HASHSCAN</node>
<no_opens>4</no_opens>
<returned_rows>4</returned_rows>
<visited_pages>1</visited_pages>
<scan_qualifiers>None</scan_qualifiers>
<next_qualifiers>
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
</next_qualifiers>
</details>

</plan>



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


Mime
View raw message