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 Tue, 06 Apr 2010 16:23:33 GMT

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

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

Rather than print them out, I used derby.language.logQueryPlan = true (to get the more accurate
statistics), and following is the derby.log file:

----------------------------------------------------------------
2010-04-06 16:04:06.112 GMT:
 Booting Derby version The Apache Software Foundation - Apache Derby - 10.6.0.0 alpha - (exported):
instance a816c00e-0127-d3dd-438c-0000003f3a18
on database directory C:\OtherNirmal\GSoC\Code\jars\sane\nirmal  

Database Class Loader started - derby.database.classpath=''
2010-04-06 16:04:06.906 GMT Thread[main,5,main] (XID = 1430), (SESSIONID = 0), (DATABASE =
nirmal), (DRDAID = null), Committing
2010-04-06 16:04:06.907 GMT Thread[main,5,main] (XID = 1430), (SESSIONID = 0), (DATABASE =
nirmal), (DRDAID = null), Rolling backan
2010-04-06 16:04:06.909 GMT Thread[main,5,main] (XID = 1431), (SESSIONID = 1), (DATABASE =
nirmal), (DRDAID = null), Committing
2010-04-06 16:04:08.753 GMT Thread[main,5,main] (XID = 1432), (SESSIONID = 1), (DATABASE =
nirmal), (DRDAID = null), Begin compiling prepared statement: select * from myTable where
i=1 :End prepared statement
2010-04-06 16:04:09.493 GMT Thread[main,5,main] (XID = 1432), (SESSIONID = 1), (DATABASE =
nirmal), (DRDAID = null), End compiling prepared statement: select * from myTable where i=1
:End prepared statement
2010-04-06 16:04:09.671 GMT Thread[main,5,main] (XID = 1432), (SESSIONID = 1), (DATABASE =
nirmal), (DRDAID = null), Executing prepared statement: select * from myTable where i=1 :End
prepared statement
2010-04-06 16:04:09.796 GMT Thread[main,5,main] (XID = 1432), (SESSIONID = 1), (DATABASE =
nirmal), (DRDAID = null), Committing
2010-04-06 16:04:09.989 GMT Thread[main,5,main] (XID = 1432), (SESSIONID = 1), select * from
myTable where i=1 ******* 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.30
	optimizer estimated cost:           29.56

2010-04-06 16:04:09.990 GMT Thread[main,5,main] (XID = 1432), (SESSIONID = 1), (DATABASE =
nirmal), (DRDAID = null), Committing
2010-04-06 16:10:07.514 GMT Thread[main,5,main] (XID = 1432), (SESSIONID = 1), (DATABASE =
nirmal), (DRDAID = null), Rolling back

2010-04-06 16:10:07.537 GMT:
Shutting down instance a816c00e-0127-d3dd-438c-0000003f3a18
----------------------------------------------------------------


I'm looking to retrieve XPlainTable values now.


> 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