db-derby-dev mailing list archives

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

    [ 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

  <dl>
  <dt>node type</dt><dd>HASHSCAN</dd> 
<dt>no_opens</dt><dd>4</dd> 
<dt>returned_rows</dt><dd>4</dd> 
<dt>visited_pages</dt><dd>1</dd> 
<dt>scan_qualifiers</dt><dd>None</dd> 
</dl>

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.


Mime
View raw message