You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "A.S.Thiwanka Somasiri (JIRA)" <ji...@apache.org> on 2010/04/16 13:56:25 UTC
[jira] Issue Comment Edited: (DERBY-4587) Add tools for improved
analysis and understanding of query plans and execution statistics
[ https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12857506#action_12857506 ]
A.S.Thiwanka Somasiri edited comment on DERBY-4587 at 4/16/10 7:56 AM:
-----------------------------------------------------------------------
Hi Bryan,
I just tried to figure out how the "sysxplain_resultsets" table behaves.I sent these SQL statements and checked the output.
1. ij>select * from cities where country='Sri Lanka' order by city_name;
2. ij> select stmt_text, xplain_time from MY_STATS.sysxplain_statements order by xplain_time;
3. ij> select st.stmt_text, rs.op_identifier
> from my_stats.sysxplain_statements st
> join my_stats.sysxplain_resultsets rs
> on st.stmt_id = rs.stmt_id;
Then I sent the 3rd query again to the database and the output was :
STMT_TEXT |OP_IDENTIFIER
---------------------------------------------------------------------------------------------------------------------------------------------------------------
select * from cities where country='Sri Lanka' order by city_name |SORT
select * from cities where country='Sri Lanka' order by city_name |TABLESCAN
select stmt_text, xplain_time from MY_STATS.sysxplain_statements
order by xplain_time |SORT
select stmt_text, xplain_time from MY_STATS.sysxplain_statements
order by xplain_time |TABLESCAN
select st.stmt_text, rs.op_identifier
from my_stats.sysxplain_statements st
join my_stats.sysxplain_resultsets rs
on st.stmt& |PROJECTION
select st.stmt_text, rs.op_identifier
from my_stats.sysxplain_statements st
join my_stats.sysxplain_resultsets rs
on st.stmt& |HASHJOIN
select st.stmt_text, rs.op_identifier
from my_stats.sysxplain_statements st
join my_stats.sysxplain_resultsets rs
on st.stmt& |TABLESCAN
select st.stmt_text, rs.op_identifier
from my_stats.sysxplain_statements st
join my_stats.sysxplain_resultsets rs
on st.stmt& |HASHSCAN
8 rows selected
_______________________________________________END OF THE OUTPUT___________________________________________________________
(The third query was sent twice to convince you that the query which tries to retrieve XPLAIN tables itself, is also stored in the XPLAIN tables again)
This clearly figures out the what type of result sets are generated when running a SQL statement.Here the OP_IDENTIFIER indicates the result set type.
Let's take the first query "select * from cities where country='Sri Lanka' order by city_name;". This has the result sets TABLESCAN and SORT. Since the first result set performs a scan on the table, the SCAN_RS_ID column in SYSXPLAIN_RESULTSETS can identify the particular row in SYSXPLAIN_SCAN statistics related to the scan behavior. Similarly, SORT statistics can also be retrieved.
Hope your comments on this.
Thanks.
was (Author: asthiwanka):
Hi Bryan,
I just tried to figure out how the "sysxplain_resultsets" table behaves.I sent these SQL statements and checked the output.
1. ij>select * from cities where country='Sri Lanka' order by city_name;
2. ij> select stmt_text, xplain_time from MY_STATS.sysxplain_statements order by xplain_time;
3. ij> select st.stmt_text, rs.op_identifier
> from my_stats.sysxplain_statements st
> join my_stats.sysxplain_resultsets rs
> on st.stmt_id = rs.stmt_id;
Then I sent the 3rd query again to the database and the output was :
STMT_TEXT |OP_IDENTIFIER
---------------------------------------------------------------------------------------------------------------------------------------------------------------
select * from cities where country='Sri Lanka' order by city_name |SORT
select * from cities where country='Sri Lanka' order by city_name |TABLESCAN
select stmt_text, xplain_time from MY_STATS.sysxplain_statements
order by xplain_time |SORT
select stmt_text, xplain_time from MY_STATS.sysxplain_statements
order by xplain_time |TABLESCAN
select st.stmt_text, rs.op_identifier
from my_stats.sysxplain_statements st
join my_stats.sysxplain_resultsets rs
on st.stmt& |PROJECTION
select st.stmt_text, rs.op_identifier
from my_stats.sysxplain_statements st
join my_stats.sysxplain_resultsets rs
on st.stmt& |HASHJOIN
select st.stmt_text, rs.op_identifier
from my_stats.sysxplain_statements st
join my_stats.sysxplain_resultsets rs
on st.stmt& |TABLESCAN
select st.stmt_text, rs.op_identifier
from my_stats.sysxplain_statements st
join my_stats.sysxplain_resultsets rs
on st.stmt& |HASHSCAN
8 rows selected
_______________________________________________END OF THE OUTPUT___________________________________________________________
(The third query was sent twice to convince you that the query which tries to retrieve XPLAIN tables itself, is also stored in the XPLAIN tables again)
This clearly figures out the what type of result sets are generated when running a SQL statement.Here the OP_IDENTIFIER indicates the result set type.
Let's take the first query "select * from cities where country='Sri Lanka' order by city_name;". This has the result sets TABLESCAN and SORT. Since the first result set performs a scan on the table, the SCAN_RS_ID column in SYSXPLAIN_RESULTSETS can identify the particular row in SYSXPLAIN_SCAN statistics related to the scan behavior. Similarly, SORT statistics can also be retrieved.
Hope your comments on this.
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