Return-Path: 
 <derby-dev-return-78631-apmail-db-derby-dev-archive=db.apache.org@db.apache.org>
Delivered-To: apmail-db-derby-dev-archive@www.apache.org
Received: (qmail 97411 invoked from network); 3 Jun 2010 15:59:20 -0000
Received: from unknown (HELO mail.apache.org) (140.211.11.3)
  by 140.211.11.9 with SMTP; 3 Jun 2010 15:59:20 -0000
Received: (qmail 92843 invoked by uid 500); 3 Jun 2010 15:59:20 -0000
Delivered-To: apmail-db-derby-dev-archive@db.apache.org
Received: (qmail 92812 invoked by uid 500); 3 Jun 2010 15:59:20 -0000
Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:derby-dev-help@db.apache.org>
List-Unsubscribe: <mailto:derby-dev-unsubscribe@db.apache.org>
List-Post: <mailto:derby-dev@db.apache.org>
List-Id: <derby-dev.db.apache.org>
Reply-To: <derby-dev@db.apache.org>
Delivered-To: mailing list derby-dev@db.apache.org
Received: (qmail 92805 invoked by uid 99); 3 Jun 2010 15:59:20 -0000
Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136)
    by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 03 Jun 2010 15:59:20 +0000
X-ASF-Spam-Status: No, hits=-1494.4 required=10.0
	tests=ALL_TRUSTED,AWL
X-Spam-Check-By: apache.org
Received: from [140.211.11.22] (HELO thor.apache.org) (140.211.11.22)
    by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 03 Jun 2010 15:59:20 +0000
Received: from thor (localhost [127.0.0.1])
	by thor.apache.org (8.13.8+Sun/8.13.8) with ESMTP id o53FwxNu000655
	for <derby-dev@db.apache.org>; Thu, 3 Jun 2010 15:59:00 GMT
Message-ID: <13951229.161031275580739836.JavaMail.jira@thor>
Date: Thu, 3 Jun 2010 11:58:59 -0400 (EDT)
From: "C.S. Nirmal J. Fernando (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Updated: (DERBY-4587) Add tools for improved analysis and
 understanding of query plans and execution statistics
In-Reply-To: <1597970315.314791268837787263.JavaMail.jira@brutus.apache.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394


     [ 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-4.diff

Hi Bryan,

I think the tree structure should be built using the XSL style sheet, but as you mentioned we need to have a more richer data structure, which will have attributes such as id, parent_id etc, and in the XML file there will be new xml tags inside <details> tag which will give the values of node_id, parent_id, depth_level etc.

These days I built a richer data structure called "TreeNode". This patch contains this new data structure. And I'm printing the nodes as a tree, following are two instances.

C:\OtherNirmal\GSoC\Code\jars\sane>java org.apache.derby.tools.ExportQueryPlanAs
XML jdbc:derby:nirmal me mine ME 4d6f406a-0128-f969-2afb-00000035d458 abc

----------------- Nodes grouped by the depth--------------

(0,0) <node>PROJECTION</node>
(1,0) <node>LOHASHJOIN</node>
(2,0) <node>PROJECTION</node>
(2,1) <node>HASHSCAN</node>
(3,0) <node>LOHASHJOIN</node>
(4,0) <node>TABLESCAN</node>
(4,1) <node>HASHSCAN</node>

-----------TREE STRUCTURE-----------------

<node>PROJECTION</node>
        <node>LOHASHJOIN</node>
                <node>PROJECTION</node>
                        <node>LOHASHJOIN</node>
                                <node>TABLESCAN</node>
                                <node>HASHSCAN</node>
                <node>HASHSCAN</node>

C:\OtherNirmal\GSoC\Code\jars\sane>java org.apache.derby.tools.ExportQueryPlanAs
XML jdbc:derby:nirmal me mine MYSCHEMA b9810038-0128-e492-0a47-00000035b7e8 abc

----------------- Nodes grouped by the depth--------------

(0,0) <node>PROJECTION</node>
(1,0) <node>LOHASHJOIN</node>
(2,0) <node>TABLESCAN</node>
(2,1) <node>HASHSCAN</node>

-----------TREE STRUCTURE-----------------

<node>PROJECTION</node>
        <node>LOHASHJOIN</node>
                <node>TABLESCAN</node>
                <node>HASHSCAN</node>


Note: 
*The patch is not that well commented and clean, I just want to let you know that, now I can recognize the points of each node in the tree. 
*This will not write an xml document, though it takes the file name as input, I've just commented it, since I haven't done with processing new xml tags yet.

Your ideas are highly appreciated.

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