[jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics

["C.S. Nirmal J. Fernando (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12853424#action_12853424 ] 

C.S. Nirmal J. Fernando commented on DERBY-4587:
------------------------------------------------

Hi Bryan,

I captured the "query execution plan" by printing out the returning String from "getStatementExecutionPlanText()" method in RunTimeStatisticsImpl.java class.

Following are the details:

==============================================================

ij> select * from myTable;
I          |J
-----------------------
1          |2
1          |3
1          |4
2          |4
2          |0
3          |6
3          |3

7 rows selected

ij> select * from myTable where i=1;

I          |J
-----------------------
1          |2
1          |3
1          |4

3 rows selected

This the query plan I printed (starts by @@@@..  and ends with @@@@..)

@@@@@@@@Table Scan ResultSet for MYTABLE at read committed isolation level using
 instantaneous share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 3
Rows filtered = 0
Fetch Size = 16
        constructor time (milliseconds) = 0
        open time (milliseconds) = 0
        next time (milliseconds) = 0
        close time (milliseconds) = 0
        next time in milliseconds/row = 0

scan information:
        Bit set of columns fetched=All
        Number of columns fetched=2
        Number of pages visited=1
        Number of rows qualified=3
        Number of rows visited=7
        Scan type=heap
        start position:
                null
        stop position:
                null
        qualifiers:
                Column[0][0] Id: 0
                Operator: =
                Ordered nulls: false
                Unknown return value: false
                Negate comparison result: false
        optimizer estimated row count:            1.20
        optimizer estimated cost:           30.90
@@@@@@@

Statement Name:
        null
Statement Text:
        select * from myTable where i=1
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate T&

====================================================================

So, in the graphical explainer for this query, there should be only one image (of the myTable) and under that there are following table of details:

====================================================================

Table Scan on myTable
_______________________________

Query: select * from myTable where i=1
Rows: 3
Optimizer Estimated Cost: 30.90

====================================================================

I like to get your comments on this, and also if you need any other information to be included in the graphical explainer please suggest them as well. 
(This was done to do a prototype of the graphical explainer as you suggested. I have already created a sample XML & XSLT file and looking forward to modify it and create a prototype)

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: PostgreSQL license.jpg
>
>
> 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.
-
You can reply to this email to add a comment to the issue online.