[
https://issues.apache.org/jira/browse/DERBY-4587?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12854156#action_12854156
]
A.S.Thiwanka Somasiri commented on DERBY-4587:
----------------------------------------------
Hi Bryan,
This is what I got when capturing the query plan for query "SELECT * FROM
cities WHERE city_name='New Orleans'; you have presented.(In which the demo
database supported with the Derby source code)
Table Scan ResultSet for CITIES at read committed isolation level using
instantaneous share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 1
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds) = 1
open time (milliseconds) = 1
next time (milliseconds) = 2
close time (milliseconds) = 0
next time in milliseconds/row = 2
scan information:
Bit set of columns fetched=All
Number of columns fetched=6
Number of pages visited=2
Number of rows qualified=1
Number of rows visited=87
Scan type=heap
start position:
null
stop position:
null
qualifiers:
Column[0][0] Id: 1
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
optimizer estimated row count: 8.80
optimizer estimated cost: 47.82
And I tried the RUNTIMESTATISTICS attribute for the query and got the following
output :
ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
0 rows inserted/updated/deleted
ij> CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1);
0 rows inserted/updated/deleted
ij> select * from cities where city_name='New Orleans';
CITY_ID |CITY_NAME |COUNTRY |AIR&|LANGUAGE
|COU&
------------------------------------------------------------------------------------------
75 |New Orleans |United States |MSY |English
|US
1 row selected
ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1
--------------------------------------------------------------------------------------------------------------------------------
Statement Name:
null
Statement Text:
select * from cities where city_name='New Orleans'
Parse Time: 1
Bind Time: 2
Optimize&
1 row selected
In here it clearly states the the time to generate the query tree from the SQL
query in Parse Time and in Bind Time
it indicates the time to traverse the query tree,etc.
Looking for an idea to move forward from this stage from you.
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.
