[
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
