db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "A.S.Thiwanka Somasiri (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4587) Add tools for improved analysis and understanding of query plans and execution statistics
Date Tue, 06 Apr 2010 20:19:33 GMT

    [ 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.


Mime
View raw message