db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "C.S. Nirmal J. Fernando (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 13:50:48 GMT

    [ 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


> 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