Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <15727336.136521271322649369.JavaMail.jira@thor>
Date: Thu, 15 Apr 2010 05:10:49 -0400 (EDT)
From: "A.S.Thiwanka Somasiri (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (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


    [ 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