db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "A.S.Thiwanka Somasiri (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
Date Thu, 15 Apr 2010 09:10:49 GMT

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

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


View raw message