[
https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12857316#action_12857316
]
C.S. Nirmal J. Fernando commented on DERBY-4587:
------------------------------------------------
Hi Bryan,
*Derby XPLAIN tables are clearly described in
http://db.apache.org/derby/docs/dev/tuning/ctun_xplain_tables.html and I had
gone through it weeks ago.
*And as we discussed way before and as I included in my GSoC proposal project
can be sub divided into two, which I planned to implement in the two phases of
GSoC-2010. These are the sub parts:
1) Build a tool (a small Java program using standard JDBC) which can read the
query execution data for a SQL statement from the Derby XPLAIN tables and emit
the data in XML tagged format. This involves specifying the XML schema for the
data, writing the program to produce the data, and building tests for it.
2) Build a tool to format the XML-formatted data into visual information in a
browser, using an XSLT stylesheet. This involves conceptualizing the visual
display, designing and writing the XSLT stylesheet, and building tests for it.
*In the phase 1 I have to build a small tool to get values of XPLAIN tables and
emit XML data, but because none of the JDBC-side support for SQL/XML is
implemented in Derby, it is not possible to bind directly into an XML value or
to retrieve an XML value directly from a result set using JDBC. Instead, we
must bind and retrieve the XML data as Java strings or character streams by
explicitly specifying the appropriate XML operators, XMLPARSE and XMLSERIALIZE,
as part of our SQL queries.
To retrieve XML values from a Derby database using JDBC, we can use the
XMLSERIALIZE operator in the SQL query.
eg: SELECT XMLSERIALIZE (stmt_text as CLOB) FROM myschema.sysxplain_statements
for more information we can refer
http://db.apache.org/derby/docs/10.5/ref/rrefsqljtypexml.html
**I did not put any comments here other than this after the GSoC application
deadline is simply because I thought it will be unfair to other applicants
since this is a some kind of a competition.
I hope to contribute for this project in this summer and also for the evolution
of Derby in future.
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: 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
